Generate Insert Statements

/* 
  SQL Server Row Script Creator
 
  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 = 'Access_Right_Role', @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, rowNumber 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 ''''
					 when 'varchar' then ''''
					 when 'char' then ''''
					 when 'text' 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 */
		UPDATE #output
		SET RowNumber = @rowNumber
		WHERE RowNumber IS NULL
	End /* if rownumber > last row number */
 
end /* cursor loop */
 
close objCurs
deallocate objCurs
 
close ColVal_Curs
deallocate ColVal_Curs
 
 
/* the following is an extra loop over the original code to output the code as one statement per row */
CREATE TABLE #combineOutput (rowNumber int, line varchar(4000))
/* get the statements out from the list*/
declare @output varchar(8000), @codeLine varchar(4000), @thisRowNum int, @lastRowNum int
SELECT @output = ''
declare line_curs cursor FOR 
SELECT line, RowNumber  FROM #output order by RowNumber, lineorder
 
open line_curs
fetch FROM line_curs INTO @codeLine, @thisRowNum
SELECT @lastRowNum = @thisRowNum
while @@fetch_status = 0
begin
	IF @thisROwNum > @lastRowNum
	BEGIN
		/* insert a row into the aggregate table if a new row number */
		INSERT INTO #combineOutput (rowNumber, line) values (@rowNumber, @output)
		SET @output = ''
	END 
	SELECT @output = @output + @codeLine + ' '
	SELECT @lastRowNum = @thisRowNum
	fetch FROM line_curs INTO @codeLine, @thisRowNum
end
/* the last row needs to be inserted */
INSERT INTO #combineOutput (rowNumber, line) values (@rowNumber, @output)
 
close line_curs
deallocate line_curs
 
SELECT line AS [Copy AND paste code FROM below] FROM #combineOutput order by rowNumber
 
/*  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 #combineOutput
DROP TABLE #ColumnValues
SET nocount off