Below script will generate the Insert script for all table from one particular db..

--Start

declare @TableName varchar(50)

declare tblcur CURSOR FOR

select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='BASE TABLE' AND TABLE_NAME LIKE 'DUMMY_%'

open tblcur

Fetch from tblcur

into @TableName

while @@fetch_status = 0

begin

/* This script will generate script to insert/update from a source table in one database to an

identical destination table in another database or server. It can be run for inserts or updates,

and can be run for a single row in insert and update mode, or all rows in a table for insert mode.

*/

declare @tab varchar(50)

,@pk1Val varChar(10)

,@pk1Name varChar(50)

,@qt char(1)

,@StatementType varChar(10)

,@sql varchar(max)

set nocount on

set @tab = @TableName

/*

Instructions:

1) open script and connect to the source database

2) Change the variable values to change the output options for the script below (@tab, @statementtype etc)

3) execute the script (best to use text output)

4) copy the script output into a script window, and run on the destination table.

@tab = the name of the source table

@pk1Val = if selecting a single row or doing an update statement, the value of the primary key for that row

@pk1Name = if inserting a single row or doing an update statement, the name of the column for the primary key

@StatementType = either 'INSERT' to create an insert statement or 'UPDATE' for an Update statement

*/

select

@pk1Val = '', @pk1Name = '', @StatementType = 'insert'

declare @tabName varchar(50)

, @colName varchar(50)

, @colType varchar(50)

, @collength varChar(50)

, @colOrder int

, @IsIdent char(1)

create table #output (Line varChar(4000), LineOrder int)

create table #ColumnValues (ColName varChar(250), ColOrder int, RowNumber int, ColValue varchar(4000), colType varchar(50))

declare @out varchar(4000)

,@lineCounter int

,@ColValue varchar(4000)

,@sortCol varchar(50)

/* get the ordering column */

select @sortCol = sc.Name

from sysobjects so

inner join syscolumns sc

on so.id= sc.id

inner join systypes st

on sc.xtype = st.xusertype

where so.Name = @tab

and ((sc.status = 0x80) OR (ColOrder = 1 and not sc.status = 0x80 ))

/* put in the repeating values based on the columns*/

declare objCurs CURSOR FOR

select so.name, sc.name, st.name, sc.length, Case when sc.status = 0x80 then 'Y' else 'N' END as IsIdent, ColOrder

from sysobjects so

inner join syscolumns sc

on so.id= sc.id

inner join systypes st

on sc.xtype = st.xusertype

where so.Name = @tab

DECLARE @counter int, @numCols int, @RowNumber int, @LastRowNumber int, @maxRowNumber int, @maxColOrder int

select @numCols = count(sc.id)

from sysobjects so

inner join syscolumns sc

on so.id= sc.id

where so.Name = @tab

--select @numCols --debug code

open objCurs

Fetch from objCurs

into @tabname, @colName, @colType, @colLength, @isIdent, @colOrder

while @@fetch_status = 0

begin

SET @counter = 0

/* get the value from the table */

if @IsIdent = 'N'

BEGIN

--select @TabName,@ColName, @ColType, @ColLEngth, @isIdent, @ColOrder --debug code

/* increase better type handling by inserting more case statments, handling different data types */

if datalength(@pk1Name) = 0 or datalength(@pk1Val) = 0

begin

/* getting all rows in the table */

