SQL INSERT INTO WITH SELECT query

Question

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.

Answer 1

You can only use one target table for each insert statement. therefore, keeping field1 as null seems like the easy way to go:

INSERT INTO [#Temp]
SELECT ID, CASE WHEN ID IN (ID1, ID2, ID3...) THEN Field1 END
FROM [Other_table] 

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:

create table #tempIDs (id int)
insert into #tempIDs values(id1), (id2), (id3), ....

then just use a simple left join:

INSERT INTO [#Temp]
SELECT t1.ID, Field1 
FROM #tempIDs t1 
LEFT JOIN [Other_table] t2 ON(t1.id = t2.id)

Answer 2

The quickest fix to your existing solution is to get all the values from other table which doesn't exists in your temp table. I have marked Field1 as NULL for all those Id's.

CREATE TABLE [#Temp] (ID Int, Field1 Varchar)

INSERT INTO [#Temp]
    SELECT ID, Field1 
    FROM [Other_table] 
    WHERE ID IN (ID1, ID2, ID3...)

INSERT INTO [#Temp]
SELECT ID, NULL AS Field1
FROM [Other_Table]
WHERE ID NOT IN (SELECT DISTINCT ID FROM #Temp)

Other way is to include it in the same INSERT statement

CREATE TABLE [#Temp] (ID Int, Field1 Varchar(100))

INSERT INTO [#Temp]
    SELECT ID, 
        CASE WHEN ID IN (ID1,ID2....) THEN Field1
             ELSE NULL END AS 'Field1'
    FROM [Other_Table] 

Answer 3

You have to create other query for NOT IN Id's

SELECT ID, Field1 
into #temp1
FROM [Other_table] WHERE ID NOT IN (ID1, ID2, ID3...)

and other one

SELECT ID, Field1 
into #temp2
FROM [Other_table] WHERE ID IN (ID1, ID2, ID3...)

What does it mean I need to do extra work on the IDs that were not matched?could you add more detail, maybe we can do it in one query not a separate

Update

Where this (ID1, ID2, ID3...) come from ? Could we use join ? Than you wil get what do you want if your answer is Yes

Try this One

SELECT *  Into #temp 
FROM(
  SELECT ID, Field1 
  FROM [Other_table] 
  WHERE ID IN (ID1, ID2, ID3...)
  Union
  SELECT ID, Field1 
  FROM [Other_table] 
  WHERE ID NOT IN (ID1, ID2, ID3...)
)

or this

SELECT ID, Field1 into #temp
  FROM [Other_table] 
  WHERE ID IN (ID1, ID2, ID3...)
  Union
  SELECT ID, Field1 
  FROM [Other_table] 
  WHERE ID NOT IN (ID1, ID2, ID3...)