-- This proc print a statement to copy data from one table to another with same structure. e.g. transfering data between servers (production to develop)

-- I use it in SSMS associated with hot key ctrl+3. Select a table name and press ctrl+3.

CREATE PROC sp_insert(@table varchar(257),@alias varchar(128) = 'a')

AS

-- author Carlo Romagnano

DECLARE

@s-2 varchar(8000)

,@i varchar(8000)

,@comma varchar(10)

,@iden INT

SET @alias = ISNULL(@alias,'a')

SET @s-2 = 'SELECT ' + char(13) + char(10)

SET @i = 'INSERT INTO ' + @table + ' (' + char(13) + char(10)

SET @comma = char(9) + ' '

SET @iden = 0

IF LEFT(@TABLE,1) = '#'

SELECT @s-2 = @s-2 + @comma + @alias + '.' + c.name + CASE c.isnullable WHEN 1 THEN ' --NULLABLE' ELSE '' END + CASE WHEN c.cdefault <> 0 THEN ' --DEFAULTED' ELSE '' END

,@i = @i + @comma + c.name + CASE c.isnullable WHEN 1 THEN ' --NULLABLE' ELSE '' END + CASE WHEN c.cdefault <> 0 THEN ' --DEFAULTED' ELSE '' END

,@comma = char(13) + char(10) + char(9) + ','

,@iden = @iden + COLUMNPROPERTY (c.id, c.name, 'IsIdentity')

from tempdb.dbo.syscolumns c

where c.id = object_id('tempdb.dbo.' + @table)

AND c.name <> 'timestamp'

AND c.iscomputed = 0

order by c.colorder

ELSE

SELECT @s-2 = @s-2 + @comma + @alias + '.' + c.name + CASE c.isnullable WHEN 1 THEN ' --NULLABLE' ELSE '' END + CASE WHEN c.cdefault <> 0 THEN ' --DEFAULTED' ELSE '' END

,@i = @i + @comma + c.name + CASE c.isnullable WHEN 1 THEN ' --NULLABLE' ELSE '' END + CASE WHEN c.cdefault <> 0 THEN ' --DEFAULTED' ELSE '' END

,@comma = char(13) + char(10) + char(9) + ','

,@iden = @iden + COLUMNPROPERTY (c.id, c.name, 'IsIdentity')

from syscolumns c

where c.id = object_id(@table)

AND c.name <> 'timestamp'

AND c.iscomputed = 0

order by c.colorder

if @iden > 0

print 'SET IDENTITY_INSERT ' + @table + ' ON'

print @i + char(13) + char(10) + ')'

print @s-2 + char(13) + char(10) + ' FROM ' + @table + ' ' + @alias

if @iden > 0

print 'SET IDENTITY_INSERT ' + @table + ' OFF'