Data script generator

  • JacekO

    SSCertifiable

    Points: 6347

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

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

  • JacekO

    SSCertifiable

    Points: 6347

    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

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

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

    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

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

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