SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Linson Daniel / Insert Script Generator / Latest Posts

RE: Insert Script Generator

Keith DuAime — Tue, 13 Jan 2009 07:47:57 GMT

I've also been doing a lot of scripting to generate procedures, tables, etc... I tweaked my process to accomplish the same task here. I really haven't tested it too much but is another direction. It is SQL2K5 though.[code]IF OBJECT_ID('dbo.InsDataGenerator') IS NOT NULL DROP PROCEDURE dbo.InsDataGeneratorGOCREATE PROCEDURE dbo.InsDataGenerator( @TableId INT )ASBEGIN IF NOT(EXISTS(SELECT * FROM sys.tables WHERE [object_id] = @TableId AND [type] = 'U')) BEGIN PRINT 'Table not found' RETURN END DECLARE @ColumnList VARCHAR(MAX), @ColumnData VARCHAR(MAX), @SqlCmd NVARCHAR(MAX) SET @ColumnList = '' SET @ColumnData = '' SELECT @ColumnList = @ColumnList + LOWER(c.NAME) + ',', @ColumnData = @ColumnData + CASE WHEN typ.NAME IN ('datetime','smalldatetime','char','nchar','varchar','nvarchar') THEN ''''''''' + ' ELSE '' END + CASE WHEN typ.NAME IN ('bigint','int','smallint','tinyint','bit','datetime','smalldatetime','smallmoney','money','real','decimal','numeric','float') THEN 'CAST(' ELSE '' END + 'RTRIM(' + LOWER(c.NAME) + ')' + CASE WHEN typ.NAME IN ('bigint','int','smallint','tinyint','bit','datetime','smalldatetime','smallmoney','money','real','decimal','numeric','float') THEN ' AS NVARCHAR(MAX))' ELSE '' END + CASE WHEN typ.NAME IN ('datetime','smalldatetime','char','nchar','varchar','nvarchar') THEN ' + ''''''''' ELSE '' END + ' + '','' + ' FROM sys.columns c INNER JOIN sys.types typ ON typ.user_type_id = c.user_type_id WHERE c.[object_id] = @TableId AND c.is_identity = 0 ORDER BY c.column_id SET @ColumnList = SUBSTRING(@ColumnList,1,LEN(@ColumnList)-1) SET @ColumnData = SUBSTRING(@ColumnData,1,LEN(@ColumnData)-8) SET @SqlCmd = 'select ' + @ColumnData + ' from ' + LOWER(OBJECT_NAME(@TableId)) CREATE TABLE #InsDataGenerator( SqlCmd NVARCHAR(MAX)) INSERT INTO #InsDataGenerator EXEC sp_executesql @SqlCmd UPDATE #InsDataGenerator SET SqlCmd = 'INSERT INTO ' + OBJECT_NAME(@TableId) + '(' + @ColumnList + ') VALUES(' + SqlCmd + ');' SELECT * FROM #InsDataGenerator ENDGOBEGIN DECLARE @TableId INT SET @TableId = OBJECT_ID('tbl') EXEC dbo.InsDataGenerator @TableIdENDGO[/code]

RE: Insert Script Generator

Linson.Daniel — Tue, 13 Jan 2009 02:14:42 GMT

First of all thank You John and Keith, u guys have found some serious flaws there....Keith, i must say I completely forgot about testing my script for that one particular scenario ...(well to be truthfull i really havent done an extensive test on it):D[quote][b]John Brauer (1/12/2009)[/b][hr]1) The script that is generated does not declare varchar(max) columns correctly (it declares them as varchar(-1), which results in a parsing error when the generated script is executed). [/quote]And John, thanks for the solutions to those flaws. however I did mention that the script would be for execution in sql 2000.But anyways now that you have given a work around for that too...it would work in sql 2005 too !!:DCarlton, yes I know that these cursors are not very nice.i hate cursors too which is why i am working on a similar script that would completely or atleast partially avoid them....Anyways thank you again fellas....I would update the scripts and post again .......(provided i find the time though):)

RE: Insert Script Generator

Carlton Leach — Mon, 12 Jan 2009 18:40:10 GMT

I like it...except for the cursor ;)Carlton..

RE: Insert Script Generator

John Brauer — Mon, 12 Jan 2009 10:49:20 GMT