exec ('insert into #ColumnValues (ColName, ColOrder, ColValue, ColType)

select ''' + @colName + ''', ' + @ColOrder + ', Convert(nvarchar(4000),' + @colName + ') , ''' + @colType + ''' from ' + @tabName + ' order by ' + @SortCol + ' ' +

' declare @counter int set @counter = 0 ' +

' update #ColumnValues Set @Counter = RowNumber = @Counter + (' + @numCols + ' * 10) where ColName = ''' + @colName + '''' )

end

else

begin

/* filtering by a pk val */

exec ('insert into #ColumnValues (RowNumber, ColName, ColORder, ColValue, ColType)

select 0, ''' + @colName + ''', ' + @ColOrder + ', Convert(nvarchar(4000),' + @colName + ') , ''' + @colType + ''' from ' + @tabName +

' where ' + @pk1Name + ' = ' + @pk1Val)

end

end /* if @isIdent = 'n' */

Fetch Next from objCurs

into @tabname, @colName, @colType, @colLength, @IsIdent, @ColOrder

end

--select * from #ColumnValues --debug code

select @MaxRowNumber = Max(rowNumber) from #columnValues --keep highest row number so we know when we are finished

select @MaxColOrder = max(ColOrder) from #ColumnValues where RowNumber = @MaxRowNumber

/* next cursor for outputting the results from the retval table into the output table */

declare ColVal_Curs cursor for

select ColName , ColOrder , RowNumber , ColValue , colType

from #ColumnValues

order by RowNumber, ColOrder

open ColVal_Curs

--set the last row number to the first in the table, so post loop checking works

select @lastRowNumber = min(rowNumber) from #ColumnValues

set @lineCounter = @LastRowNumber --initialise at the first row

fetch from ColVal_Curs into

@colName, @ColOrder, @RowNumber, @colValue, @ColType

while @@Fetch_status = 0

BEGIN /* cursor loop */

/* get the quote type to enclose the value from the column type */

select @QT = case @colType

when 'nvarchar' then ''''

when 'varchar' then ''''

when 'nchar' then ''''

when 'DateTime' then ''''

when 'ntext' then ''''

when 'char' then ''''

else ''

end

--select @linecounter, @colName, @ColOrder, @RowNumber, @colValue, @ColType

if not @ColValue is null

if @rowNumber = @lineCounter

select @out = case @statementType

when 'UPDATE' THEN 'Update ' + @tab + ' SET '

when 'INSERT' then 'INSERT INTO ' + @tab + ' ('

end

begin

if @StatementType = 'UPDATE'

BEGIN

select @Out = @out + @colName + ' = ' + @QT + COALESCE(@ColValue, 'NULL') + @QT + ',' -- + @ColType

--select @Out = @out + @colName + ' = ' + @QT + @colName + @QT + ',' -- + @ColType

insert into #output (Line, LineOrder)

values (@out, @lineCounter)

end

if @statementType = 'INSERT'

BEGIN

/* put comma in */

if @lineCounter > @RowNumber --not first line in set of values for row

select @out = @out + ','

/*put in the name of the column */

insert into #output (Line, LineOrder)

values (@out + @colName

, @lineCounter)

if @lineCounter > @RowNumber --not first line in set of values for row

select @out = ','

else

select @out = ''

/* put in the value of the column */

insert into #output (Line, LineOrder)

values (@out + @QT + COALESCE(@ColValue, 'NULL') + @QT

, @lineCounter + 10 + @numCols)

END

end /*not @ColValue is null */

select @lineCounter = @lineCounter + 1

set @out = ''

/* get the next record*/

fetch from ColVal_Curs into

@colName, @ColOrder, @RowNumber, @colValue, @ColType

--select @ColOrder, @MaxColOrder, @@Fetch_Status --debug code

if (@rowNumber > @lastRowNumber) or (@RowNumber = @MaxRowNumber and @MaxColOrder = @ColOrder and @@FEtch_Status = -1)

BEGIN

/* this bit of processing is done whenever the set of columsn in a row changes to the next row of the original table*/

/* ie we are building a record to insert, and the PK changes because we are at the next record */

/* remove the last comma from the last line */

declare @lastLine int

if @statementType = 'UPDATE'

begin

/*remove last comma*/

update #output

set Line = left(Line,datalength(Line)-1)

where lineOrder = @LineCounter

/* insert a 'where' clause */

insert into #output (line, LineOrder)

select ' WHERE ' + @pk1Name + ' = ' + @pk1Val, Max(LineOrder) + 1 from #output

end

if @statementType = 'INSERT'

BEGIN

/* put in a 'values' statement between the column names and the column values */

insert into #output (Line, LineOrder)

values (') VALUES (', @LastRowNumber + @numCols + 5)

/* close off the lot */

insert into #output (line, lineorder)

select ')', Max(LineOrder) + 1 from #output

END

set @lastRowNumber = @RowNumber

set @lineCounter = @RowNumber /* reset linecounter for next set */

End /* if rownumber > last row number */

end /* cursor loop */

close objCurs

deallocate objCurs

close ColVal_Curs

deallocate ColVal_Curs

/* get the statements out from the list*/

set @sql = 'select line as [Script], lineorder into master.dbo.' + @tab +'_Script from #output order by lineorder'

--print @sql

exec (@sql)

set @sql =''

set @TableName =''

/* bug tracking code - uncomment to diagnose problems

select distinct RowNumber from #ColumnValues order by 1

select * from #ColumnValues

order by RowNumber, ColOrder, ColName, ColValue

*/

drop table #output

drop table #ColumnValues

set nocount off

Fetch Next from tblcur

into @tablename

set @tab = @TableName

end

close tblcur

deallocate tblcur

--select 'select Replace(script,''DUMMY_'','''') from '+ table_name +' order by lineorder asc' from INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='base table' and table_name like 'dummy%'

--select 'drop table '+ table_name from INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='base table' and table_name like 'dummy%'

--select 'SELECT ''UPDATE '+ TABLE_NAME +' SET SCRIPT =END if not exists(select ''''x'''' from '+ TABLE_NAME +' WHERE '' +(SELECT TOP 1 substring(script, CHARINDEX(''('', script, 0)+1,len(script)) from ' + TABLE_NAME +' WHERE script like ''insert%'') +''= -1) BEGIN '''

--FROM INFORMATION_SCHEMA.TABLES