Not being experienced with SQL, I was hoping someone could help me with this.
I have a empty temp table, as well as a table with information in it.
My outline of my query as it stands is as follows:
CREATE TABLE [#Temp] (ID Int, Field1 Varchar)
INSERT INTO [#Temp]
SELECT ID, Field1
FROM [Other_table]
WHERE ID IN (ID1, ID2, ID3...)
So I'm passing a whole bunch of IDs to the query, and where the ID corresponds to an ID in Other_table
, it must populate the temp table with this information.
Is it possible to save the IDs that did not match somewhere else (say another temp table) within the same query? Or to the same temp table, just with Field1 = NULL in that case?
I need to do extra work on the IDs that were not matched, so I need ready access to them somewhere. I was hoping to do this all in this one query, if that's the fastest way.
Edit:
Thanks for all the help.
Apologies, I see now that my question is not entirely clear.
If Other_table contains IDs 1 - 1000, and I pass in IDs 999, 1000 and 1001, I want the temp table to contain the information for 999 and 1000, and then also an entry with ID = 1001 with Field1 = NULL. I don't want IDs 1 - 998 returned with Field1 = NULL.
You can only use one target table for each insert statement. therefore, keeping
field1
as null seems like the easy way to go:the case statement will return
null
id the ID is not in the list.Update
After you have updated the question, this is what I would recommend: First, insert the list of ids you are using in the
in
operator into another temporary table:then just use a simple left join: