Script Tbl Data to Insert Stmts. - Handles Unicode
Script Table Data to Insert Statements - Handles Unicode datatypes (nchar, nvarchar, ntext). This script works with SQL 2000 and has been improved to handle properly the nchar, nvarchar, and ntext data types for Unicode, as well as handle apostrophes in data correctly. Usage is to run the script to create the stored proc in the database that contains the table you want to script inserts for, then call the proc in the context of that database as follows: exec InsertGenerator @tableName = 'myTableName' ... Original script from, and thanks go to Sumit Amar at,
http://www.codeproject.com/database/InsertGeneratorPack.asp.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
drop PROC InsertGenerator
go
CREATE PROC InsertGenerator
(@tableName varchar(100)) as
/*
Note by Norm Enger, 12/22/2005.
Original script from, and thanks go to Sumit Amar at,
http://www.codeproject.com/database/InsertGeneratorPack.asp.
*/
--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName
OPEN cursCol
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns
SET @string='INSERT '+@tableName+'('
SET @stringData=''
DECLARE @colName nvarchar(50)
FETCH NEXT FROM cursCol INTO @colName,@dataType
IF @@fetch_status<>0
begin
print 'Table '+@tableName+' not found, processing skipped.'
close curscol
deallocate curscol
return
END
WHILE @@FETCH_STATUS=0
BEGIN
--Old varchar/char/nchar/nvarchar handling (split and enhanced by Norm Enger 12/22/2005)
--IF @dataType in ('varchar','char','nchar','nvarchar')
--BEGIN
----SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'
--SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'
--END
--ELSE
--New varchar/char/nchar/nvarchar handling (split and enhanced by Norm Enger 12/22/2005)
--modified by Norm Enger to handle unicode nchar and nvarchar inserts better
--and handle bug with apostrophes in text...
IF @dataType in ('varchar','char')
BEGIN
--SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+REPLACE('+@colName+','''''''','''''''''''')+'''''+''''',''NULL'')+'',''+'
END
ELSE
IF @dataType in ('nchar','nvarchar')
BEGIN
--SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'
SET @stringData=@stringData+'''N'+'''+isnull('''''+'''''+REPLACE('+@colName+','''''''','''''''''''')+'''''+''''',''NULL'')+'',''+'
END
ELSE
--Old text/ntext handling (split and enhanced by Norm Enger 12/22/2005)
--if @dataType in ('text','ntext') --if the datatype is text or something else
--BEGIN
--SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
--END
--New text/ntext handling (enhanced by Norm Enger 12/22/2005)
--modified by Norm Enger to handle unicode ntext inserts better
--and handle bug with apostrophes in text...
if @dataType in ('text','ntext') --if the datatype is text or ntext
BEGIN
IF @dataType = 'text'
BEGIN
SET @stringData=@stringData+'''''''''+REPLACE(isnull(cast('+@colName+' as varchar(4000)),''''),'''''''','''''''''''')+'''''',''+'
END
ELSE --ntext columns
BEGIN
--Handle/preserve unicode characters for ntext columns (Norm Enger change)
--Also handle apostrophes (Norm Enger change)
SET @stringData=@stringData+'''N'+'''''''+REPLACE(isnull(cast('+@colName+' as nvarchar(4000)),''''),'''''''','''''''''''')+'''''',''+'
END
END
ELSE
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
BEGIN
SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
END
ELSE
IF @dataType='datetime'
BEGIN
--SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'
--SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
--SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
-- 'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
END
ELSE
IF @dataType='image'
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
END
ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN
--SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
--SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
END
SET @string=@string+@colName+','
FETCH NEXT FROM cursCol INTO @colName,@dataType
END
DECLARE @Query nvarchar(4000)
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName
--select @query
exec sp_executesql @query
CLOSE cursCol
DEALLOCATE cursCol
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO 
