Technical Article

Func. generates insert and select for large table

,

Generates an insert statement including column list.  Useful for identity table data copying.  Just modify the code generated to select and insert the needed fields.  Saves time on large table inserts (also generates a select statement.  Use part or all of the SQL generated.  Saves development time when dealing with large tables.

CREATE FUNCTION dbo.fn_ins_stmt (@table varchar(100)) RETURNS varchar(3000)
AS
BEGIN

/*
Function Name:fn_ins_stmt
Created By:Joshua J. Beck
Create Date:20021205
Description:Generates an insert statement including column list.
Useful for identity table data copying.  Just modify
the code generated to select and insert the needed
fields.  Saves time on large table inserts (also
generates a select statement.  Use part or all of the
SQL generated.  Saves development time when dealing with
large tables.
Use:select dbo.fn_ins_stmt('tablename')

Revision History:
WHO|WHEN|WHAT
--------------------------------------------------------------------------
JJB20021205Created

*/
declare @col_str varchar(1000), @sql_str varchar(3000)

select @col_str = 't1.'

select @col_str = @col_str + column_name + ', t1.'
from information_schema.columns 
where table_name = @table
order by ordinal_position

if @@rowcount < 1
begin
select @sql_str = 'table not found'
end
else
begin
select @col_str = left(@col_str, datalength(@col_str) - 5)

select @sql_str = 'insert into ' + @table + ' (' + replace(@col_str, 't1.', '') + ') ' + char(13) + 
'select ' + @col_str + ' ' + char(13) + 'from ' + @table + ' t1' 
end

return @sql_str

END

GO

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating