July 12, 2011 at 8:42 am
Hi,
I need to update/insert data in TableA. The data that I'm inserting is coming from 2 other tables/views. I would like to avoid overwritting entries if they are already present.
TableA Columns
machine_id; model; speed
TableB has most of the data, however if it's not present I would check TableC, but I want to avoid overwriting data from TableB with data from TableC, if record would be present in both tables
TableB/TableC have multiple columns but machine_id, model, speed is the Column present in all 3 tables.
I can use SELECT INSERT but then when I run this on table C it could overwrite the info already updated/inserted coming from TableB, thats what I would like to avoid. I'm planningt to do this using Stored procedure that will run 1-2/day using agent job/schedule and update info accordingly in TableA.
Thx for the help.
July 12, 2011 at 8:48 am
Without more data its hard to answer your question, but from what I can gather, you're trying to do an UPSERT basically. IE, insert data into Table A if it does not exist, and update records in Table A if they do exist.
The fact that you're using two tables has no real influence on this.
What you can do is something like,
UPDATE A
SET A.Fields = COALESCE(TableB.Fields, TableC.Fields, A.Fields)
FROM TableA A
JOIN TableB ON A.ID = TableB.ID
LEFT JOIN TableC ON A.ID = TableC.ID
INSERT INTO TableA (Fields)
SELECT COALESCE(TableB.Fields, TableC.Fields, '')
FROM TableB
LEFT JOIN TableC ON TableB.ID = TableC.ID
WHERE NOT EXISTS (SELECT 1 FROM TableA WHERE TableA.ID = TableB.ID)
July 12, 2011 at 9:03 am
Sample data
TableA
machine_id model speed
598 Intel(R) Celeron(R) CPU 560 @ 2.13GHz 2128
700 Intel(R) Celeron(R) CPU 560 @ 2.13GHz 2127
800 Intel(R) Core(TM)2 Duo CPU E6750 @ 2.66GHz2660
TableB
machine_id model speed
598 Intel(R) Celeron(R) CPU 560 @ 2.13GHz 2128
700 Intel(R) Celeron(R) CPU 560 @ 2.13GHz 2127
878 Intel(R) Core(TM)2 Duo CPU E6750 @ 2.66GHz2660
TableC
machine_id model speed
598 Intel(R) Celeron(R) CPU 560 @ 2.13GHz 2128
720 Intel(R) Celeron(R) CPU 560 @ 2.13GHz 2127
700 Intel(R) Core(TM)2 Duo CPU E5750 @ 2.00GHz2000
In the above example
when I run stored procedure results should be following
TableB machine_id 878 needs to be inserted into TableA
TableC machine_id 700 should not update info in TableA
hopefully this explains a bit better what I'm trying to do.
July 12, 2011 at 9:12 am
Would this work?
-- EXISTS IN TABLE B
UPDATE A
SET A.model = B.model,
A.speed = B.speed
FROM TableA A
JOIN TableB B ON A.machine_id = TableB.machine_id
-- EXISTS IN TABLE C
UPDATE A
SET A.model = C.model,
A.speed = C.speed
FROM TableA A
JOIN TableC C ONA.machine_id = TableC.machine_id
-- IN ORDER TO PREVENT ROWS UPDATING WHEN TABLE B HAD THOSE ROWS
AND NOT EXISTS (SELECT 1 FROM TableB B WHERE C.machine_ID = b.machine_ID)
-- INSERT ROWS WHICH ARE IN TABLE B AND NOT TABLE A
INSERT INTO TableA (machine_id, model, speed)
SELECT B.machine_id, B.model, B.speed
FROM TableB B
WHERE NOT EXISTS (SELECT 1 FROM TableA A WHERE A.machine_ID = B.machine_ID)
-- INSERT ROWS WHICH ARE IN TABLE C AND NOT IN TABLE B OR TABLE A
INSERT INTO TableA (machine_id, model, speed)
SELECT C.machine_id, C.model, C.speed
FROM TableC C
WHERE NOT EXISTS (SELECT 1 FROM TableA A WHERE A.machine_ID = C.machine_ID)
.
July 12, 2011 at 9:23 am
You should consider using the MERGE statement. The syntax is complicated, but it removes the danger of cardinality errors that can occur (unreported) with the UPDATE...FROM construction. These happen when the row you are updating has more than one corresponding row in the table you are updating from.
John
July 12, 2011 at 9:29 am
Hah - I completely forgot I was looking at the SQL Server 2008 forums. Of course MERGE is much better for UPSERT procedures 😛 my bad! Don't have enough experience with it though, so I'll leave that to someone else to help with.
July 13, 2011 at 1:02 am
MERGE is what you want in SQL Server 2008. This piece of code is untested, in fact, I've never used the COALESCE command myself (being a self-taught SQL-novice).
This piece of code below will look at each row in the table_b/table_c. If a machine_id exists in table_b it will use that machine_id, model and speed - if it does not exist in table_b (is null) then take the data from table_c. If a machine_id happens to exist in both table_b and table_c and the model and/or speed is NULL in b, then it should take that model/speed that is null in table_b from the data in table_c. (in the USING command).
Then it will merge the data, that is, update TABLE_A model and speed from the data caught in the USING command above if the machine_id exists in table_a. If the machine_id does not exist, it will insert a new record.
Hope it works.
MERGE INTO TABLE_A AS TARGET
USING (
SELECT
COALESCE(B.MACHINE_ID,C.MACHINE_ID) AS "MACHINE_ID"
,COALESCE(B.MODEL,C.MODEL) AS "MODEL"
,COALESCE(B.SPEED,C.SPEED) AS "SPEED"
FROM TABLE_B B
FULL OUTER JOIN TABLE_C C
ON B.MACHINE_ID=C.MACHINE_ID
) AS SOURCE
ON TARGET.MACHINE_ID=SOURCE.MACHINE_ID
/* WHEN MATCHED THEN UPDATE SET
MODEL=SOURCE.MODEL
,SPEED=SOURCE.SPEED
-- This piece of code would update the data in TABLE_A in case the machine_id already existed - taken out due to clarification above --
*/
WHEN NOT MATCHED THEN
INSERT (
MACHINE_ID
,MODEL
,SPEED
)
VALUES (
SOURCE.MACHINE_ID
,SOURCE.MODEL
,SOURCE.SPEED
)
;
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply