Row by Row Insert Query

Question

The Below code works fine for single insert, but I need to do row by row insert starting E.EmpId 101 till the end.

Insert into Emp values (Col1,Col2,Col3,Col4,Col5)

select BarCode -1 as 'RefNumber' from table1 t1 where t1.Col=E.Col
,'Default Value'
,(select isnull(p.JoinDate,t.ReleaseDate) from table2 t2 where t2.Col=E.Col
,(SELECT LEFT(ACCOUNT+'0000000000', 10) from table3 where t3.Col=E.Col
,(select case when Col5 then else)...from table4 where t4.Col=E.Col

from EmpStagging E Where E.EmpId=101

Answer 1

To expand on what Viorel says: The error is due to that for one or more rows, one of more of the subqueries you have returns more than one row. Most likely, this indicate a logical error of yours. Maybe you have missed join condition. Or maybe the resulting table should have more than one row in the base table. But it could also be junk in the source tables.

If you want to find some sample rows, you can try various ranges. For instance

from EmpStagging E Where E.EmpId BETWEEN 100 AND 200

If this comes out clean, try 200 to 300 etc. If you get an error, try 100 to 150 etc and half-down until you have single row. Then you can run the subqueries for this ID to get a better understanding of the data.

Answer 2

Start with this - once you have validated it is returning the correct data, then change the where clause to >= 101.

 Select Col1 = t1.BatCode - 1
      , Col2 = 'Default Value'
      , Col3 = isnull(p.JoinDate, t.ReleaseDate)        --these tables are not referenced???
      , Col4 = left(ACCOUNT + '0000000000', 10)         --What table does ACCOUNT come from???
      , Col5 = Case When Col5 ... Then ... Else ... End --not a valid statement, no idea here
   From EmpStagging      e
  Inner Join table1     t1 On t1.Col = e.Col
  Inner Join table2     t2 On t2.Col = e.Col
  Inner Join table3     t3 On t3.Col = e.Col
  Inner Join table4     t4 On t4.Col = e.Col
  Where e.EmpId = 101;

I am assuming you tried to 'sanitize' the code - but the code isn't valid and no idea if this is really for SQL Server or some other product.

Answer 3

Hi @Mansoor Mohammed ,

Insert into Emp values (Col1,Col2,Col3,Col4,Col5)  
  
select BarCode -1 as 'RefNumber' from table1 t1 where t1.Col=E.Col  
,'Default Value'  
,(select isnull(p.JoinDate,t.ReleaseDate) from table2 t2 where t2.Col=E.Col  
,(SELECT LEFT(ACCOUNT+'0000000000', 10) from table3 where t3.Col=E.Col  
,(select case when Col5 then else)...from table4 where t4.Col=E.Col  
  
from EmpStagging E Where E.EmpId=101  

Your code does not seem to be a standard tsql statement. I tested it with my data and returned an error. Are you using SQL SERVER?

I watched the previous discussion, and you may not provide the complete code. As far as the code you tried, it will not return the error you mentioned.The'where E.EmpId >= 101' mentioned by Viorel-1 can solve your problem theoretically. But in fact it doesn't, which means that your code is different from what we saw.

Also, what does it mean to insert row by row? SQL can be inserted in batches, so there is no need to insert row by row.If you have to insert row by row, then you need the WHILE loop statement to achieve.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.