Nice script Linson. I had to make a couple tweaks you might want to know about:1) The script that is generated does not declare varchar(max) columns correctly (it declares them as varchar(-1), which results in a parsing error when the generated script is executed). I resolved this using the following modification: ELSE @DeclareCol + '@'+@ColumnName +' '+ @DataType +CASE WHEN @CharacterLen IS NOT NULL THEN '('+ [b]case when @CharacterLen < 1 then 'max' else @CharacterLen end [/b] +')' ELSE '' END + ',' 2) Source data that contains embedded single quotes (e.g., "Murphy's Law") are not handled correctly. I resolved this using the following modification: THEN 'CASE WHEN @'+@ColumnName+' IS NOT NULL THEN '+'''''''''+'+[b]'replace(@'+@ColumnName + ', char(39), char(39)+char(39))'[/b] + '+'''''''''+' ELSE ''NULL'' END'+'+'',''+'Thanks for posting the script - it'll come in handy here.

RE: Insert Script Generator

Carlo Romagnano — Mon, 12 Jan 2009 10:02:40 GMT

[quote][b]Linson.Daniel (1/12/2009)[/b][hr]Carlos,First of all, great SP there...but let me tell you why mine is quite different from yours.1. Here my aim was to script the table s data in insert script format. this way the generated insert script could be run on any other server /database.if you look at the output of your SP which is SET IDENTITY_INSERT DataLink ONINSERT INTO DataLink ( LinkId ,LinkDisplay ,LinkAddress ,LinkStatus)SELECT a.LinkId ,a.LinkDisplay ,a.LinkAddress ,a.LinkStatus FROM DataLink aSET IDENTITY_INSERT DataLink OFFNow this is an insert script ..ofcourse but why is that the target table and the base table are the same.Hmm....did i run this wrong?;)Nope I dint...Anyways my point isThe SP that i had written would literally give you the insert script like this INSERT INTO [dbo].[DataLink] (LinkDisplay,LinkAddress,LinkStatus) values ('Whats New as of June 25,2008','http://www.google.ca',0)INSERT INTO [dbo].[DataLink] (LinkDisplay,LinkAddress,LinkStatus) values ('Test','http://www.google.ca',0)INSERT INTO [dbo].[DataLink] (LinkDisplay,LinkAddress,LinkStatus) values ('Whats New as of October 29th','http://www.google.ca',1)By providing it in this manner , the user can make use of it whereever he requires.I m not sure if i have put clearly across to you the basic difference b/w the two scripts.:hehe:[/quote] I use this script to transfer data between servers or database or similar table.e.i.:SET IDENTITY_INSERT DataLink ONINSERT INTO DataLink ( LinkId ,LinkDisplay ,LinkAddress ,LinkStatus)SELECT a.LinkId ,a.LinkDisplay ,a.LinkAddress ,a.LinkStatus FROM remoteserver.db.dbo.DataLink awhere a.LinkId >= 1000SET IDENTITY_INSERT DataLink OFFOr I use it to start insert values:INSERT INTO DataLink ( LinkDisplay ,LinkAddress ,LinkStatus)SELECT 'aaa' --a.LinkDisplay ,'bbb' --a.LinkAddress ,'ccc' --a.LinkStatusUNION ALLSELECT 'aa1' --a.LinkDisplay ,'bb1' --a.LinkAddress ,'cc1' --a.LinkStatusUNION ALLSELECT 'aa2' --a.LinkDisplay ,'bb2' --a.LinkAddress ,'cc2' --a.LinkStatus

RE: Insert Script Generator

Keith DuAime — Mon, 12 Jan 2009 09:37:11 GMT

I couldn't get it to work without a minor tweak.I changed the following snippets...[code] SELECT @ConctColNme = CASE WHEN @IdentityColumn = @ColumnName THEN '' ELSE @ConctColNme + @ColumnName + ',' END SELECT @DeclareCol = CASE WHEN @IdentityColumn = @ColumnName THEN @DeclareCol ELSE @DeclareCol + '@'+@ColumnName +' '+ @DataType +CASE WHEN @CharacterLen IS NOT NULL THEN '('+@CharacterLen+')' ELSE '' END + ',' END SELECT @DeclareCol2 = CASE WHEN @IdentityColumn = @ColumnName THEN '' ELSE @DeclareCol2 + '@'+@ColumnName + ',' END SELECT @DeclareCol3 = CASE WHEN @IdentityColumn = @ColumnName THEN '' [/code]to[code] SELECT @ConctColNme = CASE WHEN @IdentityColumn = @ColumnName THEN @ConctColNme ELSE @ConctColNme + @ColumnName + ',' END SELECT @DeclareCol = CASE WHEN @IdentityColumn = @ColumnName THEN @DeclareCol ELSE @DeclareCol + '@'+@ColumnName +' '+ @DataType +CASE WHEN @CharacterLen IS NOT NULL THEN '('+@CharacterLen+')' ELSE '' END + ',' END SELECT @DeclareCol2 = CASE WHEN @IdentityColumn = @ColumnName THEN @DeclareCol2 ELSE @DeclareCol2 + '@'+@ColumnName + ',' END SELECT @DeclareCol3 = CASE WHEN @IdentityColumn = @ColumnName THEN @DeclareCol3[/code]Our tables are designed with an identity field at the end and since the variables are set with '' when it finds the identity field it doesn't work. The variable has to be reassigned to itself.

