December 29, 2008 at 10:21 pm
Comments posted to this topic are about the item Insert Script Generator
January 12, 2009 at 12:54 am
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 Romagnano
DECLARE
@s-2 varchar(8000)
,@i varchar(8000)
,@comma varchar(10)
,@iden INT
SET @alias = ISNULL(@alias,'a')
SET @s-2 = '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-2 = @s-2 + @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-2 = @s-2 + @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-2 + char(13) + char(10) + ' FROM ' + @table + ' ' + @alias
if @iden > 0
print 'SET IDENTITY_INSERT ' + @table + ' OFF'
January 12, 2009 at 4:22 am
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 ON
INSERT INTO DataLink (
LinkId
,LinkDisplay
,LinkAddress
,LinkStatus
)
SELECT
a.LinkId
,a.LinkDisplay
,a.LinkAddress
,a.LinkStatus
FROM DataLink a
SET IDENTITY_INSERT DataLink OFF
Now 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 is
The 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:
January 12, 2009 at 9:37 am
I couldn't get it to work without a minor tweak.
I changed the following snippets...
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 ''
to
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
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.
January 12, 2009 at 10:02 am
Linson.Daniel (1/12/2009)
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 ON
INSERT INTO DataLink (
LinkId
,LinkDisplay
,LinkAddress
,LinkStatus
)
SELECT
a.LinkId
,a.LinkDisplay
,a.LinkAddress
,a.LinkStatus
FROM DataLink a
SET IDENTITY_INSERT DataLink OFF
Now 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 is
The 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:
I use this script to transfer data between servers or database or similar table.
e.i.:
SET IDENTITY_INSERT DataLink ON
INSERT INTO DataLink (
LinkId
,LinkDisplay
,LinkAddress
,LinkStatus
)
SELECT
a.LinkId
,a.LinkDisplay
,a.LinkAddress
,a.LinkStatus
FROM remoteserver.db.dbo.DataLink a
where a.LinkId >= 1000
SET IDENTITY_INSERT DataLink OFF
Or I use it to start insert values:
INSERT INTO DataLink (
LinkDisplay
,LinkAddress
,LinkStatus
)
SELECT
'aaa' --a.LinkDisplay
,'bbb' --a.LinkAddress
,'ccc' --a.LinkStatus
UNION ALL
SELECT
'aa1' --a.LinkDisplay
,'bb1' --a.LinkAddress
,'cc1' --a.LinkStatus
UNION ALL
SELECT
'aa2' --a.LinkDisplay
,'bb2' --a.LinkAddress
,'cc2' --a.LinkStatus
January 12, 2009 at 10:49 am
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 '('+ case when @CharacterLen < 1 then 'max' else @CharacterLen end +')' 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 '+'''''''''+'+'replace(@'+@ColumnName + ', char(39), char(39)+char(39))' + '+'''''''''+' ELSE ''NULL'' END'+'+'',''+'
Thanks for posting the script - it'll come in handy here.
January 12, 2009 at 6:40 pm
I like it...except for the cursor 😉
Carlton..
January 13, 2009 at 2:14 am
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
John Brauer (1/12/2009)
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).
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 !!:D
Carlton, 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):)
January 13, 2009 at 7:47 am
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.
IF OBJECT_ID('dbo.InsDataGenerator') IS NOT NULL
DROP PROCEDURE dbo.InsDataGenerator
GO
CREATE PROCEDURE dbo.InsDataGenerator( @TableId INT )
AS
BEGIN
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),
@ColumnDataVARCHAR(MAX),
@SqlCmdNVARCHAR(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
END
GO
BEGIN
DECLARE @TableId INT
SET @TableId = OBJECT_ID('tbl')
EXEC dbo.InsDataGenerator @TableId
END
GO
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply