Generating Script - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Generating Script

Author

Topic

kiran
Starting Member

30 Posts

Posted - 2003-04-14 : 01:18:57

How do I generate a sql script of INSERT statements for a table data?

I have a table with some 20 rows, I want to generate a sql script with an INSERT statement for each row. How can I go this ?

thanks in advance...

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-04-14 : 01:21:49

Hi

Go to www.lockwoodtech.com
They have a tool that will do this for you. Or you can write one yourself with some ADO code if you are after a fun project.

Damian

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-14 : 11:00:41

Here's something that I keep in my backpocket until needed. You can expand on it I imagine by incorporating a rewference to the actual table itself.

Hope it helps:

USE NorthWind

DECLARE @TBName sysname, @TBName2 sysname

SELECT @TBName = 'Orders', @TBName2 = 'Orders2'

SELECT SQL FROM (

SELECT 'INSERT INTO ' + @TBName2 + ' ( ' + COLUMN_NAME As SQL, TABLE_NAME, 1 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBName
AND ORDINAL_POSITION = 1
UNION ALL
SELECT ' , ' + COLUMN_NAME As SQL, TABLE_NAME, 1 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBName
AND ORDINAL_POSITION <> 1
UNION ALL
SELECT ') ' As SQL, TABLE_NAME, 2 As SQL_Group, 1 As Row_Order
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @TBName
UNION ALL
SELECT 'SELECT ' + '''' + ' ' + '''' + ' AS ' + COLUMN_NAME As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBName
AND ORDINAL_POSITION = 1
UNION ALL
SELECT ' , ' + '''' + ' ' + '''' + ' AS ' + COLUMN_NAME As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBName
AND ORDINAL_POSITION <> 1
UNION ALL
SELECT ' FROM ' + TABLE_NAME As SQL, TABLE_NAME, 4 As SQL_Group, 1 As Row_Order
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @TBName
) AS XXX
Order By TABLE_NAME, SQL_Group, Row_Order

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources