none
INSERT INTO with multiple UNIONS RRS feed

Answers

  • Hi -

    Yes, it can be append or comletely blow away - it doesn't really make a difference for my end goal. I just need ot ensure only unique records are added into MasterCodeList upon rebuild and I can't quite get it to work..

    Okay, if you only need to ensure uniqueness, then you might be able to use the DISTINCT keyword. For example:

    SELECT * INTO TableName FROM (SELECT DISTINCT Fields FROM Table1 INNER JOIN Table2...)

    Or maybe:

    SELECT DISTINCT * INTO Table FROM SELECT...

    Hope it helps...

    • Marked as answer by cdtakacs1 Tuesday, August 28, 2018 12:04 AM
    Monday, August 27, 2018 9:26 PM

All replies

  • Hi,

    What is the issue? Have you tried specifying the fields for MasterCodeList? For example:

    INSERT INTO MasterCodeList (AssetID, AssetAlias, etc...)

    Also, you will probably have to use the UNION as a subquery. For example:

    INSERT INTO MasterCodeList (...) SELECT FieldList... FROM (SELECT ... UNION SELECT...)

    Hope it helps...

    Monday, August 27, 2018 7:47 PM
  • Thank you, I will try that.

    How do I also insert only records that currently don't exit?

    You can either create a unique index or add a WHERE clause to your subquery. For example:

    INSERT INTO...() SELECT ... FROM (SELECT... UNION...) WHERE criteria_to_exclude_existing_records_here

    Hope it helps...

    Monday, August 27, 2018 8:15 PM
  • Hi,

    Not quite. The EXISTS subquery will need to have some correlation with the main query to give you what you need. For example:

    SELECT * FROM Table1 WHERE NOT EXISTS(SELECT ID FROM Table2 WHERE Table2.ID=Table1.ID)

    Hope it helps...

    Monday, August 27, 2018 8:52 PM
  • Hi,

    I wasn't paying close attention earlier. It seems you have switched from using an APPEND query to a Make Table query. Is this correct?

    What your last SQL statement will do is delete the table MasterCodeList, if it already exists, and then create a new one using the records from your subquery.

    Is this your intent now? If so, which records would you consider EXISTS already if we're deleting MasterCodeList anyway?

    Monday, August 27, 2018 9:08 PM
  • Hi -

    Yes, it can be append or comletely blow away - it doesn't really make a difference for my end goal. I just need ot ensure only unique records are added into MasterCodeList upon rebuild and I can't quite get it to work..

    Okay, if you only need to ensure uniqueness, then you might be able to use the DISTINCT keyword. For example:

    SELECT * INTO TableName FROM (SELECT DISTINCT Fields FROM Table1 INNER JOIN Table2...)

    Or maybe:

    SELECT DISTINCT * INTO Table FROM SELECT...

    Hope it helps...

    • Marked as answer by cdtakacs1 Tuesday, August 28, 2018 12:04 AM
    Monday, August 27, 2018 9:26 PM
  • Hi,

    Congratulations! Glad to hear you got it to work. Good luck with your project.

    Tuesday, August 28, 2018 3:35 PM