SQL INSERT INTO WITH SELECT查询

Not being experienced with SQL, I was hoping someone could help me with this.

没有经验的SQL，我希望有人可以帮助我。

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.

所以我将一大堆ID传递给查询，并且ID对应于Other_table中的ID，它必须使用此信息填充临时表。

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?

是否可以在同一查询中保存与其他位置不匹配的ID（比如另一个临时表）？或者对于相同的临时表，在这种情况下只有Field1 = NULL？

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.

我需要对未匹配的ID做额外的工作，所以我需要在某处随时访问它们。我希望在这一个查询中完成所有操作，如果这是最快的方法。

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.

如果Other_table包含ID 1 - 1000，并且我传入了ID 999,1000和1001，我希望临时表包含999和1000的信息，然后是ID为1001且Field1 = NULL的条目。我不希望ID 1 - 998与Field1 = NULL一起返回。

3 个解决方案

#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语句只能使用一个目标表。因此，将field1保持为null似乎是最简单的方法：

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.

case语句将返回null id，ID不在列表中。

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:

更新更新问题后，我建议这样做：首先，将您在in运算符中使用的ID列表插入另一个临时表：

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)

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

对现有解决方案的最快修复是获取临时表中不存在的其他表中的所有值。我已将所有这些Id的Field1标记为NULL。

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

其他方法是将它包含在同一个INSERT语句中

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]

#3

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

您必须为NOT IN Id创建其他查询

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

我需要对未匹配的ID做额外的工作是什么意思？你可以添加更多细节，也许我们可以在一个查询中完成它而不是单独的

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

这个（ID1，ID2，ID3 ......）来自哪里？我们可以使用加入吗？如果你的答案是肯定的，那么你会得到你想要的东西

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

#1