locked
Create SQL Data script RRS feed

  • Question

  • Hi All,

    I need to create a sql script for a SQL table data, the script should contain INSERT or UPDATE statement for each record based on the record if it exists in the destination table.

    This script would then be executed on the UAT database.

    There are some 500 records for which the script is to be generated.



    • Edited by S Prashant Tuesday, September 3, 2013 1:10 PM
    Tuesday, September 3, 2013 12:46 PM

Answers

  • Resolved this using the select query below

    SELECT '
    IF NOT EXISTS (SELECT 1 FROM TblName WHERE [LocationCode] = '''+LocationCode+''')
         INSERT INTO TblName ([LocationCode], [Area])
         VALUES ('''+LocationCode+''', '''+Area+''')
    ELSE
         UPDATE TblName SET [Area] = '''+Area+''' WHERE [LocationCode] = '''+LocationCode+'''
    '
    FROM TblName

    • Marked as answer by S Prashant Friday, September 6, 2013 12:48 PM
    Friday, September 6, 2013 12:48 PM

All replies

  • Use MERGE .

    Ref:http://technet.microsoft.com/en-us/library/bb510625.aspx


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, September 3, 2013 12:56 PM
  • Hi,

    I don't want to use MERGE, I need to create INSERT or UPDATE script for each record in the table, something like this;

    IF NOT EXISTS (SELECT [Location] FROM dbo.Locations WHERE LocationCode = N'0001')
         INSERT INTO dbo.Locations ([LocationCode], [LocationName])
         VALUES (N'0001', N'Americas')
    ELSE
         UPDATE dbo.Locations SET [LocationName] = N'Americas' WHERE [LocationCode] = N'0001'

    Tuesday, September 3, 2013 1:09 PM
  • Can you just explain more on why you do not want MERGE? This is looking MERGE is the ideal candidate for your case.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, September 3, 2013 1:13 PM
  • Hi,

    I have two different databases, and I need to create Insert/Update scripts for master data in the QA database table. This script would then be used in the UAT database which is on separate server.

    SSMS  has a feature to create INSERT scripts for the table but I need INSERT/Update script for a record. If the record exists then update else insert.

    Tuesday, September 3, 2013 1:16 PM
  • For creating script including data do below

    Go to database ->Right click and selct task-> Generate script->->Select Specific databse objects->click on advance-> Select of type of data to script->Schema and data(choose as per your requiremnt)

    Tuesday, September 3, 2013 1:24 PM
  • Ok, Still my suggestion is that, you use MERGE.

    If you have linked server between the above server and UAT, then you can use the same script pointing the source db from the first server. If you do not have or can not create a linked server, then take the insert script from the first server after the MERGE and create a new table in the UAT and load the data. Then use the same MERGE script. Considering you do not want to delete the existing records in the UAT database.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, September 3, 2013 1:29 PM
  • Hi Latheesh,

    Thanks for the suggestion, but I need to create the SQL with Insert/Update for each record.

    Are there any tools which generate this kind of script.

    SSMS has feature to generate insert scripts but not Insert/Update script for the record.

    Tuesday, September 3, 2013 1:36 PM
  • Resolved this using the select query below

    SELECT '
    IF NOT EXISTS (SELECT 1 FROM TblName WHERE [LocationCode] = '''+LocationCode+''')
         INSERT INTO TblName ([LocationCode], [Area])
         VALUES ('''+LocationCode+''', '''+Area+''')
    ELSE
         UPDATE TblName SET [Area] = '''+Area+''' WHERE [LocationCode] = '''+LocationCode+'''
    '
    FROM TblName

    • Marked as answer by S Prashant Friday, September 6, 2013 12:48 PM
    Friday, September 6, 2013 12:48 PM
  • Hi Prashant, Gald that you fixed your issue.

    I think if your table does not enforce uniqueness for LocationCode, then it will update all Area with the new one. Hope you will be aware of it. The other point is, you will be doing it in a cursor or while loop. If I were, I will use MERGE instead to avoid row by row operation.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, September 6, 2013 12:57 PM
  • Thanks Latheesh for the reply,

    LocationCode is unique key in the table, so no issues about the update.

    Also, the above query will generate the SQL script(Insert/Update) for each record, which was my requirement.

    Wednesday, September 18, 2013 8:25 AM