/**********************************************************************************************************

NAME: insert_statement_generator.sql

SYNOPSIS: Generates insert statements for Teradata using SQL Server metadata.

This is useful for easily migrating small tables (i.e. < 1000 rows)

from SQL Server to Teradata. DO NOT use on large tables.

DEPENDENCIES: The following dependencies are required to execute this script:

- SQL Server 2005 or newer

AUTHOR: Michelle Ufford, http://sqlfool.com

CREATED: 2012-07-26

VERSION: 1.0

LICENSE: Apache License v2

----------------------------------------------------------------------------

DISCLAIMER:

This code and information are provided "AS IS" without warranty of any kind,

either expressed or implied, including but not limited to the implied

warranties or merchantability and/or fitness for a particular purpose.

----------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------

-- DATE VERSION AUTHOR DESCRIPTION --

---------------------------------------------------------------------------------------------------------

20150619 1.0 Michelle Ufford Open Sourced on GitHub

**********************************************************************************************************/

-- User-defined variables --

DECLARE

@tableName NVARCHAR(128) = 'dbo.example_table'

, @Top VARCHAR(10) = 1000 -- Leave NULL for all rows

, @Execute BIT = 1

, @GenerateSchema BIT = 1

, @GenerateTruncate BIT = 1

, @TeradataDatabase VARCHAR(30) = 'mufford'

, @TeradataTable VARCHAR(30) = NULL -- Will generate if you leave NULL

-- Script-defined variables --

DECLARE @columnList TABLE (columnID INT);

DECLARE @TeradataTableName VARCHAR(60);

IF @TeradataTable IS NULL

SET @TeradataTableName = @TeradataDatabase + '.tmp_' + SUBSTRING(@tableName,PATINDEX('%.%',@tableName)+1,26);

ELSE

SET @TeradataTableName = @TeradataDatabase + '.' + @TeradataTable;

DECLARE @insertStatement NVARCHAR(MAX) = '' --= 'SELECT '

, @columnStatement NVARCHAR(MAX) = 'INSERT INTO ' + @TeradataTableName + ' ('

, @schemaStatement NVARCHAR(MAX) = 'CREATE TABLE ' + @TeradataTableName + '('

, @currentID INT

, @firstID INT;

INSERT INTO @columnList

SELECT column_id

FROM sys.columns

WHERE object_id = OBJECT_ID(@tableName)

ORDER BY column_id;

SELECT @firstID = MIN(columnID) FROM @columnList;

WHILE EXISTS(SELECT * FROM @columnList)

BEGIN

SELECT @currentID = MIN(columnID) FROM @columnList;

IF @currentID <> @firstID

BEGIN

SELECT

@columnStatement = @columnStatement + ','

, @schemaStatement = @schemaStatement + ','

, @insertStatement = @insertStatement + '+'',''+';

END

SELECT @columnStatement = @columnStatement + '"' + SUBSTRING(name, 1, 30) + '"'

FROM sys.columns

WHERE object_id = OBJECT_ID(@tableName)

AND column_id = @currentID;

SELECT @schemaStatement = @schemaStatement + '"' + SUBSTRING(c.name, 1, 30) + '" '

+ CASE

WHEN t.name = 'BIT' THEN 'BYTEINT'

WHEN t.name = 'TINYINT' THEN 'SMALLINT'

WHEN t.name = 'UNIQUEIDENTIFIER' THEN 'CHAR(38)'

WHEN t.name = 'DATETIME' THEN 'TIMESTAMP(3)'

WHEN t.name = 'MONEY' THEN 'DECIMAL(18,4)'

WHEN t.name = 'XML' THEN 'CLOB'

WHEN t.name IN ('SMALLDATETIME', 'DATETIME2') THEN 'TIMESTAMP(0)'

WHEN t.name IN ('NVARCHAR','NCHAR')

THEN SUBSTRING(t.name, 2, 10) + '(' + CAST(c.max_length / 2 AS VARCHAR(4)) + ') CHARACTER SET UNICODE NOT CASESPECIFIC'

WHEN t.name IN ('VARCHAR','CHAR')

THEN t.name + '(' + CAST(c.max_length AS VARCHAR(4)) + ')'

ELSE t.name

END

+ CASE

WHEN c.is_nullable = 1 THEN ' NULL'

ELSE ' NOT NULL'

END

FROM sys.columns AS c

JOIN sys.types AS t

ON c.system_type_id = t.system_type_id

WHERE c.object_id = OBJECT_ID(@tableName)

AND c.column_id = @currentID;

SELECT DISTINCT @insertStatement = @insertStatement

+ 'CASE WHEN ' + QUOTENAME(c.name) + ' IS NULL THEN ''NULL'' ELSE ' +

+ CASE

WHEN t.name IN ('tinyint','smallint','int','real','float','bit','decimal','numeric','smallmoney','bigint') /* number-based columns */

THEN 'CAST(' + QUOTENAME(c.name) + ' AS VARCHAR(' + CAST(c.precision AS VARCHAR(10)) + '))'

WHEN t.name IN ('datetime', 'date', 'datetime2', 'smalldatetime') /* date-based columns */

THEN '''''''''+' + 'CONVERT(VARCHAR(23),' + QUOTENAME(c.name) + ',126)' + '+'''''''''

WHEN t.name IN ('uniqueidentifier') /* guid columns */

THEN '''''''''+' + 'CAST(' + QUOTENAME(c.name) + ' AS CHAR(36))' + '+'''''''''

WHEN t.name IN ('XML') /* xml columns */

THEN '''''''''+' + 'CAST(' + QUOTENAME(c.name) + ' AS VARCHAR(MAX))' + '+'''''''''

ELSE '''''''''+REPLACE(' + QUOTENAME(c.name) + ','''''''','''''''''''')+''''''''' --'''+''''''+''' /* character-based columns */

END

+ ' END '

FROM sys.columns AS c

JOIN sys.types AS t

ON c.system_type_id = t.system_type_id

WHERE c.object_id = OBJECT_ID(@tableName)

AND c.column_id = @currentID;

DELETE FROM @columnList WHERE columnID = @currentID;

END;

SET @insertStatement = 'SELECT ' + CASE WHEN @Top IS NOT NULL THEN 'TOP (' + @Top + ') ' ELSE '' END + '''' + @columnStatement + ') VALUES (''+' + @insertStatement + '+'');'' FROM ' + @tableName + ' WITH (NOLOCK);';

IF @GenerateSchema = 1

SELECT @schemaStatement + ');' AS 'Execute this statement in Teradata to create the table:'

IF @GenerateTruncate = 1

SELECT 'DELETE FROM ' + @TeradataTableName + ';' AS 'Execute this statement in Teradata to truncate the table:'

IF @Execute = 1

EXECUTE sp_executeSQL @insertStatement;

ELSE

SELECT @insertStatement AS 'Execute this statement in SQL Server to generate commands:';