Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    11

    Unanswered: "Insert Into... Where..." with multiple criteria

    Good morning all.

    I'm sure this is pretty simple, but i'm failing at the moment.

    I want to run an insert sql script to insert new data into a table.

    Here is a copy of my script so far.

    Code:
    insert into TargetTbl (Prefix,Bet1,Updated,Key1,Key2,Key3,Bet2)
    select Prefix,Key3,GETDATE(),Key1,Key2,Key3,Key3
    from SourceTbl
    where ...
    This is where i get stuck... I only want records to be imported where the Prefix in SourceTbl is "A", and the fields "Key1" and "Key2" are not collectively already in TargetTbl.

    I should probably explain that i already have an UPDATE statement to cater for records where "Key1" and "Key2" are collectively in TargetTbl. I wanted to use a MERGE statement, but due to the unchangeable compatibility level of our database, its not going to happen.

    If someone could help or point me in the right direction, i'd greatly appreciate it!

    Thank you all.

  2. #2
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Practice doing a simple SELECT to verify you get the wanted rows:

    WHERE SourceTbl = 'A'
    AND NOT EXISTS
    (SELECT * FROM TargetTbl as TT WHERE tt.key1 = st.key1 AND tt.key2 = st.key2)

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You may also want to have a look at the MERGE statement.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Oct 2011
    Posts
    11
    Stolze, i cannot use the MERGE statement as the compatibility level of the database is too low, as previously stated.

    JarlH, thanks for your help. I was only missing the 'EXISTS' or 'NOT EXISTS' section off my script, all working now, thank you.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Sorry, I didn't know what you mean by "compatibility level".
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Tags for this Thread

Posting Permissions

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