Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bjcascone
Starting Member
37 Posts |
 Posted - 2011-03-01 : 16:16:43
|
| I am attempting to create a new table (basically a copy of an existing one just changing the name) in a new DB. I am trying to do this by recursing through a list of existing table names and build each table on (all fields are the same) I know there are a few ways to do this but i am having trouble getting this to work as a recursive loop that builds the statement and populates the table names each time it executes. this is what i have so far:(this gives me my unique list of names i will later use to populate the query)-- SELECT TABLE NAMES WITH ONLY NUMERIC NAMES INTO NEW TABLE. BEGIN SET @SQLStringTBL = " BEGIN TRY DROP TABLE " + @DBNAME + ".DBO.TBLNAMELIST END TRY BEGIN CATCH END CATCH SELECT [NAME] AS [NAME] INTO " + @DBName + " .DBO.TBLNAMELIST FROM " + @DBName + " .DBO.sysobjects WHERE isnumeric([Name])=1 " EXEC(@SQLStringTBL) END(this is where I am having issues. The @TBLNAME variable only exists in the @SQL and that is where it is being initialized.) SET @SQL = " DECLARE @TBLNAME AS VARCHAR(100) DECLARE TBLNM CURSOR FOR SELECT [NAME] FROM " + @NEWDBNAME+".DBO.TBLNAMELIST OPEN TBLNM FETCH NEXT FROM " + @NEWDBNAME+".DBO.TBLNAMELIST INTO "+ @TBLNAME + " WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY DROP TABLE " + @NEWDBNAME+".DBO.TEMPdbname END TRY BEGIN CATCH END CATCH USE " + @NEWDBNAME+" GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo]. a"+ @TBLNAME +" ( [Dept] [smallint] NULL, [Class] [smallint] NULL, [Item] [smallint] NULL, [Store] [smallint] NULL, [Year] [smallint] NULL, [Monthh] [smallint] NULL, [OHQty1] [int] NULL, [SalesQty1] [int] NULL, [SalesAmt1] [money] NULL, [OHQty2] [int] NULL, [SalesQty2] [int] NULL, [SalesAmt2] [money] NULL, [OHQty3] [int] NULL, [SalesQty3] [int] NULL, [SalesAmt3] [money] NULL, [OHQty4] [int] NULL, [SalesQty4] [int] NULL, [SalesAmt4] [money] NULL, [OHQty5] [int] NULL, [SalesQty5] [int] NULL, [SalesAmt5] [money] NULL, [RetlAmt1] [money] NULL, [GrossRnchk1] [money] NULL, [RnchkSub1] [int] NULL, [AdRetl1] [money] NULL, [RetlAmt2] [money] NULL, [GrossRnchk2] [money] NULL, [RnchkSub2] [int] NULL, [AdRetl2] [money] NULL, [RetlAmt3] [money] NULL, [GrossRnchk3] [money] NULL, [RnchkSub3] [int] NULL, [AdRetl3] [money] NULL, [RetlAmt4] [money] NULL, [GrossRnchk4] [money] NULL, [RnchkSub4] [int] NULL, [AdRetl4] [money] NULL, [RetlAmt5] [money] NULL, [GrossRnchk5] [money] NULL, [RnchkSub5] [int] NULL, [Adretl5] [money] NULL, [SalesType1] [char](1) NULL, [SalesType2] [char](1) NULL, [SalesType3] [char](1) NULL, [SalesType4] [char](1) NULL, [SalesType5] [char](1) NULL, [VenNum] [real] NULL, [InvRcvQty] [real] NULL, [TermsDiscAmt] [float] NULL, [NetMkdnAmtA] [float] NULL, [NetMkdnAmtB] [float] NULL, [NetMkdnAmtC] [float] NULL, [NetMkdnAmtD] [float] NULL, [MkUAmt] [float] NULL, [MkupCanAmt] [float] NULL, [CbVendIncmAmt] [float] NULL, [CBDefectCostAmt] [float] NULL, [CBMiscCostAmt] [float] NULL, [InBndFrtAmt] [float] NULL, [OutBndFrtAmt] [float] NULL, [MiscFrtAmt] [float] NULL, [StoreCrcFrtAmt] [float] NULL, [CrcVendFrtAmt] [float] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO CLOSE TBLNM DEALLOCATE TBLNM END " PRINT(@SQL) Thank you for any help you are able to provide. -Brian |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-01 : 16:34:32
|
| Why not just generate scripts for the existing tables and run them in the new database? Right-click the database in SSMS, Tasks, Generate Scripts, and go from there. |
 |
|
|
Bjcascone
Starting Member
37 Posts |
Posted - 2011-03-01 : 16:41:08
|
| These tables are going to get recreated on a monthly basis and the names are going to change each time.. for example:DB01 TableXX01 TableXX02 TableXX03 TableXX04the next month the same thing will have to get doneDB01 TableYY01 TableYY02 TableYY03 TableYY04Also this is part of a much larger stored proc that ultimatly is inserting the data in these tables into a new database in identical compressed tables, and makes some name changes. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-01 : 16:59:24
|
| Should I bother mentioning this is a poor design practice? Identical table structures should have their data consolidated into a single table of the proper structure. Doing so avoids the entire need for this procedure. |
|
|
|
Bjcascone
Starting Member
37 Posts |
Posted - 2011-03-01 : 17:18:52
|
| I do not work in a transactional environment it is more of a development enviorment in a data warehouse. I am doing this because the data in this DB is rather large (over 1TB, and there are 12 of them, one for every month they are partitioned) due to the sheer size of these tables they are basically "stored" as compressed tables. but running the compression and shrinking the DB after takes a long time. so the procedure that i am writing creates a new DB, creates the tables, turns on the compression on these new tables, does a minimally logged insert into these new tables (space savings of 75%) and eventually drops the old tables and DB. This may not be the best way to go about this but currently shrinking these compated DB's are taking 22 days and this new method will take less than a day. I am just trying to automate these steps into a single Stored procedure. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-01 : 17:25:15
|
| Then why not consolidate your uncompressed DBs into a single compressed database? All you have to do is run INSERTs from the old into the new. You don't have to recreate a new DB and go through the entire compression process for each one, and you reduce the number of databases. The amount of disk space used in the end will be the same, perhaps even less.By the way, unless you are damn near out of disk space, DO NOT SHRINK your databases. You'll fragment the data and indexes and lower performance. |
|
|
|
|
|
|
|
|
how do I dynamically build create table statements