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.

Extensor

SELECT ID, Field1 FROM [Other_table] WHERE ID NOT IN (ID1, ID2, ID3...)? – Fathomless

Ok maybe I'm missing something, but that will just do the opposite of my current query; I want it to do both in the same query? Or the fastest way to achieve both results. – Extensor

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)

Sascha

Thanks @Zohar Peled. That was very helpful, except I want the results the other way round, my question was not clear. I have amended it. – Extensor

Answer 2

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...)

Sigil

Thanks, so you reckon I must do two queries, that's what I thought but wanted to make sure. The 'other work' means querying another server to get this information, that bit as far as I can tell not relevant to my question here. I want the temp table to have the information that is found, and the list of the IDs not found easily available. – Extensor

@Extensor i have updated my answer, You can use Union in this case and result store in same #temp-table – Sigil

case is a part of t-sql since at least sql server 2000. Unless the OP is somehow working with an older version, then your suggested solutions are over complications at best. – Sascha

it only one way how to get this data, you can add your one, i am trying to avoid case statement, yeap you can do the same with case. Thank you – Sigil

there are many ways to get the data, the question is which way is the best... why avoid using case? – Sascha

As i remember case doesnt use indexes, i have found this link, maybe i am wrong but its only my opinion – Sigil

Irrelevant in this case. comparing execution plans between my suggestion and yours, on a table with 100000 records, the case took less then half the time the union took. (32% vs 68%). – Sascha

Yeap in this case better use case statement you right! – Sigil

Answer 3

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] 

Womankind