Insert statement with multiple Select statements in SQL Server

Question

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.

You can't have the same number in each otherwise you wouldn't be getting the error. I count 8 in the insert and 5 in the select — Ash Burlaczenko
– Ash Burlaczenko, Commented Apr 15, 2013 at 6:51
@AshBurlaczenko I think you missed 1,GETDATE(),100 from the last line — Priyank Patel
– Priyank Patel, Commented Apr 15, 2013 at 6:54

Damien_The_Unbeliever · Accepted Answer · 2013-04-15 06:58:12Z

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

user4765249 · Accepted Answer · 2015-04-08 17:07:26Z

0

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

answered Apr 8, 2015 at 17:07

user4765249

1

Collectives™ on Stack Overflow

Insert statement with multiple Select statements in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related