create an insert statement

,

Many times we are required to write lenghty insert statements for tables with many columns. This script will create an insert statement for u.
It takes 2 parameters first tbale name and second the ordinal positions of the columns seperated by ','
for example

USP_GenerateInsert 'TabName' ,'1,2,3,4,5'

Hope this is Helpful

ALTER procedure USP_GenerateInsert 
@TableName varchar(100),
@Ordinal varchar(100)=NULL
as
DECLARE @SQL nvarchar(4000)
DECLARE @InsertQuery nvarchar(4000)
if @Ordinal is not null
BEGIN
SET @SQL ='
select Column_name into ##Temp_Generate 
from information_schema.columns 
where table_name=''' + @TableName + ''' and ordinal_position in (' + @Ordinal + ')'
execute sp_executesql @SQL
--select @SQL
END 
ELSE
BEGIN
SET @SQL ='
select Column_name into ##Temp_Generate 
from information_schema.columns 
where table_name=''' + @TableName + ''''
execute sp_executesql @SQL
END 

Set @InsertQuery='Insert into ' + @TableName  + ' ( '
DECLARE @Value varchar(4000)
DECLARE Generate_CUR CURSOR FOR
select * from ##Temp_Generate
OPEN Generate_CUR
FETCH NEXT FROM Generate_CUR
INTO @Value
WHILE @@FETCH_STATUS = 0
BEGIN
set @InsertQuery=@InsertQuery + @Value + ','
FETCH NEXT FROM Generate_CUR
INTO @Value
END
Set @InsertQuery= Substring(@InsertQuery,1,LEN(@InsertQuery)-1)
set @InsertQuery=@InsertQuery + ')'
CLOSE Generate_CUR
DEALLOCATE Generate_CUR
drop table ##Temp_Generate
Select @InsertQuery

Rate

3 (6)

Share

Share

Rate

3 (6)