Is there a way to script a table's data (temp tables too) in MS SQL 2000?

Question

Given a table or a temp table, I'd like to run a procedure that will output a SQL script (i.e. a bunch of INSERT statements) that would populate the table. Is this possible in MS SQL Server 2000?

Thanks!

Answer 1

You can create a script to do it using a cursor. I just did one yesterday. You can get the idea from this.

DECLARE MY_CURSOR Cursor 

FOR
Select Year, HolidayId, Date, EffBegDate, isnull(EffEndDate,'') AS EffEndDate, ChangedUser From HolidayDate

Open My_Cursor 

DECLARE @Year varchar(50), @HolidayId varchar(50), @Date varchar(50), @EffBegDate varchar(50), @EffEndDate varchar(50), @ChangedUser varchar(50)

Fetch NEXT FROM MY_Cursor INTO @Year, @HolidayId, @Date, @EffBegDate, @EffEndDate, @ChangedUser
While (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)

print 'INSERT INTO [Employee3].[dbo].[HolidayDate]([Year],[HolidayId],[Date],[EffBegDate],[EffEndDate],[ChangedUser])'
print 'VALUES ('''+@Year+''','''+@HolidayId+''','''+@Date+''','''+@EffBegDate+''','''+@EffEndDate+''','''+@ChangedUser+''')'

FETCH NEXT FROM MY_Cursor INTO @Year, @HolidayId, @Date, @EffBegDate, @EffEndDate, @ChangedUser
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
GO

Answer 2

A simple approach:

SELECT 'INSERT INTO table (col1, col2, col3) VALUES ('
    '''' + col1 + ''', '
    '''' + col2 + ''', '
    '''' + col3 + ''')'
FROM table

A more elaborate approach would be to write a procedure that builds the INSERT statement by checking the table's schema, but I've never found a real need to do that in practice.

Answer 3

Somebody else tried it here. Please have a look.