Jerry Nixon on Windows: Turning a SQL Table to a tSQL Insert statement

Jerry Nixon on Windows

Friday, February 18, 2005

Turning a SQL Table to a tSQL Insert statement

This is a simple routine I use to convert a SQL Table into a tSQL Insert statement. The script could easily be converted into a Stored Procedure if you needed it. The routine implicitely converts all numerics as string back to their original datatype; there is also the limitation of files 1000 characters or less (can be modified) and blobs - there's no accounting for those here.

The need for something like this comes up a lot; so here it is.

declare @Table varchar(255); Set @Table = 'tb_users'
declare @Catalog varchar(255); Set @Catalog = 'pubs'

declare @sql1 varchar(4000)
declare @sql2 varchar(4000)

DECLARE c_cols CURSOR
KEYSET
FOR select
column_name
from
INFORMATION_SCHEMA.COLUMNS
where
table_name = @Table
and table_catalog = @Catalog
DECLARE @col_name varchar(40)
OPEN c_cols
FETCH NEXT FROM c_cols INTO @col_name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
if (@sql1 is null) set @sql1 = ''
if (@sql2 is null) set @sql2 = ''
set @sql1 = @sql1 + '' + @col_name + ','
set @sql2 = @sql2 +
' isnull('''''''' + convert(varchar(1000), ' +
@col_name + ') + '''''''', ''null'') + '','' +'
END
FETCH NEXT FROM c_cols INTO @col_name
END
CLOSE c_cols
DEALLOCATE c_cols

-- handle no columns

if (@sql1 is null)
begin
raiserror('Table %s does not exist in Catalog %s.',
16, 1, @Table, @Catalog)
return
end

set @sql1 = substring(@sql1, 1, len(@sql1) - 1) + ''
set @sql2 = substring(@sql2, 1, len(@sql2) - 10) +
''') as InsertStatement'

declare @sql varchar(8000)
set @sql = 'select ''insert into [' +
@Table + '] (' + @sql1 + ') values ('' + ' +
@sql2 + ' from [' + @Table + ']'

print @sql
execute(@sql)

GO