August 7, 2007 at 2:38 am
I have two tables with identical structure such as
ID, product1, product2, product3, product4, product5
Table 1 is filled with 5 ID numbers, say 1,2,3,4,5 and NULL (ie nothing) for the products.
Table 2 is filled with 10 ID numbers, 1,2,3,4,5,6,7,8,9,10 and all product details filled in (doesnt matter what with).
I have to update Table 1 such that it basically becomes a copy of table 2 with all the details.
The question basically here is can this be done with a single query or must you use an UPDATE query to first update the details of the ID numbers 1-5, and then an INSERT query to get the details of ID's 6-10 into table one?
I have tried the UPDATE query, and managed to get it to update with ID's 1-5 with the product details, but did not manage to get it to insert ID's 6-10 into table 1.
Quick help is appreciated.
August 7, 2007 at 4:38 am
Thomas
You could do it in one statement using dynamic SQL, but I think it's more elegant to do it like I've done below. Why do you prefer the single query approach?
John
--Tables
CREATE TABLE #table1 (
ID INT
,prod1 VARCHAR(20)
,prod2 VARCHAR(20)
,prod3 VARCHAR(20)
,prod4 VARCHAR(20)
,prod5 VARCHAR(20)
)
CREATE TABLE #table2 (
ID INT
,prod1 VARCHAR(20)
,prod2 VARCHAR(20)
,prod3 VARCHAR(20)
,prod4 VARCHAR(20)
,prod5 VARCHAR(20)
)
--Data
INSERT INTO #table1 (ID)
SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5
INSERT
INTO #table2 (ID, prod1, prod2, prod3, prod4, prod5)
SELECT 1, 'apple', 'orange', 'pear', 'banana', 'peach' UNION
SELECT 2, 'car', 'train', 'plane', 'bus', 'bicycle' UNION
SELECT 3, 'pea', 'bean', 'potato', 'carrot', 'swede 'UNION
SELECT 4, 'black', 'white', 'red', 'yellow', 'blue' UNION
SELECT 5, 'pound', 'dollar', 'euro', 'yen', 'franc' UNION
SELECT 6, 'bedroom', 'bathroom', 'kitchen', 'hall', 'garden' UNION
SELECT 7, 'rose', 'violet', 'pansy', 'sunflower', 'lily' UNION
SELECT 8, 'knee', 'elbow', 'shin', 'skull', 'rib' UNION
SELECT 9, 'hill', 'mountain', 'river', 'valley', 'lake' UNION
SELECT 10, 'football', 'rugby', 'tennis', 'hockey', 'badminton'
--Do the work
UPDATE
t1
SET
prod1 = t2.prod1
,prod2 = t2.prod2
,prod3 = t2.prod3
,prod4 = t2.prod4
,prod5 = t2.prod5
FROM
#table1 t1
JOIN
#table2 t2 ON t1.ID = t2.ID
INSERT INTO #table1 (ID, prod1, prod2, prod3, prod4, prod5)
SELECT
t2.ID
,t2.prod1
,t2.prod2
,t2.prod3
,t2.prod4
,t2.prod5
FROM
#table2 t2
LEFT JOIN
#table1 t1 ON t2.ID = t1.ID
WHERE
t1.ID IS NULL
--Check the work
SELECT * FROM #table1
SELECT * FROM #table2
August 7, 2007 at 6:19 am
AS per my understanding you want table1 exact copy of table 2.
Why dnt you use this simple approach.
Delete from table1 ---------------if there is not integrity constarint voilation
--since structure of both tables are same
Select * into table1 from table2
so all you need to run two satemenst...one is delete and second is select.
Hope this would work for you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply