Generating INSERT statements in SQL Server

• Download source - 1.18 KB

Introduction

The stored procedure InsertGenerator generates the INSERT..VALUES statements for the specified table name.

Background

SQL Server doesn�t allow generation of INSERT statements for the table data, when Generate SQL Script option is selected. The workaround is to make use of DTS for transferring data across servers. However, there exists a need to generate INSERT statements from the tables for porting data. Simplest example is when small or large amount of data needs to be taken out on a removable storage media and copied to a remote location, INSERT..VALUES statements come handy.

Using the code

This small yet useful stored procedure will take as parameter the table name and generates the INSERT SQL statements for the same. The output can be redirected to either text format (Ctrl+T in Query Analyzer) or Output to a text file. The procedure accepts an input varchar type parameter that has to be the table name under consideration for statement generation.

CREATE PROC InsertGenerator
(@tableName varchar(100)) as

Then it includes a cursor to fetch column specific information (column name and the data type thereof) from information_schema.columns pseudo entity and loop through for building the INSERT and VALUES clauses of an INSERT DML statement.

--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
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
    SET @stringData=@stringData+'''''''''+
            isnull('+@colName+','''')+'''''',''+'
END
ELSE
if @dataType in ('text','ntext') --if the datatype 

                                 --is text or something else 

BEGIN
    SET @stringData=@stringData+'''''''''+
          isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
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'')+''''''),''+'
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'')+'''''',''+'
END

SET @string=@string+@colName+','

FETCH NEXT FROM cursCol INTO @colName,@dataType
END

After both of the clauses are built, the VALUES clause contains a trailing comma which needs to be replaced with a single quote. The prefixed clause will only face removal of the trailing comma.

DECLARE @Query nvarchar(4000) -- provide for the whole query, 

                              -- you may increase the size


SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') 
    VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' 
    FROM '+@tableName
exec sp_executesql @query --load and run the built query

Eventually, close and de-allocate the cursor created for columns information.

CLOSE cursCol
DEALLOCATE cursCol

After the procedure is compiled and created, just run it in Query Analyzer by using the following syntax:

InsertGenerator <tablename>

E.g.:

USE pubs
GO
InsertGenerator employee 
GO

Then copy the INSERT statements and run�em in the query analyzer.

Before the INSERTs are run, SET IDENTITY_INSERT <TABLENAME>ON should be passed for adding values in an identity-based column.

Points of Interest

D: T-SQL lovers might wish to extend this procedure for providing support for binary data types such as IMAGE etc.

History

• Ver 0.1b added Dec 5, 03.

You must Sign In to use this message board.

FAQ    Noise Tolerance Very HighHighMediumLowVery Low   Layout Expand Posts & RepliesThread ViewNo JavascriptNo JS + Preview   Per page 102550

Msgs 1 to 25 of 44 (Total in Forum: 44) (Refresh) FirstPrevNext

