SQL Server Performance Forum – Threads Archive
How to avoid loops to insert into multiple tables
Hello, I have a stored proc which reads rows from Table A and inserts it into Table B and Table C. Scenario: Table A FieldA1 | FieldA2 | FieldA3 Table BFieldB1 | FieldB2 Table C
FieldC1 | FieldC2 | Field C3
There is a foriegn key relation between B and C. FieldB1 => FieldC1 When I insert a rows into B and C, it should be like this FieldB1 = Autogenerated (Primary Key)
FieldB2 = Field A1 FieldC1 = FieldB1
FieldC2 = FeildA2
FieldC3 = FeildA3 For this, I’m using a loop to read each row from Table A and First Inserting it the rows into Table B, then take the latest autogenerated number in Table B (using SCOPE_IDENTITY()) and then inserting rows into Table C (from Table A and the latest autogenerated number). Can I meet this requirement withour using a loop? Please advise. This loop is making the stored proc exec slowly. So its a Performance Issue. Thanks in Advance !! Vyas
Try this Insert into tableB(FieldB2) Select FieldA1 from tableA
Insert into tableB(FieldC2,FieldC3) Select FieldA2, FieldA3 from tableA Madhivanan Failing to plan is Planning to fail
Is fieldB2 unique row identifier in tableB?
No FieldB2 is just a normal field. FieldB1 is the primary key.
Did you try the queries I suggested? Insert into tableB(FieldB2) Select FieldA1 from tableA
Insert into tableC(FieldC2,FieldC3) Select FieldA2, FieldA3 from tableA
Madhivanan Failing to plan is Planning to fail
What is table_a pk, if any?
Madhivanan, Will these queries help me to enter max(FieldB1) in FieldC1? My first impression is it will not. Please advise. mmarovic, No PK in Table A
Try something like this:
declare @LastID int
—
declare @tableA table(
increment int identity(1,1),
fieldA1 <fieldA1 type>,
fieldA2 <fieldA2 type>,
fieldA3 <fieldA3 type>
)
—
select @LastID = max(fieldB1) from tableB
—
insert into @tableA(fieldA1, fieldA2, FieldA3) select * from tableA
—
set identity_insert tableB on
—
insert into tableB(fieldB1, fieldB2)
select @LastID + increment, fieldA1
from @tableA
—
if @@error <> 0 begin
set identity_insert tableB off
return <error code>
end
—
set identity_insert tableB off
—
insert into tableC(…)
select @lastID + increment, fieldA2, fieldA3
from @tableA
You may add transactions and more error handling if you need it…
This looks a gud solution. I’ll try it and let you know.. Thanks a lot
You are wellcome. Feedback is always appreciated.
]]>