question

MansoorMohammed-9831 avatar image
0 Votes"
MansoorMohammed-9831 asked EchoLiu-msft commented

Row by Row Insert Query

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

sql-server-generalsql-server-transact-sql
· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Did you try 'where E.EmpId >= 101'?


0 Votes 0 ·

no luck :(

0 Votes 0 ·
Viorel-1 avatar image Viorel-1 MansoorMohammed-9831 ·

What do you mean?


0 Votes 0 ·
Show more comments

Why exactly do you believe you need to do "row by row" insert? This is not a normal operation for SQL Server.

0 Votes 0 ·

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered JeffreyWilliams-3310 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Take a look at the statement again - the statement appears to be using VALUES clause to insert a row and each subquery therefore needs to return a single value. It also doesn't appear to be valid T-SQL statement but I could be wrong.

0 Votes 0 ·
JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

Hi @MansoorMohammed-9831,

 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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.