Data script generator

Question

Data script generator

JacekO

SSCertifiable

Points: 6610
More actions
February 20, 2009 at 7:19 am

#201470

Comments posted to this topic are about the item Data script generator
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Carlo Romagnano SSC-Insane Points: 22596 More actions · Answer 1

-- 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-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'

JacekO SSCertifiable Points: 6610 More actions · Answer 2

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.

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

ramu.valleti SSCrazy Points: 2229 More actions · Answer 3

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....!

JacekO SSCertifiable Points: 6610 More actions · Answer 4

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

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Linson.Daniel Ten Centuries Points: 1153 More actions · Answer 5

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