SQL Server Script to Create INSERT Statements

Microsoft SQL Server logo

Though you can generate INSERT statements using SQL Server Management Services if the Generate Scripts functionality is enabled, this script may help in cases when it is not.

USE DatabaseName
GO
SET NOCOUNT ON

DECLARE @TableName VARCHAR(255)
DECLARE @IndividualInserts BIT

SET @TableName = 'TableName'
SET @IndividualInserts = 0

DECLARE @TableStructure TABLE (ColumnName VARCHAR(255), DataType VARCHAR(50), [MaxLength] INT, [precision] INT, [scale] INT, is_nullable BIT, PrimaryKey BIT, ColumnID INT)
DECLARE @ColumnName VARCHAR(255)
DECLARE @DataType VARCHAR(50)
DECLARE @MaxLength INT
DECLARE @precision INT
DECLARE @scale INT
DECLARE @is_nullable BIT
DECLARE @PrimaryKey BIT
DECLARE @ColumnID INT
DECLARE @SelectQuery VARCHAR(MAX) = ''
DECLARE @ResultQuery VARCHAR(MAX) = ''
DECLARE @SQL NVARCHAR(MAX) = ''
DECLARE @CrLf VARCHAR(10) = CHAR(13) + CHAR(10)

INSERT INTO @TableStructure
SELECT 
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
    c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) 'Primary Key',
	c.column_id
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID(@TableName) 
ORDER BY c.column_id

--SELECT * FROM @TableStructure

DECLARE TableCursor CURSOR FOR
SELECT * FROM @TableStructure 
ORDER BY ColumnID


OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @ColumnName, @DataType, @MaxLength, @precision, @scale, @is_nullable, @PrimaryKey, @ColumnID
WHILE @@FETCH_STATUS = 0
BEGIN
	--SELECT @ColumnName, @DataType, @MaxLength, @precision, @scale, @is_nullable, @PrimaryKey, @ColumnID
	SET @ColumnName = '[' + @ColumnName + ']'
	IF LEN(@SelectQuery)=0 SET @SelectQuery='SELECT' + @CrLf + '''INSERT INTO ' + @TableName + ' (' 
	ELSE SET @SelectQuery = @SelectQuery + ',' --@CrLf + ',' 
	SET @SelectQuery = @SelectQuery + @ColumnName -- + '   -- ' + @DataType

	IF LEN(@ResultQuery)=0 
	BEGIN
		IF @IndividualInserts=0 SET @ResultQuery = 'UNION ALL SELECT CASE WHEN ROW_NUMBER() OVER(ORDER BY ' + @ColumnName + ') <> 1 THEN '','' ELSE '''' END +' + @CrLf + '''('
		ELSE SET @ResultQuery= @CrLf + '(' 
	END
	ELSE SET @ResultQuery = @ResultQuery + ','
	SELECT @ResultQuery = 
	CASE
		WHEN @DataType LIKE '%CHAR' 
		THEN @ResultQuery + '''+ COALESCE('''''''' + ' + @ColumnName + ' + '''''''',''NULL'') +'''
		WHEN @DataType LIKE '%DATETIME' 
		THEN @ResultQuery + '''+ COALESCE(''CONVERT(DATETIME,'''''' + CONVERT(VARCHAR,' + @ColumnName + ',21) + '''''',21)'',''NULL'') +'''		
		WHEN @DataType LIKE 'NUMERIC%' 
		THEN @ResultQuery + '''+ COALESCE(CONVERT(VARCHAR,' + @ColumnName + '),''NULL'') +'''
		WHEN @DataType = 'TEXT' 
		THEN @ResultQuery + '''+ COALESCE('''''''' + CONVERT(VARCHAR,' + @ColumnName + ') + '''''''',''NULL'') +'''
		WHEN @DataType LIKE '%INT' 
		THEN @ResultQuery + '''+ COALESCE(CONVERT(VARCHAR,' + @ColumnName + '),''NULL'') +'''
		WHEN @DataType = 'FLOAT' 
		THEN @ResultQuery + '''+ COALESCE(CONVERT(VARCHAR,' + @ColumnName + '),''NULL'') +'''
		ELSE @ResultQuery + '''+ COALESCE(' + @ColumnName + ',''NULL'') +'''
	END
	--SET @ResultQuery = @ResultQuery + '   -- ' + @DataType	
	
	FETCH NEXT FROM TableCursor INTO @ColumnName, @DataType, @MaxLength, @precision, @scale, @is_nullable, @PrimaryKey, @ColumnID
END
CLOSE TableCursor
DEALLOCATE TableCursor

SET @SelectQuery = @SelectQuery + ') VALUES' 
IF @IndividualInserts=0 SET @SelectQuery = @SelectQuery + ''''
SET @ResultQuery = @ResultQuery + ')'' FROM ' + @TableName + ';'

SET @SQL = @SelectQuery + @ResultQuery

--SELECT @SelectQuery
--SELECT @ResultQuery

--SELECT @SQL

--PRINT @SelectQuery 
--PRINT @ResultQuery
EXEC sp_executesql @SQL

This script may not work in every single case, as there are data types not accounted for here.

 

Solving the ROW-00060 Error When Using SSIS with Oracle

Attunity Oracle Source in SSIS

I have built SSIS packages to load data from one Oracle database to another before, but never had I come across this error until recently:

“ROW-00060: Internal error: [dainsert,16] Source: Oracle Destination: Oracle Error Occurred @ after 403k records.”

Initially, I thought that some constraint had been violated on the destination data source – like an attempt at a NULL value being inserted into a NOT NULL column. I ran the package a few times and noticed that the row count was always in the neighborhood of 400k and that the package had run for hours. I thought it odd that so many rows could be inserted with no problem, and so a double- and triple-checked to make certain that the constraints on the source columns were the same as those on the destination ones.

Though there were no clear cut solutions explaining exactly why this error happens, several different people on different fora mentioned that they had overcome this particular error (at different row counts, not always 400k!) by using a connector from Attunity rather than the one that comes with the Oracle Client. Fortunately, this driver is made to work with SSIS and is distributed by Microsoft. This driver not only works with Oracle, but also with Teradata. Choose the version based on the version of SSIS you are using.

Version 2.0 for SQL 2012
Version 3.0 for SQL 2014
Version 4.0 for SQL 2016

After installing the connector into Visual Studio and restarting VS, you should be able to use it by selecting “Oracle Source” and/or “Oracle Destination” from the SSIS Toolbox.

Attunity Oracle Source in SSIS

After setting up the new source and destination connections, I was able to run the SSIS package to completion, loading over five million rows of data in less than an hour!