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

Data script generator Expand / Collapse
Author
Message
Posted Friday, February 20, 2009 7:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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
Posted Wednesday, March 11, 2009 3:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:33 AM
Points: 2,542, Visits: 2,410
-- 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
Posted Wednesday, March 11, 2009 7:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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
Posted Monday, March 16, 2009 4:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 7, 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
Posted Monday, March 16, 2009 6:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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
Posted Monday, March 23, 2009 11:48 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
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.......

Cheers
Linson Daniel


Post #681694
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse