SQL INSERT INTO WITH SELECT query

Question

Having no experience with SQL, I was hoping someone would help me with this.

I have an empty temp table, as well as a table with information in it.

My plan for my request, as it is, 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 pass the request to a number of identifiers, and where the identifier corresponds to the identifier in Other_table , it should fill the temp table with this information.

Is it possible to store identifiers that do not match somewhere else (for example, another temporary table) in one query? Or in the same temporary table, only with field 1 = NULL in this case?

I need to do extra work on identifiers that have not been mapped, so I need some kind of access to them. I was hoping to do all this in this single request if this is the fastest way.

Edit:

Thanks for the help.

Sorry, now I see that my question is not entirely clear.

If Other_table contains identifiers 1 - 1000, and I pass identifiers 999, 1000 and 1001, I want the temporary table to contain information for 999 and 1000, and then also the record with identifier = 1001 with field 1 = NULL. I do not want identifiers 1 through 998 to be returned with Field1 = NULL.

Answer 1

You can use only one target table for each insert statement. so saving field1 as zero seems like a simple way:

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

the case statement returns a null id that is not listed.

Update
After you have updated the question, I recommend: First, insert the list of identifiers that you use in the in statement 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

You need to create another query for NOT IN Id

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

and another

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

What does it mean that I need to do additional work on identifiers that have not been matched? Could you add more detailed information, perhaps we can do this in one request, and not separately.

Update

Where is it from (ID1, ID2, ID3 ...)? Can we use a connection? How do you get what 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

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

Answer 3

The quickest fix for an existing solution is to get all the values ​​from another table that are not in your temporary table. I designated Field1 as NULL for all of these identifiers.

 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) 

Another 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]