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

Rate

1 (1)

Share

Share

Rate

1 (1)