How to generate the insert scripts in MSSQL Server

 

To  generate the insert scripts for a table in MSSQL sql follow the below steps.
 

1. Replace the table name in 3rd line and execute

DECLARE

@tableName varchar(100),@distinct varchar(5)

set @tableName='cities'

set @distinct='N'

--Declare a cursor to retrieve column specific information for the specified table

DECLARE cursCol CURSOR FAST_FORWARD FOR

SELECT column_name,data_type FROM information_schema.columns a (nolock)

inner join sys.objects b (nolock) on a.table_name=b.name

inner join sys.columns c (nolock) on a.column_name=c.name and c.object_id=b.object_id

where table_name = @tableName and c.is_identity=0

OPEN cursCol

DECLARE @string nvarchar(max) --for storing the first half of INSERT statement

DECLARE @stringData nvarchar(max) --for storing the data (VALUES) related statement

DECLARE @dataType nvarchar(1000) --data types returned for respective columns

SET @string='INSERT INTO '+@tableName+'('

SET @stringData=''

DECLARE @colName nvarchar(150) ,@c varchar(20)

set @c=','''''''','''''''''''')'

FETCH NEXT FROM cursCol INTO @colName,@dataType

IF @@fetch_status<>0

begin

print 'Table '+@tableName+' not found, processing skipped.'

close curscol

deallocate curscol

return

END

WHILE @@FETCH_STATUS=0

BEGIN

IF @dataType in ('varchar','char','nchar','nvarchar')

BEGIN

--SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'

SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+'replace('+@colName+@c+'+'''''+''''',''NULL'')+'',''+'

END

ELSE

if @dataType in ('text','ntext') --if the datatype is text or something else

BEGIN

SET @stringData=@stringData+'''''''''+isnull(cast('+'replace('+@colName+@c+' as varchar(2000)),'''')+'''''',''+'

END

ELSE

IF @dataType = 'money' --because money doesn't get converted from varchar implicitly

BEGIN

SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'

END

ELSE

IF @dataType='datetime'

BEGIN

SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'

END

ELSE

IF @dataType='image'

BEGIN

SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'

END

ELSE --presuming the data type is int,bit,numeric,decimal

BEGIN

SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'

END

SET @string=@string+@colName+','

FETCH NEXT FROM cursCol INTO @colName,@dataType

END

DECLARE @Query varchar(max)

IF(@distinct='N')

BEGIN

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName +'(nolock) where '

END

ELSE

BEGIN

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') SELECT DISTINCT ''+ ' + substring(@stringData,0,len(@stringData)-2)+''' FROM '+@tableName +'(nolock) where '

END

--exec sp_executesql @query

select replace(replace(@query,'NUL,','NULL,'),'NUL)','NULL')

CLOSE cursCol

DEALLOCATE cursCol

2. Above query will result give select query, now copy and paste it in new query window. Here you can include the where clause if required and execute.

SELECT 'INSERT INTO env(project,reltype,environment,orval) VALUES('+ ''+isnull(''''+replace(project,'''','''''')+'''','NULL')+','+''+isnull(''''+replace(reltype,'''','''''')+'''','NULL')+','+''+isnull(''''+replace(environment,'''','''''')+'''','NULL')+','+''+isnull(''''+convert(varchar(200),orval)+'''','NULL')+''+')' FROM env(nolock)  where 

You will get the insert scripts.