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]

• Carlo Romagnano

  SSC-Insane

  Points: 22746

  More actions

  March 11, 2009 at 3:19 am

  #957329

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

  March 11, 2009 at 7:07 am

  #957449

  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

  March 16, 2009 at 4:42 am

  #960110

  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

  March 16, 2009 at 6:38 am

  #960164

  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

  March 23, 2009 at 11:48 am

  #964639

  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