April 11, 2012 at 11:48 am
I need to update an item table(item_tbl1 ) but only if the item number is in item_tbl2 and not already in item_tbl1
I have 3000 inserts to do that look like this:
INSERT INTO item_tbl1
(SETID,INV_ITEM_ID, MFG_ID, MFG_ITM_ID, UNIT_OF_MEASURE, UPN_TYPE_CD, UPN_ID)
VALUES
('CORP1', '0031', '800281', '0031', 'EA', 'DOM', '800281000016')
INSERT INTO item_tbl1
(SETID,INV_ITEM_ID, MFG_ID, MFG_ITM_ID, UNIT_OF_MEASURE, UPN_TYPE_CD, UPN_ID)
VALUES
('CORP1', '0032', '800281', '0032', 'EA', 'DOM', '800281000029')
April 11, 2012 at 12:14 pm
Where's the data coming from? Where are those 3000 source rows?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2012 at 12:15 pm
Pretty light on details but does this help?
INSERT INTO item_tbl1
(SETID,INV_ITEM_ID, MFG_ID, MFG_ITM_ID, UNIT_OF_MEASURE, UPN_TYPE_CD, UPN_ID)
select 'CORP1', '0031', '800281', '0031', 'EA', 'DOM', '800281000016'
where not exists
(select 'CORP1', '0031', '800281', '0031', 'EA', 'DOM', '800281000016' from item_tbl2)
Of course this could be different depending on where the values are coming from. In other words, it would be preferred to make this a set based operation instead row by row inserts.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 11, 2012 at 12:30 pm
the 3000 rows are in an excel spreadsheet that I was emailed to insert in to my two tables(3000 for each table 6000 total)
I will copy these into studio and then run them was their plan but I already have some of these in the tables and I don't want to insert any that I do not have an item for in my master item table.
April 11, 2012 at 12:33 pm
Here is my suggestion. Create an empty table, insert the data there, then using SQL, you can insert the appropriate records into the appropriate tables in a set-base manner.
April 11, 2012 at 2:07 pm
I created another table that look like these
SETID INV_ITEM_ID MFG_ID MFG_ITM_ID PREFERRED_MFG
CORP1 0031 800281 0031 Y
CORP1 0031TM 800281 0031TM Y
CORP1 0032 800281 0032 Y
CORP1 0051 800281 0051 Y
CORP1 0051SP 800281 0051SP Y
CORP1 0052 800281 0052 Y
CORP1 0061 800281 0061 Y
CORP1 0062 800281 0062 Y
CORP1 0071 800281 0071 Y
CORP1 0072 800281 0072 Y
It has 3000 rows and I want to update it's sister table but only where the inv_item_id is in the master_item_tbl and not in the sister table mfg_item
April 11, 2012 at 2:24 pm
Write a select query first to help you identify the data you need to insert.
April 11, 2012 at 9:03 pm
Maybe somethin' like this'll do you right:
;WITH InData
(SETID,INV_ITEM_ID, MFG_ID, MFG_ITM_ID, UNIT_OF_MEASURE, UPN_TYPE_CD, UPN_ID)
AS (
SELECT 'CORP1', '0031', '800281', '0031', 'EA', 'DOM', '800281000016')
UNION ALL SELECT 'CORP1', '0032', '800281', '0032', 'EA', 'DOM', '800281000029'
)
INSERT INTO item_tbl1
SELECT * FROM InData i
INNER JOIN item_tbl2 i2 ON i2.INV_ITEM_ID = i.INV_ITEM_ID
LEFT OUTER JOIN item_tbl1 ON i1.INV_ITEM_ID = i.INV_ITEM_ID
WHERE i1.INV_ITEM_ID IS NULL
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply