Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert Script Generator Expand / Collapse
Author
Message
Posted Monday, December 29, 2008 10:21 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 3:27 AM
Points: 313, Visits: 382
Comments posted to this topic are about the item Insert Script Generator
Post #627013
Posted Monday, January 12, 2009 12:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:53 AM
Points: 2,502, Visits: 2,384
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 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'
Post #634497
Posted Monday, January 12, 2009 4:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 3:27 AM
Points: 313, Visits: 382
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.


Post #634557
Posted Monday, January 12, 2009 9:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:14 AM
Points: 140, Visits: 312
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.
Post #634792
Posted Monday, January 12, 2009 10:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:53 AM
Points: 2,502, Visits: 2,384
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.



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



Post #634819
Posted Monday, January 12, 2009 10:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 4:25 PM
Points: 6, Visits: 206
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.
Post #634858
Posted Monday, January 12, 2009 6:40 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:17 AM
Points: 868, Visits: 1,131
I like it...except for the cursor ;)

Carlton..
Post #635129
Posted Tuesday, January 13, 2009 2:14 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 3:27 AM
Points: 313, Visits: 382
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):)






Post #635216
Posted Tuesday, January 13, 2009 7:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:14 AM
Points: 140, Visits: 312
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),
@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

END
GO
BEGIN
DECLARE @TableId INT
SET @TableId = OBJECT_ID('tbl')
EXEC dbo.InsDataGenerator @TableId
END
GO

Post #635421
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse