Method to recreate a table's structure & data

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Method to recreate a table's structure & data

Author

Topic

Racho
Starting Member

13 Posts

Posted - 2009-09-30 : 08:20:15

I am in the foothills of writing a procedure that given an existing table name recreates it's structure and saves the data therein into a temporary table.

The built in script generator in SSMS is great in that if I ask it to script a table (for example to do an INSERT INTO), typically it creates the following kind of script:

INSERT INTO [SQNSA].[dbo].[Address]
           ([Nadnr]
           ,[Name]
           ,[CountryCode]
           ,[IdPlace]
           ,[PostCode]
           ,[NadLines]
           ,[IdBusinessType]
           ,[Phone]
           ,[Fax]
           ,[WebSite]
           ,[IdHQ])
     VALUES
           (<Nadnr, varchar(6),>
           ,<Name, nvarchar(65),>
           ,<CountryCode, varchar(2),>
           ,<IdPlace, int,>
           ,<PostCode, varchar(10),>
           ,<NadLines, nvarchar(120),>
           ,<IdBusinessType, int,>
           ,<Phone, varchar(30),>
           ,<Fax, varchar(30),>
           ,<WebSite, varchar(50),>
           ,<IdHQ, int,>)
GO

How can I replicate this behaviour in my own code?

I've written something along the lines of:

SELECT 
				Column_Name, 
				Data_Type,
				CASE WHEN CHARACTER_MAXIMUM_LENGTH IS Null THEN '' ELSE '(' + CONVERT(VARCHAR(20),character_Maximum_Length) + ')' END AS Length
				FROM [INFORMATION_SCHEMA].COLUMNS 
				WHERE [TABLE_NAME] = 'Address'
				ORDER BY [ORDINAL_POSITION] ASC

but still a long way from getting usable results

SQL2008 and Visual Studio 2008 (VB Preferred)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-30 : 08:25:58

http://vyaskn.tripod.com/code/generate_inserts_2005.txt

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources