Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2002
    Posts
    23

    Question Unanswered: Generate Scripts for existing records in a table

    I can use Generate Scripts tool to get scripts for tables, procedures etc. But now I have records in tables which I want all "Insert into ..." queries from sqlserver. My co-worker said I have to manually type all these lines for my data.sql. Do I have to? I guess there must be a way...

    Thanks,

    Lili
    DTS -> Oracle

  2. #2
    Join Date
    Mar 2002
    Location
    Edinburgh
    Posts
    8
    Try using DTS (Data Transformation services) which will allow you to recreate and / or rename the table and also lets you populate with original data.

    The fastest way to recreate tables as you can recreate your entire database if you want to.


  3. #3
    Join Date
    Apr 2002
    Posts
    23
    DTS is awsome but I need to deliver data.sql (of course with all other *.sql files) to the client so that they can install and drop database any time they mess it up :P
    DTS -> Oracle

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    It sounds like you want to give the client some base data. Could you not just BCP the data out into files and then create a command file that would truncate the tables and then BCP the data back in?

    Or do you need to create the INSERT statements?
    Example:
    Code:
    INSERT myTbl (col1,col2,col3) VALUES ('xxx','aaa',123)
    MCDBA

  5. #5
    Join Date
    Apr 2002
    Posts
    23
    Yes! base data. I need to insert statements. I just typed up some, but I'm wondering whether I can use a tool to make life easier

    and guru, could you read my post about "Create My Own Log (for functionality) "? Thank you!

    Lili
    DTS -> Oracle

  6. #6
    Join Date
    Apr 2002
    Location
    VA
    Posts
    18

    Script generation

    Following sql statement will help you to generate INSERT STATEMENT for your base table

    Table Name : BaseTable
    fields
    BName varchar(20)
    dt smalldatetime
    val int


    select 'Insert into BaseTable VALUES (' + '''' + bname + '''' + ','
    + '''' + convert(varchar(10),dt,101) + '''' + ','
    + '''' + convert(varchar(10),val) + '''' + ')'
    from basetable


    handle NULL part by using ISNULL function


    All the best
    Deepak Khattar

  7. #7
    Join Date
    Apr 2002
    Posts
    23
    Wow! Good idea. Silly me Thanks,

    Lili
    DTS -> Oracle

  8. #8
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    You know sometimes I get carried away. I wrote a stored procedure that will generate all the INSERT statements for a given table. I've attached the proc, it worked for me on 2000.

    Example on pubs
    Code:
    exec usp_CreateInsert discounts
    
    OUTPUT:
    INSERT  discounts ( discounttype, stor_id, lowqty, highqty, discount)
            VALUES ('Initial Customer',NULL,NULL,NULL,10.50)
    INSERT  discounts ( discounttype, stor_id, lowqty, highqty, discount)
            VALUES ('Volume Discount',NULL,100,1000,6.70)
    INSERT  discounts ( discounttype, stor_id, lowqty, highqty, discount)
            VALUES ('Customer Discount','8042',NULL,NULL,5.00)
    Last edited by achorozy; 04-05-02 at 22:48.
    MCDBA

  9. #9
    Join Date
    Apr 2002
    Posts
    23
    //@@ where is the stored procedure?
    DTS -> Oracle

  10. #10
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    It must have stoppped me because the extension of the file was '.sql', so I had to change it to '.txt'
    Attached Files Attached Files
    MCDBA

  11. #11
    Join Date
    Apr 2002
    Posts
    23
    wow, you are real sql guru //admire

    thank you for your help and sharing

    Have a nice weekend!

    Lili
    DTS -> Oracle

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •