Data script generator

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jacek Osuchowski » Data script generator

Data script generator

Rate Topic

Display Mode

Topic Options

Author

Message

JacekO

Posted Friday, February 20, 2009 7:19 AM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606

Comments posted to this topic are about the item Data script generator

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

Post #661274

Carlo Romagnano

Posted Wednesday, March 11, 2009 3:19 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 3:24 AM
Points: 1,996, Visits: 1,859

-- This proc print a statement to copy data from one table to another with same structure. e.g. transfering data between servers (production to develop)
-- I use it in SSMS associated with hot key ctrl+3. Select a table name and press ctrl+3.
CREATE PROC sp_insert(@table varchar(257),@alias varchar(128) = 'a')
AS
-- author 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 #673070

JacekO

Posted Wednesday, March 11, 2009 7:07 AM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606

The difference between your script and mine is the usage and the output it generates. The objective of my script is to generate a set of insert statements that can be stored in a file and taken offline or to another, not linked server.

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

Post #673218

ramu.valleti

Posted Monday, March 16, 2009 4:42 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, March 07, 2013 12:16 AM
Points: 381, Visits: 340

Hello there,

when i try to execute the procedure its throwing the following error,

" Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. "

Can any one help me what is the problem?

Regards,
Ramu

Ramu
No Dream Is Too Big....!

Post #676354

JacekO

Posted Monday, March 16, 2009 6:38 AM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606

Replace the VARCHAR declarations with NVARCHAR and let me know if it helped.

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

Post #676424

Linson.Daniel

Posted Monday, March 23, 2009 11:48 AM

Old Hand

Group: General Forum Members
Last Login: Sunday, June 09, 2013 3:21 AM
Points: 312, Visits: 369

Well Jack,

You might wanna have a look at this stored procedure that I had written a few months back......similar to what you were trying to achieve......

http://www.sqlservercentral.com/scripts/insert+script+generator/65407/

Using the above sp that I have written you could generate for multiple tables all at once , just pass the table names in a concatenated fashion......and oh by way it also supports all datatypes i.e to say the insert script would be generated taking into consideration the datatypes......except for the sql_variant datatype.......

ohh did I mention it cares of the single quotes too....... Cool

Cheers
Linson Daniel

Post #681694

« Prev Topic | Next Topic »

Permissions