Tks a Lot! FernandoMartin 3:10 18 Nov '09   Tks a Lot for this useful script! Sign In·View Thread·PermaLink Thanks !!! aqnysos123 6:11 29 Sep '09   Thanks for the script. Sign In·View Thread·PermaLink Thanks a lot! Uday Takbhate 3:41 7 Aug '09   I had a unique situation where I had to migrate a SQL DB to a server where I did not have any access but a web SQL prompt. The only way to migrate data was to execute the SQL insert script on the web SQL prompt. Your script helped a lot. -Uday udyaa Sign In·View Thread·PermaLink Thank you! BruceL 6:24 21 May '09   Thanks very much for this slick and very efficient piece of code. It saved me hours of work and I really appreciate your talent and creativity. Great code! Again, Thanks!!! Sign In·View Thread·PermaLink How to emulate SQLPUBWIZ? [modified] apergiel 13:10 11 May '09   How can I get this to emulate SQLPUBWIZ? I have five tables that I would like to script a INSERT query for. The resulting file would contain the INSERT code for all five tables. Perhaps a batch file that comes loaded with the 5 file names? Which in turn generates a 2nd .sql file. Which would then be executed on the remote Host. My attempts to do this were frustrated by the PRINT command getting confused with special characters ( ',", etc), When I was attempting to have TSQL code written to the resulting script file. Thanks modified on Tuesday, May 12, 2009 7:23 AM Sign In·View Thread·PermaLink fine Script, updated with schema of table spider_pat 2:26 5 Mar '09   with the little changes of table_schema for the table, so i can use this script now. ------------------------------------------------------------------------------------ CREATE PROC InsertGenerator (@tableName varchar(100)) as --Declare a cursor to retrieve column specific information for the specified table DECLARE cursCol CURSOR FAST_FORWARD FOR SELECT column_name,data_type,table_schema 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 DECLARE @schemaType nvarchar(100) --table schema value for correct schema execution SET @stringData='' DECLARE @colName nvarchar(50) FETCH NEXT FROM cursCol INTO @colName,@dataType,@schemaType IF @@fetch_status<>0 begin print 'Table '+@tableName+' not found, processing skipped.' close curscol deallocate curscol return END WHILE @@FETCH_STATUS=0 BEGIN IF @dataType in ('varchar','char','nchar','nvarchar') BEGIN --SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+' SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+' END ELSE if @dataType in ('text','ntext') --if the datatype is text or something else BEGIN SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+' 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='INSERT '+@schemaType+'.'+@tableName+'(' SET @string=@string+@colName+',' FETCH NEXT FROM cursCol INTO @colName,@dataType,@schemaType END DECLARE @Query nvarchar(4000) SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@schemaType+'.'+@tableName Print @query exec sp_executesql @query --select @query CLOSE cursCol DEALLOCATE cursCol GO Sign In·View Thread·PermaLink 3.50/5 (2 votes) Perfect!!!! Patrick_SURFThru 4:10 4 Feb '09   Just what I was looking for to make upload data in GoDaddy easier, since they do not allow you to connect to SQL Server from a local enterprise manager. Great work!!! Sign In·View Thread·PermaLink Muchas Gracias Galo Solís 10:10 19 Jan '09   Oye gracias muy bueno tu aporte!! Me salvaste de un buen trabajo Galo. Sign In·View Thread·PermaLink Thanks !!! Herewith Addition for inserting without taking the Primary Keys ( i.e. not having to run set identity_insert on ; .... [modified] yordan_georgiev 1:20 19 Nov '08   create PROCEDURE [dbo].[procUtils_InsertGenerator]      (      @domain_user varchar(50),      @tableName varchar(100)      )                  as            --Declare a cursor to retrieve column specific information for the specified table      DECLARE cursCol CURSOR FAST_FORWARD FOR            --old takes the PK's SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName      /* NEW without PK's */ select object_name(c.object_id) "TABLE_NAME", c.name "COLUMN_NAME", s.name "DATA_TYPE"      from sys.columns c            join sys.systypes s on (s.xtype = c.system_type_id)            where object_name(c.object_id) in (select name from sys.tables where name not like 'sysdiagrams')            AND object_name(c.object_id) in (select name from sys.tables where [name]=@tableName   ) and c.is_identity=0       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      DECLARE @IDENTITY_STRING nvarchar ( 100 )      SET @IDENTITY_STRING = ' '      select   @IDENTITY_STRING      SET @string='INSERT '+@tableName+'('      SET @stringData=''            DECLARE @colName nvarchar(50)            FETCH NEXT FROM cursCol INTO @tableName , @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      IF @dataType in ('varchar','char','nchar','nvarchar')      BEGIN      --SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'      SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'      END      ELSE      if @dataType in ('text','ntext') --if the datatype is text or something else      BEGIN      SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'      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 @tableName , @colName,@dataType      END      DECLARE @Query nvarchar(4000)            SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName      exec sp_executesql @query      --select @query         CLOSE cursCol      DEALLOCATE cursCol               /*      use MyDataBase go    DECLARE @RC int   DECLARE @domain_user varchar(50)   DECLARE @tableName varchar(100)      -- TODO: Set parameter values here.   set @domain_user='myDomainUser'   set @tableName = 'MyTableName'      EXECUTE @RC = [POC].[dbo].[procUtils_InsertGenerator]      @domain_user      ,@tableName      */ <div class="ForumMod">modified on Wednesday, November 19, 2008 1:41 PM</div> Sign In·View Thread·PermaLink thanks a lot! Member 2386861 11:16 8 Oct '08   this has been very useful to me. Sign In·View Thread·PermaLink Simpler way, which i preferred dRn-1 5:34 8 May '08   <code> -- MSSQL 2005 declare @table_name nvarchar(100) declare @columns      nvarchar(max) declare @values      nvarchar(max) declare @identity   bit declare @sql            nvarchar(max) set @table_name = 'MyTable' set @columns      ='' set @values      ='' set @identity   = 0 select      @identity = @identity | columnproperty(object_id(@table_name), column_name, 'IsIdentity'),      @columns   = @columns + ',' + '['+column_name+']',      @values   = @values + '+'',''+isnull(master.dbo.fn_varbintohexstr(cast(['+column_name+'] as varbinary(max))),''NULL'')' from      information_schema.columns where      table_name = @table_name and data_type != 'timestamp' set @sql = 'select ''insert into [' + @table_name + '] (' + substring(@columns,2,len(@columns)) + ') values (''+' + substring(@values,6,len(@values)) + '+'')'' from ' + @table_name if @identity=1 print 'set identity_insert [' + @table_name + '] on' exec sp_executesql @sql if @identity=1 print 'set identity_insert [' + @table_name + '] off' </code> Sign In·View Thread·PermaLink 4.14/5 (5 votes) Re: Simpler way, which i preferred galsont 0:58 12 Mar '09   You are right, this is much better! Sign In·View Thread·PermaLink Generated script will break on Varchar fields with ( ' ) Hanysm 5:36 5 Mar '08   I ran the SP and it did produced an inserts that breaks if the fields of type varchar include the (') character. Here is an updated version Hany~ -- --- SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO ALTER PROC InsertGenerator (@tableName varchar(100)) as --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 IF @dataType in ('varchar','char','nchar','nvarchar') BEGIN --SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+' -- SET @stringData=@stringData+''''+'''+isnull('''''+'''''+REPLACE('+ @colName +',char(39), (char(39) + char(39)+ char(39))) +'''''+''''',''NULL'')+'',''+' END ELSE if @dataType in ('text','ntext') --if the datatype is text or something else BEGIN SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+' 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 exec sp_executesql @query CLOSE cursCol DEALLOCATE cursCol GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO Sign In·View Thread·PermaLink Auto-Increment PK Columns Jacques Doubell 23:10 20 Nov '07   This SP does not take into account Auto-Increment PK Columns, so the generated sql statements have to be modified to work. Still a usefull SP though! Sign In·View Thread·PermaLink 2.40/5 (3 votes) Time saver pornoisfoei 22:44 26 Jun '07   Saved me a lot of time... Sign In·View Thread·PermaLink Very useful, saved a lot of time Omar Al Zabir 23:34 1 Apr '07   Thanks for the script. It was very helpful to me. Saved a lot of time. Works like a marvel. I am trying to generate INSERT script where each statement becomes more than 8192 character. It does not show on query window, nor does it get writen properly in output file properly. Statements get truncated because there are too many texts in the table. Do you have any solution? Regards, Omar AL Zabir Visual C# MVP Sign In·View Thread·PermaLink SQL Scripter tommyonline 5:32 28 Jan '06   Try SQL Scripter to generate data scripts. All commands supported: Insert, Update, Delete + Combinations... It's free. http://www.sqlscripter.com Sign In·View Thread·PermaLink 5.00/5 (1 vote) Re: SQL Scripter r_maiya 7:21 27 Mar '07   Thanks for the link. Even the stored proc is also good. Sign In·View Thread·PermaLink Escape the quotes Amit Gee 9:13 18 Jan '06   This nearly did what I needed it to do, except for escaping the quotes within the data. Perhaps you could update the stored procedure to fix this.. Sign In·View Thread·PermaLink 3.14/5 (5 votes) COLLATE milovanderlinden 3:19 18 Jan '06   I had some trouble generating the insert statements on converting to varchar because some of the collumns in my database used a different COLLATE. I therefor enhanced the statements with the COLLATE clauses needed: SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO ALTER PROC InsertGenerator (@tableName varchar(100)) as --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 print @colname + ' ' + @datatype IF @dataType in ('varchar','char','nchar','nvarchar') BEGIN SET @stringData=@stringData+''''+'''+isnull('''''+'''''+REPLACE('+@colName+','''''+''''','''''+'''''+'''''+''''')+'''''+''''',''NULL'')+'',''+' --SET @stringData=@stringData+'''''''''+isnull(['+@colName+'],'''')+'''''',''+' --SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+' END ELSE if @dataType in ('text','ntext') --if the datatype is text or something else BEGIN SET @stringData=@stringData+'''''''''+isnull(REPLACE(cast('+@colName+' as varchar(2000))COLLATE database_default ,'''''+''''','''''+'''''+'''''+'''''),'''')+'''''',''+' --SET @stringData=@stringData+'''''''''+isnull(cast(['+@colName+'] as varchar(2000)),'''')+'''''',''+' 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)) COLLATE database_default ,''0.0000'')+''''''),''+' END ELSE IF @dataType='timestamp' BEGIN SET @stringData=@stringData+'''''+ ''NULL'' +'',''+' 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) COLLATE database_default ,['+@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)) COLLATE database_default,''0'')+'''''',''+' END ELSE --presuming the data type is int,bit,numeric,decimal BEGIN --print @colname + 'other datatype' SET @stringData=@stringData+''''+ '''+isnull(convert(varchar(200),['+ @colName + ']) COLLATE database_default,''NULL'')+'',''+' --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 print 'All fields fetched' DECLARE @Query nvarchar(4000) SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName print @query exec sp_executesql @query --select @query CLOSE cursCol DEALLOCATE cursCol GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO Milo van der Linden GIS consultant AVD-ict Sign In·View Thread·PermaLink Null Values For DateTime Data Type YinkaAlalade 0:03 4 Nov '05   Your code does not accept null values to be inserted in datetime fields instead it generates 01/01/1900. How can I allow the code to insert nulls using the code? Thanks Yomi Sign In·View Thread·PermaLink 1.67/5 (3 votes) Error with type image sebastien971 11:01 29 Oct '05   IF @dataType='image' i have an error, it's stop like that : ,'ÿØÿà any idea ? Sign In·View Thread·PermaLink 2.00/5 (1 vote) ran into a cool tool which generates SQL Insert statements Padigela 11:01 14 May '05   I ran into a cool tool which generates SQL Insert statements for your data from SQLServer Database at http://www.codesforall.com/downloads/export.htm. Worth having a look at it if you are in the same line. Sign In·View Thread·PermaLink 1.33/5 (2 votes) presuming the data type is int,bit,numeric,decimal jaem007 3:35 13 May '05   I'm very new to SQL and this program has been great. I'm currently trying to make the output for int,bit,numeric and decimal come without the quotes, Any suggestions? Thanks. Jelias Sign In·View Thread·PermaLink Re: presuming the data type is int,bit,numeric,decimal Jaem007 12:24 13 May '05   Ok,I have found the answer: SET @stringData=@stringData+''''+'''+isnull(convert(varchar200),'+@colName+'),''NULL'')+'',''+' Thanks!! Sign In·View Thread·PermaLink

Last Visit: 4:38 21 Nov '09     Last Update: 4:38 21 Nov '09 12 Next »