Insert Data Into Parent And Then Child Tables

Amit Shrivastava

11y2.8k

i am trying to figure out how i can create an SSIS package to insert into multiple tables. After the first insert, I want to take the ID created (an Identity column) and then use that to insert into other associated (foreign key) tables.

For example, I have a table Products. The primary key is an Identity column. Once the SSIS insert is complete, the bulk load of new Product has an identity ID value for each row. What I want to do, during the same SSIS package, is to take each row as it is inserted and add rows to other tables. Like, ProductsImages - it has a foreign key for the Product id and a foreign key to the ProductsImages being added. And, as part of this I will need to get the latest ID value and possibly some other values and store them in variables.

Thanks

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Priyaranjan K S11y ago
Accepted answer
Hi Amit,

Please refer this blog for a better understanding of the approach that we can use in this scenario :
http://agilebi.com/jwelch/2010/10/29/insert_parent_child_pattern1/

Thanks,
Priyan

If you found this useful Please mark this as Answer.
Priyaranjan K S11y ago
Glad I could help Amit . Cheers !
Amit Shrivastava11y ago
hello sir
thanks for your help
Ravi Kumar11y ago
you can do in stored procedure

create procedure usp_insertproduct
(
@productname nvarchar(200),
@subproductname nvarchar(200),
)
begin
insert into product(productname)values(@productname)
declare @productid numeric(18,2)
set @productid=(select top 1 productid from product order by productid desc)
insert into subproduct(productid,subproductname)values(@productid,@subproductname)
end