Monday, 26 October 2009

Update/Insert data from one table to another

create TABLE #Load(
id int,
[name] VARCHAR(20)
)

create table #Data(
id int,
[name] VARCHAR(20)
)

SELECT * FROM #Data

INSERT INTO #Load
SELECT 1,'one'
UNION SELECT 2,'two'

UPDATE dbo.#Data SET
[name]=l.[name]
FROM #Load l JOIN #Data i ON l.id = i.id

INSERT INTO #Data
SELECT l.* FROM #Load l
LEFT JOIN #Data i ON i.id = l.id
WHERE i.id IS NULL

SELECT * FROM #Data

DELETE FROM #Load

INSERT INTO #Load
SELECT 2,'updated two'
UNION SELECT 3,'three'

UPDATE dbo.#Data SET
[name]=l.[name]
FROM #Load l JOIN #Data i ON l.id = i.id

INSERT INTO #Data
SELECT l.* FROM #Load l
LEFT JOIN #Data i ON i.id = l.id
WHERE i.id IS NULL

SELECT * FROM #Data

DROP TABLE #Load
DROP TABLE #Data

No comments:

Post a Comment