在SQL Server中插入一条包含多个Select语句的语句

Question

我有一个查询，使用多个Select语句插入一些记录我的查询如下

    INSERT INTO tbl_StreetMaster
    (
    StreetName,
    CityID,
    StartPoint,
    EndPoint, 
    StoreID,
    IsActive,
    CreationDate,
    CreatedBy
    )
    SELECT
    (SELECT a.StreetName,CAST(a.CityName AS INT),a.EndPointFrom,a.EndPointTo
     FROM #TempRecords a 
     WHERE NOT EXISTS
     (SELECT b.StreetID,b.StreetName FROM tbl_StreetMaster b 
   WHERE a.StreetName=b.StreetName and a.EndPointFrom=b.StartPoint and
   a.EndPointTo=b.EndPoint and CAST(a.CityName AS INT)=b.CityID and b.IsActive=1
     ))
     ,
     (SELECT  a.StoreID   FROM tbl_StoreGridMapping a
     inner join tbl_GridMaster b on a.GridID=b.GridID
      inner join #TempRecords c on b.GridCode=c.GridCode1
      WHERE NOT EXISTS
      (SELECT b.StreetID,b.StreetName FROM tbl_StreetMaster b
     WHERE c.StreetName=b.StreetName and c.EndPointFrom=b.StartPoint and
     c.EndPointTo=b.EndPoint and CAST(c.CityName AS INT)=b.CityID and b.IsActive=1))
     , 
     1,GETDATE(),100 

尽管我在Select中设置了正确的列数但总是会出现错误

The select list for the INSERT statement contains fewer items
than the insert list. The number of SELECT values must match the number of INSERT 
columns.

有谁能帮帮忙吗

Answer 1

不能从子选择中返回多个列(因此系统可能会假设每个子选择中有一列，并计算出总共有五列)，而没有足够深入地告诉您子选择只能返回一个值。

我不清楚为什么你没有写它作为一个单一的查询:

INSERT INTO tbl_StreetMaster
(
StreetName,
CityID,
StartPoint,
EndPoint, 
StoreID,
IsActive,
CreationDate,
CreatedBy
)
SELECT
    c.StreetName,CAST(c.CityName AS INT),c.EndPointFrom,c.EndPointTo,
    a.StoreID, 1,GETDATE(),100
FROM tbl_StoreGridMapping a
 inner join tbl_GridMaster b on a.GridID=b.GridID
  inner join #TempRecords c on b.GridCode=c.GridCode1
  WHERE NOT EXISTS
  (SELECT b.StreetID,b.StreetName FROM tbl_StreetMaster b
 WHERE c.StreetName=b.StreetName and c.EndPointFrom=b.StartPoint and
 c.EndPointTo=b.EndPoint and CAST(c.CityName AS INT)=b.CityID and b.IsActive=1)

除非通过tbl_GridMaster和tbl_StoreGridMapping的映射可能不存在(并且您希望StoreID的null，在这种情况下，您可能希望将inner join s替换为right join s。

我还将查询将名为CityName的列转换为INT的智慧(沿着这条线的某个地方)。有些东西坏了(在命名，如果没有别的)。

Answer 2

如果在select

之间使用并集就可以了

:

INSERT INTO table(elem1,elem2,elem3) SELECT elem1,elem2,elem3 from table1 union select elem1,elem2,elem3 from table2 union select elem1,elem2,elem3 from table3