Script all data of a table

  • Comments posted to this topic are about the item Script all data of a table

  • Nice script there, I too had written a similar script

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

    but the bad thing about mine would be the fact that I had made use of cursors....and also since it was written for sql server 2000 it has its limitation when tried in 2005.....

  • When I used it, there was an Error if the @column_names = 0. The word VALUES would concatenate at the end of the table name without a space(i.e. INSERT INTO dbo.FolderLevelLookupVALUES (0, 'None')

    ) . I just added a space before the word VALUES to fix it in the script.

    ...

    PRINT ' '' VALUES ('' +'

    ...

    Great script. Thanks


    Kindest Regards,

    David Petersen
    dipetersen.com

  • Linson.Daniel (6/17/2009)


    Nice script there, I too had written a similar script

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

    but the bad thing about mine would be the fact that I had made use of cursors....and also since it was written for sql server 2000 it has its limitation when tried in 2005.....

    Hi Daniel

    Sorry for the very late answer!! Seems I lost the response mail...

    Just had a look at your script, maybe you should include QUOTENAME or REPLACE to avoid SQL injection by "'". For smaller tables cursors should be okay.

  • dipetersen (7/10/2009)


    When I used it, there was an Error if the @column_names = 0. The word VALUES would concatenate at the end of the table name without a space(i.e. INSERT INTO dbo.FolderLevelLookupVALUES (0, 'None')

    ) . I just added a space before the word VALUES to fix it in the script.

    ...

    PRINT ' '' VALUES ('' +'

    ...

    Great script. Thanks

    Thanks for the feedback! 🙂

    Thanks also for the bug report, I will fix this. Usually it takes some days until it becomes updated by admins.

  • Hi,

    Excellent Script.

    I m not sure whether you have updated it or not.

    But it gives error in some cases where datatype is used as DbStamp of in case of Datetime.

    Thanks,

    Chirag.

  • Hi Craig

    Chirag Prajapati (8/10/2009)


    But it gives error in some cases where datatype is used as DbStamp of in case of Datetime.

    Thanks for the feedback!

    Could you explain the error and how it happens? What do you mean with DbStamp?

    Greets

    Flo

  • Hi,

    Love the script.

    I had a small problem when I tried running it due to the collation being wrong. I was getting the error

    Cannot resolve collation conflict for column 1 in SELECT statement.

    To fix this I changed the following at the end of the script

    -- Script the end of the statement

    PRINT ' '') COLLATE Latin1_General_CI_AS'''

    PRINT ' FROM ' + @table_name

    This then worked a treat.

    Thanks again

  • Nice post.

    Here's a script to just update data in a particular column in a table

    select

    'update @tablename set @columnname = ',

    '''' + @columnname + '''',

    'where @primarykeycolumn = ',

    @primarykeycolumn

    from @tablename

    It generates a script which when run, updates that particular column in that table.

  • I missed this before, Flo... it's written well enough that it probably should have been an article. Well done.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks, Jeff!

    I intend to update the script to add some missing data types like HIERARCHYID, UDTs, GEOMETRY and GEOGRAPHY.

    Wish you best!

    Flo

    PS: Sorry for being out of the forums since a long time. I'm quiet busy with new projects. I'll be back as soon as possible!

  • Hi,

    I tried this on SQL Server 2000 but getting error on VARCHAR(MAX) line.

    Will it not work on SQL Server 2000?

    Thanks,

    Vivek

  • Hi Vivek

    The script was made for SQL Server 2005. However, if you change VARCHAR(MAX) to VARCHAR(8000) it might work, if your tables are not too wide.

    In addition change:

    SELECT @object_id = object_id, @schema_id = schema_id

    FROM sys.tables

    WHERE object_id = OBJECT_ID(@table_name)

    ... to something like this:

    SELECT @object_id = id, @schema_id = schema_id

    FROM systables

    WHERE id = OBJECT_ID(@table_name)

    Unfortunately I cannot test it on 2000 at the moment.

    Hope this helps

    Flo

  • Hi Florian,

    Still getting few errors -

    WHERE TABLE_SCHEMA = SCHEMA_NAME(@schema_id)

    'SCHEMA_NAME' is not a recognized function name.

    DECLARE @select VARCHAR(MAX)

    Line 82: Incorrect syntax near 'MAX'.

    Please suggest on this.

    Thanks,

    Vivek

  • Hi Florian,

    Resolve the issue replacing -

    DECLARE @select VARCHAR(MAX)

    With

    DECLARE @select VARCHAR(8000)

    &

    changing this query

    SELECT @object_id = object_id, @schema_id = schema_id

    FROM sys.tables

    WHERE object_id = OBJECT_ID(@table_name)

    to

    SELECT @object_id = id, @schema_id = schema_id

    FROM sysobjects

    WHERE id = OBJECT_ID(@table_name) and type = 'U'

    But still getting error at schema_id

    Please help on this.

    Thanks

    Vivek

Viewing 15 posts - 1 through 15 (of 37 total)

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