July 15, 2009 at 1:46 pm
I have two tables 1) tblProduct 2)tblProductSizeWidthColor
I want to insert first into the tblProduct first, generate the primary key identity value 'fintProductId' and then insert into my second table called tblProductSizeWidthColor.
I'm trying to do both these inserts in a stored procedure.
I have 3 columns in my third table called tblRegularitems from where I'm dumping the data into these two tables. Those 3 columns contain product identifying data such as the sku number, etc. Im joining on the sku number to insert into the tblproduct and checking to see if the product is not available in this table. IF it's not then I insert it by dumping data. This inturn generates the fintproductid for those products I added.
Now I need to insert those product details into the tblproductsizewidthcolor table which is linked to tblproduct thru the fintproductid. I already have identified the records that are missing in my third table against the tblproductsizewidthcolor table but can not get the fintproductid because there are already existing records in the tblproduct and I dont know how to seperate the ones i inserted.
Please see my code below:
INSERT INTO tblProduct(fstrShortSKU, fstrProductName,fmnyPrice, fbitShowLinkedSkuOnPage)
SELECTStyleCd, StyleDesc, Price, '0'
FROMtblRegularItems a
WHEREStyleCd IN
(SELECTri.StyleCd
FROMtblregularitems ri
LEFT JOINtblproductsizewidthcolor pswc
ONri.itemno=pswc.fstrlongsku and ri.edpno=pswc.fintecometryid
WHEREpswc.fstrlongsku is null and pswc.fintecometryid is null)
INSERT INTO tblProductSizeWidthColor (fintEcometryId, fstrLongSKU, fintProductId, fintSizeId, fintWidthId,
fintColorId, fstrCountryOfOrigin, fbitDeleted)
SELECT a.EdpNo,
a.ItemNo,
/*b.fintProductId*/
c.fintSizeId,
d.fintWidthId,
e.fintColorId,
f.CountryOrigin,
'0'
FROMtblRegularItems a
--LEFT JOINtblProduct bON a.StyleCd = b.fstrShortSku
LEFT JOINltblSize cON a.Level1Desc = c.fstrSizeValue
LEFT JOINltblWidth dON a.Level2Desc = d.fstrWidthValue
LEFT JOINltblcolor eON a.Level3Desc = e.fstrColorName
LEFT JOINtblCountryofOrigin fON a.EdpNo = f.Edpno
WHEREa.EdpNo IN
(SELECTri.EdpNo
FROMtblregularitems ri
LEFT JOINtblproductsizewidthcolor pswc
ONri.itemno=pswc.fstrlongsku and ri.edpno=pswc.fintecometryid
WHEREpswc.fstrlongsku is null and pswc.fintecometryid is null)
Any advice, further questions, information will help. Thanks in advance.
--
:hehe:
July 16, 2009 at 2:48 am
[font="Verdana"]It seems that the relation between Table: tblProduct and Table: tblProductSizeWidthColor is Parent - Child and before inserting records into tblProduct you are verifying whether the records is not exists and if not then you insert into tblProduct table then why you are again verifying record before inserting into tblProductSizeWidthColor. Just go on and insert into table tblProductSizeWidthColor directly instead of verifying the records again.
Let me know, on this,
Mahesh[/font]
MH-09-AM-8694
July 16, 2009 at 6:02 am
Take a look at the OUTPUT clause in the Books Online. That will allow you to capture the ID's as you create them and then use those ID's in the next set of inserts. It's exactly what you need.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 16, 2009 at 7:10 am
Grant,
Thank you for the advice. I actually did take a look at the OUTPUT clause on BOL. I too think it is what I need but wasn't sure. I will try to implement it as I haven't used it before.
Thanks,
S
--
:hehe:
July 16, 2009 at 7:19 am
Mahesh,
The problem is that I have no connection between the tblproduct and tblproductsizewidthcolor tables. Now in order for me to do the insert, I have to verify again, because if I verify against the tblproduct what happens is, it joins on ALL records giving me a result set of 30,000 plus. That is part of the problem that once I do insert in tblproduct I no longer just get a result set of 579 records (real number of records that are missing). However, BEFORE the insert I do!
I looked at the OUTPUT clause and I believe it could work out for me, I'm still trying to understand it though.
Thanks,
S
--
:hehe:
July 16, 2009 at 7:56 am
i don't get all things what you say but one thing i can suggest you for
if you want to insert data into two tables right dpends on first table to insert data into second table ...
now do one thing when you are inserting data in fist table create trigger on that
and from inserted data check that already exist in second table or not
if not then insert into second table ....
so from this it's very easy for you to do task for only one table insertion from procedure and data will be directly insert when trigger fire.......
May be this idea useful to you ....please tell me if you need more help
Raj Acharya
July 16, 2009 at 8:03 am
Raj,
That definitely came to my mind as well. The use of triggers to do multiple inserts. However, as far as query performance and efficiency is concerned, would this be effected by using triggers? Are you familiar with the OUTPUT clause? How would it compare to that?
Thanks,
S
--
:hehe:
July 16, 2009 at 8:06 am
I would strongly recommend you use the OUTPUT clause instead of triggers. Not least because, i think, you won't have access to the identity values inside the trigger.
OUTPUT is pretty easy to use. Since you want to do a multi-row insert, you simple create a table variable, OUTPUT the values you want into the table variable, and then use that table variable in the next insert, joining or whatever. There's a line by line example for doing this right in the Books Online.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 16, 2009 at 8:15 am
Grant,
Thanks! I believe you're right. I guess I'm scared of using the OUTPUT clause because I have so much currently going on in my sproc, that i'd hate to delete everything that I've worked on and start from scratch using this new method.
Even though it does make sense and might work, I'm just freaking out if this will achieve what I'm exactly trying to do and if not then I'd basically have wasted my time. Though, come to think of it, I'm wasting time right now deciding or not doing anything anyway.
Guess I should get to work on this! Will let you guys know.. by the way, there is another article on SQLTeam.com which I found explaining the OUTPUT clause very well. I don't know why but I find the BOL explanations very hard to understand.
Article for reference:
Thanks,
S
--
:hehe:
July 16, 2009 at 1:26 pm
Grant,
Just thought I'd update you and everyone else here that I was successful with the OUTPUT clause. It actually is easy to implement.
I have all the fintproductid's in my table variable along with the sku , so ill join based on the sku and get all the fintproductids for my second insert.
AWESOME!!!!!!!!!!! :w00t:
Thank you!
S
--
:hehe:
July 16, 2009 at 3:21 pm
Okay, I actually got excited a little earlier than I should have gotten.
OUTPUT clause it seems will benefit only in a situation where there is a ONE-to-ONE relationship between the first and second tables. The problem with my situation is that my table tblproduct has a one-to-many relationship with tblregularitems.
Any advice?
--
:hehe:
July 16, 2009 at 8:57 pm
yeah, it shouldn't be an issue.
Let's assume your data looks something like this:
Columns in Table 1:
ParentID KeyVal1 KeyVal2 Some Data
Columns in Table2:
ChildID ParentId Some Data
But in the data as you import it, presumably, you have the key data fields in both sets of data, so you can identify which ones belong to which (without SOMETHING to relate the data, how do you do it?).
So, when you insert the data, you capture the ID's into a table variable so you can use them for the next set of inserts. You also capture (or recapture or however you want to think of it) the key value data. Then you insert into the child table, joining to the table variable on the key values and using the ParentID generated out of the OUTPUT clause. That's about it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 16, 2009 at 10:04 pm
Grant,
I think i'm missing something here. OKAY I do have key values... and thats what I use to join..
but.....
the child table does not have the key values.. because it does not have those products... so even though my table variable contains the key value column and the primary key identity value.......... how do i join to it from the child table those records are not in the child table?
Am I making sense?
--
:hehe:
July 17, 2009 at 5:57 am
Yeah, you're making sense, apparently I'm not.
I didn't mean that the child table would have those values, instead it has the FK to the PK of the parent, right? No, I meant that the raw data that you're receiving from somewhere must have the means of joining back out to the parent data. Whatever means you're using to move that into the system, you should be able to join it with the OUTPUT data, presumably through the key values, not the ID's. But by joining through the key values, you get the ID's.
Does that make more sense?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 17, 2009 at 7:49 pm
I'd like to thank Grant and everybody else that contributed to this topic.
However, I chose a different route from the various above mentioned solutions, OUTPUT clause being the definite winner.
I relied on my joins but all I did was look for all the distinct values from my tblRegularItems to the tblProduct (one to many relationship) and used my join based on those distinct values to insert my data.
Once I had all my data into tblProduct, I basically joined all the values missing from tblProductWidthSizeColor that were available in tblRegularItems and got my fintProductID from tblProduct through a left outer join.
This seems so simple right now explaining it but trying to figure this out was much harder. I believe, being new to the database environment definitely played a big role in making such a small issue into such a big one.
Anyhow.... though my deliverable will get delayed (and i'll be working over the weekend to meet the deadline) I'm definitely moving on and putting in the business logic required for our process flow.
Thanks again for all the help!
S - Relieved and Relaxed 😎
--
:hehe:
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply