To generate a stored procedure for an INSERT statement based on data in a table

Stored Procedures | data | statements
There is a drawback ... Is that the column that identifies the seed is also insert.

CREATE proc Spgeninsertsql (@tablename varchar (256))

As

Begin

DECLARE @sql varchar (8000)

DECLARE @sqlValues varchar (8000)

Set @sql = ' ('

Set @sqlValues = ' VALUES (' + '

Select @sqlValues = @sqlValues + cols + ' + ', ' + ', @sql = @sql + ' [' + name + '], '

From

(Select Case

When xtype in (48,52,56,59,60,62,104,106,108,122,127)

Then ' case when ' + name + ' is null then ' null ' else ' + ' cast (' + name + ' as varchar) ' + ' end '

When xtype in (58,61)

Then ' case when ' + name + ' is null then ' ' null ' else ' + ' ' ' + ' + ' ' + ' ' + ' ' + ' + ' as varchar ' + ' + ' '

When xtype in (167)

Then ' case when ' + name + ' is null then ' null ' else ' + ' ' + ' ' + ' + ' replace (' + name+ ', ' ' ', ' ', ' ', ' ', ' ' ', ' ' ' + ' + ' "" ' + ' end '

When xtype in (231)

Then ' case if ' + name + ' is null then ' null ' ' Else ' + ' ' N ' ' ' + ' ' + ' + ' replace (' + name+ ', ' ' ', ', ' ', ' ', ' ', ' ' ' + ' + ' "" ' + ' end '

When xtype in (175)

Then ' case when ' + name + ' is null then ' null ' ' Else ' + ' ' + ' + ' cast (replace (' + name+ ', ' ' ', ', ' ', ' ', ' ' Char (' + cast (length as varchar) + ')) + ' "', ' + ' end '

When xtype in (239)

Then ' case when ' + name + ' is null then ' null ' else ' + ' ' N ' ' + ' ' + ' + ' cast (replace (' + name+ ', ' ' ', ', ' ', ' ', ' ' Char (' + cast (length as varchar) + ')) + ' "', ' + ' end '

Else ' ' NULL '

End as Cols,name

From syscolumns

WHERE id = object_id (@tablename)

) T

Set @sql = ' SELECT ' INSERT into [' + @tablename + '] ' + left (@sql, Len (@sql)-1) + ' "+ Left (@sqlValues, Len (@sqlValues)-4) + ' From ' + @tablename

--print @sql

EXEC (@sql)

End

Go