Post reply

script some insert statements from some of the rows in an existing table

  • November 9, 2011 at 12:54 pm

    #262525

    I would like to generate multiple insert statements like below that l need to run later.

    insert table column1, column2, column3.... Values (......)

    insert tabel ..................

    I know I can use generate script from database task, select the table and script data to do this.

    But the problem is I don't want to script all the records in the table, but just some records that I can define using a query, how can I do that?

    Thanks

  • November 9, 2011 at 12:58 pm

    #1405437

    dynamic sql is about the only way i can think of to do that.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • November 9, 2011 at 4:14 pm

    #1405474

    sqlfriends (11/9/2011)

    I would like to generate multiple insert statements like below that l need to run later.

    insert table column1, column2, column3.... Values (......)

    insert tabel ..................

    I know I can use generate script from database task, select the table and script data to do this.

    But the problem is I don't want to script all the records in the table, but just some records that I can define using a query, how can I do that?

    Thanks

    Something like this:

    SELECT 'INSERT INTO TABLE_2 (Column1, Column2, Column3) VALUES (' + Column1 + ', ' + Column2 + ', ' + Column3 + ');' AS Command

    FROM TABLE_1

    WHERE Column1 > 1000

    AND Column2 = 5

  • November 9, 2011 at 4:32 pm

    #1405475

    Thank you, that helps a lot.

  • November 9, 2011 at 4:44 pm

    #1405476

    Narayana Vyas Kondreddi generate insert statements script is overloaded with a lot of parameters;

    for example

    exec sp_generate_inserts 'myTable', @from="FROM myTable WHERE somevalue > 42"

    or

    exec sp_generate_inserts 'myTable', @ommit_identity=1,@from="FROM myTable WHERE somevalue > 42"

    http://vyaskn.tripod.com/code/generate_inserts_2005.txt

    it's been in my SQl toolbox for years, and i use it often.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 9, 2011 at 4:46 pm

    #1405477

    Thanks a lot, will look into it.

  • November 9, 2011 at 9:56 pm

    #1405503

    You can use the below code format:

    INSERT INTO <Tablename> (Col1, Col2, Col3)

    SELECT Col1,Col2, Col3

    FROM <table name>

    WHERE <Condition>

  • November 10, 2011 at 9:12 am

    #1405855

    Thanks, for this one actually I do want to generate multiple insert statements for another production enviroment for release. And it's a delta data script.

Viewing 8 posts - 1 through 7 (of 7 total)

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