CODEWITHSUNDEEP
sql-server-2008
Priyank Patel
Priyank Patel

Reputation: 7006

Insert statement with multiple Select statements in SQL Server

I have a query to insert some records using multiple Select statements My query is as follows

    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

Even though I have right number of columns in my Select it gives me error all the time

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.

Can any one help out with this.

Upvotes: 1

Views: 10440

Answers (2)

user4765249
user4765249

Reputation: 1

You can do it if you put a union bewteen selects

like this:

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

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

You can't return multiple columns from a subselect (so the system is probably assuming one column from each subselect and counting five columns total, and not getting far enough to tell you that the subselects can only return 1 value.

I'm not clear why you haven't written it as a single query anyway:

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)

Unless the mapping through tbl_GridMaster and tbl_StoreGridMapping may not exist (and you want a null for StoreID, in which case you might want to replace the inner joins with right joins.

I'd also query the wisdom (somewhere along the line) of casting a column called CityName to an INT. Something's broken there (in naming, if nothing else).

Upvotes: 1

Related Questions