March 11, 2011 at 5:35 am
Hi there all,
I have a table stucture as:
location, month, metric1, metric2,..metric100
I want to update the rows in this table with newer data, which is in the same format as above. The combination of month and location becomes the key.
1) So we can insert rows from the newer table if there is no key match.
2) Where there is a match on this key, we need to replace the metric values.
Can anyone suggest a neat way of doing this please ? I have 109 metrics, so want to avoid a SET statement for each one...
Regards, Greg.
March 11, 2011 at 6:35 am
Dynamic sql is your friend:
DECLARE @sql nvarchar(max)
SET @sql = STUFF((
SELECT ',DEST.' + name + ' = SRC.' + name AS [text()]
FROM (
SELECT cols.name
FROM sys.columns AS cols
INNER JOIN sys.tables AS tabs
ON cols.object_id = tabs.object_id
WHERE tabs.name = 'metrics'
AND cols.name NOT IN ('location', 'month')
) AS metrics
FOR XML PATH('')
), 1, 1, SPACE(0))
SELECT @sql = '
UPDATE DEST
SET ' + @sql + '
FROM tempdb..metrics AS DEST
INNER JOIN tempdb..new_metrics AS SRC
ON DEST.location = SRC.location
AND DEST.month = SRC.month'
PRINT(@sql)
EXEC(@sql)
SELECT @sql = '
INSERT INTO tempdb..metrics
SELECT *
FROM tempdb..new_metrics AS SRC
WHERE NOT EXISTS (
SELECT 1
FROM tempdb..metrics AS DEST
WHERE DEST.location = SRC.location
AND DEST.month = SRC.month
)'
PRINT(@sql)
EXEC(@sql)
DECLARE @sql nvarchar(max)
SET @sql = STUFF((
SELECT ',DEST.' + name + ' = SRC.' + name AS [text()]
FROM (
SELECT cols.name
FROM sys.columns AS cols
INNER JOIN sys.tables AS tabs
ON cols.object_id = tabs.object_id
WHERE tabs.name = 'metrics'
AND cols.name NOT IN ('location', 'month')
) AS metrics
FOR XML PATH('')
), 1, 1, SPACE(0))
SELECT @sql = '
UPDATE DEST
SET ' + @sql + '
FROM tempdb..metrics AS DEST
INNER JOIN tempdb..new_metrics AS SRC
ON DEST.location = SRC.location
AND DEST.month = SRC.month'
PRINT(@sql)
EXEC(@sql)
SELECT @sql = '
INSERT INTO tempdb..metrics
SELECT *
FROM tempdb..new_metrics AS SRC
WHERE NOT EXISTS (
SELECT 1
FROM tempdb..metrics AS DEST
WHERE DEST.location = SRC.location
AND DEST.month = SRC.month
)'
PRINT(@sql)
EXEC(@sql)
Hope this helps,
Gianluca
-- Gianluca Sartori
March 11, 2011 at 7:10 am
greg.bull (3/11/2011)
Hi there all,I have a table stucture as:
location, month, metric1, metric2,..metric100
I want to update the rows in this table with newer data, which is in the same format as above. The combination of month and location becomes the key.
1) So we can insert rows from the newer table if there is no key match.
2) Where there is a match on this key, we need to replace the metric values.
Can anyone suggest a neat way of doing this please ? I have 109 metrics, so want to avoid a SET statement for each one...
Regards, Greg.
Assuming the schema is stable : SSMS, your db, your tables, script insert / update / select
G's trick could also be usefull to create the script once and then move to static sql.
March 11, 2011 at 7:38 am
How is the data coming in? Is this loaded through SSIS/bcp or is it in another table?
I would use Greg's statement to build the SQL I needed and then use that in a stored procedure somewhere.
March 11, 2011 at 7:41 am
That is a brilliant answer. Mnay thanks as I suspect that's saved me a few days of fumbling around...
Thanks, Greg.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply