Generate Insert Statements

Set NoCount On
Declare @TableList Table (tabname varchar(100), ExcludeDefault bit, ExcludeIdentity bit)

/*******************************************************************//*  Add or remove table information in this section before running.*//*  Insert a record into @TableList for each table you want output.*//*******************************************************************/-- currently setup for Northwind.
Insert Into @TableList (tabName, ExcludeDefault, ExcludeIdentity) Values ('Orders',0,0)
Insert Into @TableList (tabName, ExcludeDefault, ExcludeIdentity) Values ('Products',0,0)
Insert Into @TableList (tabName, ExcludeDefault, ExcludeIdentity) Values ('Shippers',0,0)
--Note:  If ExcludeDefault is selected, this excludes any columns that have Default values declared.
--Note:  If ExcludeAuto is selected, this excludes any columns that are auto increment fields.
/*******************************************************************//*  Add or remove table information above before running.          *//*  You can use the following script to generate the above list for*//*  the entire database.  Then you can cut and paste the rows you  *//*  wish to modify                                                 *//* 
   Select 'Insert Into @TableList (tabName, ExcludeDefault, ExcludeIdentity) Values (''' + name + ''',0,0)'
   From Sysobjects Where xtype = 'U'
   and (objectproperty(sysobjects.id, 'IsMsShipped') = 0)
*//*******************************************************************/
--declare some variables that will be used
DECLARE @InsertStmt varchar(8000),   -- Holds the Actual Insert Statement.
@Fields varchar(8000),       -- Is the list of fields, comma delimited.
@SelList varchar(8000),      -- A select statement to extract the data from the table.
@ColName varchar(128),       -- One column's name.
@IsChar tinyint,             -- Helps determine how to handle a particular column.
@FldCounter int,             -- Loop Variable
@TableData varchar(8000),    -- Contains a line of data to be inserted (comma delimited)
@ExcludeDefault bit,         -- Retrieved from the table set above.
@ExcludeIdentity bit         -- Retrieved from the table set above.

DECLARE CR_TableList CURSOR FAST_FORWARD FOR
SELECT tabName, ExcludeDefault, ExcludeIdentity From @TableList
FOR READ ONLY

DECLARE @table varchar(128)

OPEN CR_TableList
FETCH NEXT FROM CR_TableList INTO @table, @ExcludeDefault, @ExcludeIdentity
WHILE (@@fetch_status <> -1)
BEGIN
--initialize some of the variables
SELECT @InsertStmt = 'INSERT INTO ' + @Table + ' (',
@Fields = '',
@SelList = 'SELECT ',
@FldCounter = 0

--create a cursor that loops through the fields in the table
--and retrieves the column names and determines the delimiter type that the
--field needs
DECLARE CR_Table CURSOR FAST_FORWARD FOR
SELECT COLUMN_NAME,
'IsChar' = CASE
WHEN DATA_TYPE in ('int', 'money', 'decimal', 'tinyint', 'smallint') THEN 0
WHEN DATA_TYPE in ('char', 'varchar','nvarchar','text','nchar','ntext' ) THEN 1
WHEN DATA_TYPE in ('datetime', 'smalldatetime') THEN 2
ELSE 9
END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = @table
AND DATA_TYPE <> 'timestamp'
And (COLUMN_DEFAULT is Null or @ExcludeDefault = 0)
And (columnproperty(object_id(@table), column_name,'IsIdentity') = 0 or @ExcludeIdentity = 0)
ORDER BY ORDINAL_POSITION
FOR READ ONLY


OPEN CR_Table
FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
IF @FldCounter = 0
BEGIN
SELECT @Fields = @Fields + @ColName + ', '
SELECT @SelList = CASE
WHEN @IsChar = 1 THEN @SelList + ' '''''''' + ISNULL( REPLACE('+ @ColName + ', '''''''', ''''''''''''),'''') + '''''''' +' + ' '
WHEN @IsChar = 2 THEN @SelList + ' '''''''' + ISNULL(CONVERT(varchar(20),' + @ColName + '),''12/30/1899'') + '''''''' +' + ' '
ELSE @SelList + 'ISNULL(CONVERT(varchar(2000),'+@ColName + '),0)' + '+'''' + '
END

SELECT @FldCounter = @FldCounter + 1

FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
END

If @@Fetch_Status <> -1
begin
SELECT @Fields = @Fields + @ColName + ', '
SELECT @SelList = CASE
WHEN @IsChar = 1 THEN @SelList + ' '','''''' + ISNULL(REPLACE(' + @ColName + ', '''''''',''''''''''''), '''') + '''''''' +' + ' '
WHEN @IsChar = 2 THEN @SelList + ' '','''''' + ISNULL(CONVERT(varchar(20),' + @ColName + '),''12/30/1899'') + '''''''' +' + ' '
ELSE @SelList + ' '','' + ISNULL(CONVERT(varchar(2000),'+@ColName + '),0)' + '+'
END
END

END
FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
END
CLOSE CR_Table
DEALLOCATE CR_Table


If @ExcludeIdentity = 0
Begin
Print 'Set Identity_Insert ' + @table + ' On'
Print 'Go'
End

SELECT @Fields = SUBSTRING(@Fields, 1,(len(@Fields)-1)) -- Remove Trailing comma
SELECT @SelList = SUBSTRING(@SelList, 1,(len(@SelList)-1)) -- Remove Trailing comma
SELECT @SelList = @SelList + ' FROM ' + @table
SELECT @InsertStmt = @InsertStmt + @Fields + ')'

--now we need to create and load the temp table that will hold the data
--that we are going to generate into an insert statement
CREATE TABLE #TheData (TableData varchar(8000))
INSERT INTO #TheData (TableData) EXEC (@SelList)

--Cursor through the data to generate the INSERT statement / VALUES clause
DECLARE CR_Data CURSOR FAST_FORWARD FOR SELECT TableData FROM #TheData FOR
READ ONLY
OPEN CR_Data
FETCH NEXT FROM CR_Data INTO @TableData
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT @InsertStmt + ' VALUES (' + @TableData + ')' + char(13) + 'GO'
END
FETCH NEXT FROM CR_Data INTO @TableData
END
CLOSE CR_Data
DEALLOCATE CR_Data
DROP TABLE #TheData

If @ExcludeIdentity = 0
Begin
Print 'Set Identity_Insert ' + @table + ' Off'
Print 'Go'
End

FETCH NEXT FROM CR_TableList INTO @table, @ExcludeDefault, @ExcludeIdentity
END
CLOSE CR_TableList
DEALLOCATE CR_TableList