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)
set nocount on
/*
 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 @tab = 'mytable', @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 'nchar' then ''''
					 when 'DateTime' then ''''
					 when 'ntext' 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 
				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*/
select line as [Copy script from output window below] from #output order by lineorder

/*  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