RE: Insert Script Generator

Linson.Daniel — Mon, 12 Jan 2009 04:22:54 GMT

Carlos,First of all, great SP there...but let me tell you why mine is quite different from yours.1. Here my aim was to script the table s data in insert script format. this way the generated insert script could be run on any other server /database.if you look at the output of your SP which is SET IDENTITY_INSERT DataLink ONINSERT INTO DataLink ( LinkId ,LinkDisplay ,LinkAddress ,LinkStatus)SELECT a.LinkId ,a.LinkDisplay ,a.LinkAddress ,a.LinkStatus FROM DataLink aSET IDENTITY_INSERT DataLink OFFNow this is an insert script ..ofcourse but why is that the target table and the base table are the same.Hmm....did i run this wrong?;)Nope I dint...Anyways my point isThe SP that i had written would literally give you the insert script like this INSERT INTO [dbo].[DataLink] (LinkDisplay,LinkAddress,LinkStatus) values ('Whats New as of June 25,2008','http://www.google.ca',0)INSERT INTO [dbo].[DataLink] (LinkDisplay,LinkAddress,LinkStatus) values ('Test','http://www.google.ca',0)INSERT INTO [dbo].[DataLink] (LinkDisplay,LinkAddress,LinkStatus) values ('Whats New as of October 29th','http://www.google.ca',1)By providing it in this manner , the user can make use of it whereever he requires.I m not sure if i have put clearly across to you the basic difference b/w the two scripts.:hehe:

RE: Insert Script Generator

Carlo Romagnano — Mon, 12 Jan 2009 00:54:35 GMT

I use this one that is fast and do not use cursor. In SSMS I put it in hotkey ctrl+3. When I select the table I press ctrl+3 and I get the command to insert a table. In comments you'll find nullable and defaulted cols:CREATE PROCEDURE sp_insert(@table varchar(257),@alias varchar(128) = 'a')AS-- By Carlo RomagnanoDECLARE @s varchar(8000),@i varchar(8000),@comma varchar(10),@iden INT SET @alias = ISNULL(@alias,'a') SET @s = 'SELECT ' + char(13) + char(10) SET @i = 'INSERT INTO ' + @table + ' (' + char(13) + char(10) SET @comma = char(9) + ' ' SET @iden = 0 IF LEFT(@TABLE,1) = '#' SELECT @s = @s + @comma + @alias + '.' + c.name + CASE c.isnullable WHEN 1 THEN ' --NULLABLE' ELSE '' END + CASE WHEN c.cdefault <> 0 THEN ' --DEFAULTED' ELSE '' END ,@i = @i + @comma + c.name + CASE c.isnullable WHEN 1 THEN ' --NULLABLE' ELSE '' END + CASE WHEN c.cdefault <> 0 THEN ' --DEFAULTED' ELSE '' END ,@comma = char(13) + char(10) + char(9) + ',' ,@iden = @iden + COLUMNPROPERTY (c.id, c.name, 'IsIdentity') from tempdb.dbo.syscolumns c where c.id = object_id('tempdb.dbo.' + @table) AND c.name <> 'timestamp' AND c.iscomputed = 0 order by c.colorder ELSE SELECT @s = @s + @comma + @alias + '.' + c.name + CASE c.isnullable WHEN 1 THEN ' --NULLABLE' ELSE '' END + CASE WHEN c.cdefault <> 0 THEN ' --DEFAULTED' ELSE '' END ,@i = @i + @comma + c.name + CASE c.isnullable WHEN 1 THEN ' --NULLABLE' ELSE '' END + CASE WHEN c.cdefault <> 0 THEN ' --DEFAULTED' ELSE '' END ,@comma = char(13) + char(10) + char(9) + ',' ,@iden = @iden + COLUMNPROPERTY (c.id, c.name, 'IsIdentity') from syscolumns c where c.id = object_id(@table) AND c.name <> 'timestamp' AND c.iscomputed = 0 order by c.colorder if @iden > 0 print 'SET IDENTITY_INSERT ' + @table + ' ON' print @i + char(13) + char(10) + ')' print @s + char(13) + char(10) + ' FROM ' + @table + ' ' + @alias if @iden > 0 print 'SET IDENTITY_INSERT ' + @table + ' OFF'

Insert Script Generator

Linson.Daniel — Mon, 29 Dec 2008 22:21:34 GMT

Comments posted to this topic are about the item [B]Insert Script Generator[/B]