merge table with update ms sql

Question

I need help with one update- insert with merge in sql Here is the original Insert

INSERT INTO [WarehouseMgmt].[JobSteps] ([JobId],[StepName],[StepNo],[ExecName]) VALUES (N'HOURLY_JOB', N'SyncReportServerReports', 4210, N'WarehouseMgmt.SyncReportServerReports'), (N'MANUAL_JOB', N'SyncReportServerReports', 4210, N'WarehouseMgmt.SyncReportServerReports'), (N'HOURLY_JOB', N'SyncReportServerUserEntries', 4220,N'WarehouseMgmt.SyncReportServerUserEntries'), (N'MANUAL_JOB', N'SyncReportServerUserEntries', 4220, N'WarehouseMgmt.SyncReportServerUserEntries'), (N'HOURLY_JOB', N'SyncReportServerUsers', 4230,N'WarehouseMgmt.SyncReportServerUsers'), (N'MANUAL_JOB', N'SyncReportServerUsers', 4230, N'WarehouseMgmt.SyncReportServerUsers'), (N'HOURLY_JOB', N'StageFactReportServerExecutionLog', 4240, N'WarehouseStaging.StageFactReportServerExecutionLog'), (N'MANUAL_JOB', N'StageFactReportServerExecutionLog', 4240, N'WarehouseStaging.StageFactReportServerExecutionLog'), (N'HOURLY_JOB', N'SyncFactReportServerExecutionLog', 4250, N'WarehouseMgmt.SyncFactReportServerExecutionLog'), (N'MANUAL_JOB', N'SyncFactReportServerExecutionLog', 4250, N'WarehouseMgmt.SyncFactReportServerExecutionLog') INSERT WarehouseMgmt.SyncJobSteps ( JobStepId, StepType, SyncDataSQLId ) SELECT JobStepId = JS.Id, StepType = 'SQL', SyncDataSQLId = SSD.Id FROM WarehouseMgmt.JobSteps JS JOIN ( VALUES ('SyncReportServerReports', 'WarehouseMgmt.DimReportServerReports'), ('SyncReportServerUserEntries', 'WarehouseMgmt.DimReportServerUserEntries'), ('SyncReportServerUsers', 'WarehouseMgmt.DimReportServerUsers'), ('StageFactReportServerExecutionLog', 'WarehouseStaging.FactReportServerExecutionLog'), ('SyncFactReportServerExecutionLog', 'WarehouseMgmt.FactReportServerExecutionLog') ) V([SyncJobStep], [SQLDataObjectName]) ON JS.StepName=V.[SyncJobStep] JOIN WarehouseMgmt.SyncSQLData SSD ON V.[SQLDataObjectName]=SSD.ObjectName

and i want to have this i my table

INSERT INTO [WarehouseMgmt].[JobSteps] ([JobId],[StepName],[StepNo],[ExecName]) VALUES (N'HOURLY_JOB', N'SyncReportServerReports', 4210, N'WarehouseMgmt.SyncReportServerReports'), (N'MANUAL_JOB', N'SyncReportServerReports', 4210, N'WarehouseMgmt.SyncReportServerReports'), (N'HOURLY_JOB', N'SyncReportServerUserEntries', 4220,N'WarehouseMgmt.SyncReportServerUserEntries'), (N'MANUAL_JOB', N'SyncReportServerUserEntries', 4220, N'WarehouseMgmt.SyncReportServerUserEntries'), (N'HOURLY_JOB', N'SyncReportServerUsers', 4230,N'WarehouseMgmt.SyncReportServerUsers'), (N'MANUAL_JOB', N'SyncReportServerUsers', 4230, N'WarehouseMgmt.SyncReportServerUsers'), (N'HOURLY_JOB', N'SyncReportServerFormatEntries', 4240,N'WarehouseMgmt.SyncReportServerFormatEntries'), (N'MANUAL_JOB', N'SyncReportServerFormatEntries', 4240, N'WarehouseMgmt.SyncReportServerFormatEntries'), (N'HOURLY_JOB', N'SyncReportServerReportFormat', 4250,N'WarehouseMgmt.SyncReportServerReportFormat'), (N'MANUAL_JOB', N'SyncReportServerReportFormat', 4250, N'WarehouseMgmt.SyncReportServerReportFormat'), (N'HOURLY_JOB', N'StageFactReportServerExecutionLog', 4260, N'WarehouseStaging.StageFactReportServerExecutionLog'), (N'MANUAL_JOB', N'StageFactReportServerExecutionLog', 4260, N'WarehouseStaging.StageFactReportServerExecutionLog'), (N'HOURLY_JOB', N'SyncFactReportServerExecutionLog', 4270, N'WarehouseMgmt.SyncFactReportServerExecutionLog'), (N'MANUAL_JOB', N'SyncFactReportServerExecutionLog', 4270, N'WarehouseMgmt.SyncFactReportServerExecutionLog') INSERT WarehouseMgmt.SyncJobSteps ( JobStepId, StepType, SyncDataSQLId ) SELECT JobStepId = JS.Id, StepType = 'SQL', SyncDataSQLId = SSD.Id FROM WarehouseMgmt.JobSteps JS JOIN ( VALUES ('SyncReportServerReports', 'WarehouseMgmt.DimReportServerReports'), ('SyncReportServerUserEntries', 'WarehouseMgmt.DimReportServerUserEntries'), ('SyncReportServerUsers', 'WarehouseMgmt.DimReportServerUsers'), ('SyncReportServerFormatEntries', 'WarehouseMgmt.DimReportServerFormatEntries'), ('SyncReportServerReportFormat', 'WarehouseMgmt.DimReportServerReportFormat'), ('StageFactReportServerExecutionLog', 'WarehouseStaging.FactReportServerExecutionLog'), ('SyncFactReportServerExecutionLog', 'WarehouseMgmt.FactReportServerExecutionLog') ) V([SyncJobStep], [SQLDataObjectName]) ON JS.StepName=V.[SyncJobStep] JOIN WarehouseMgmt.SyncSQLData SSD ON V.[SQLDataObjectName]=SSD.ObjectName

I cannot use only INSERT,because there are already values in database and i don't want to delete them.How can i update 4 values in my first Insert and and 4 new. Look carefully first insert and second

I want something like this

MERGE [WarehouseMgmt].[JobSteps] JS USING #JobSteps TJS ON(TJS.[StepName] = JS.[StepName]) WHEN NOT MATCHED BY TARGET THEN INSERT ([JobId],[StepName],[StepNo],[ExecName]) VALUES (N'HOURLY_JOB', N'SyncReportServerFormatEntries', 4240,N'WarehouseMgmt.SyncReportServerFormatEntries'), (N'MANUAL_JOB', N'SyncReportServerFormatEntries', 4240, N'WarehouseMgmt.SyncReportServerFormatEntries'), (N'HOURLY_JOB', N'SyncReportServerReportFormat', 4250,N'WarehouseMgmt.SyncReportServerReportFormat'), (N'MANUAL_JOB', N'SyncReportServerReportFormat', 4250, N'WarehouseMgmt.SyncReportServerReportFormat') WHEN MATCHED THEN UPDATE JS.[StepNo] = TJS.[StepNo] ;

Why this is wrong ?

Create a temporary table and insert those "new" values in there. Then you can join it just like any other table and complete the insert. — chris544
– chris544, Commented Jan 6, 2015 at 12:06
i cannot use temp table, i need merge statement that will update my rows and insert new rows — John
– John, Commented Jan 6, 2015 at 12:10
It is possible to create a table variable with DECLARE syntax. It will be gone in the next batch. Are there really specific restrictions that permit creation of this kind of variable? — chris544
– chris544, Commented Jan 6, 2015 at 12:12

GarethD · Accepted Answer · 2015-01-06 12:28:05Z

You can use a table valued constructor in your merge statement as the source table in almost the same way you do in a select, so instead of:

SELECT Column1, Column2 FROM (VALUES (1, 2), (3, 4)) AS v (Column1, Column2)

You can use:

MERGE [TableName] AS t USING (VALUES (1, 2), (3, 4)) AS v (Column1, Column2) ON t.Column1 = v.Column1 WHEN MATCHED etc...

So for your first example you would end up with:

MERGE [WarehouseMgmt].[JobSteps] AS js USING ( VALUES (N'HOURLY_JOB', N'SyncReportServerReports', 4210, N'WarehouseMgmt.SyncReportServerReports'), (N'MANUAL_JOB', N'SyncReportServerReports', 4210, N'WarehouseMgmt.SyncReportServerReports'), (N'HOURLY_JOB', N'SyncReportServerUserEntries', 4220,N'WarehouseMgmt.SyncReportServerUserEntries'), (N'MANUAL_JOB', N'SyncReportServerUserEntries', 4220, N'WarehouseMgmt.SyncReportServerUserEntries'), (N'HOURLY_JOB', N'SyncReportServerUsers', 4230,N'WarehouseMgmt.SyncReportServerUsers'), (N'MANUAL_JOB', N'SyncReportServerUsers', 4230, N'WarehouseMgmt.SyncReportServerUsers'), (N'HOURLY_JOB', N'SyncReportServerFormatEntries', 4240,N'WarehouseMgmt.SyncReportServerFormatEntries'), (N'MANUAL_JOB', N'SyncReportServerFormatEntries', 4240, N'WarehouseMgmt.SyncReportServerFormatEntries'), (N'HOURLY_JOB', N'SyncReportServerReportFormat', 4250,N'WarehouseMgmt.SyncReportServerReportFormat'), (N'MANUAL_JOB', N'SyncReportServerReportFormat', 4250, N'WarehouseMgmt.SyncReportServerReportFormat'), (N'HOURLY_JOB', N'StageFactReportServerExecutionLog', 4260, N'WarehouseStaging.StageFactReportServerExecutionLog'), (N'MANUAL_JOB', N'StageFactReportServerExecutionLog', 4260, N'WarehouseStaging.StageFactReportServerExecutionLog'), (N'HOURLY_JOB', N'SyncFactReportServerExecutionLog', 4270, N'WarehouseMgmt.SyncFactReportServerExecutionLog'), (N'MANUAL_JOB', N'SyncFactReportServerExecutionLog', 4270, N'WarehouseMgmt.SyncFactReportServerExecutionLog') ) AS v ([JobId],[StepName],[StepNo],[ExecName]) ON v.JobID = js.JobID AND v.StepName = js.StepName WHEN MATCHED THEN UPDATE SET StepNo = v.StepNo, ExecName = v.ExecName WHEN NOT MATCHED BY TARGET THEN INSERT (JobId, StepName, StepNo, ExecName) VALUES (v.JobId, v.StepName, v.StepNo, v.ExecName);

Then for the second statement, I would encapsulate the new data with a common table expression for clarity, but the principle is the same (although I am not sure example what columns you want to match on or update):

WITH NewSyncJobStep AS ( SELECT JobStepId = JS.Id, StepType = 'SQL', SyncDataSQLId = SSD.Id FROM WarehouseMgmt.JobSteps JS JOIN ( VALUES ('SyncReportServerReports', 'WarehouseMgmt.DimReportServerReports'), ('SyncReportServerUserEntries', 'WarehouseMgmt.DimReportServerUserEntries'), ('SyncReportServerUsers', 'WarehouseMgmt.DimReportServerUsers'), ('SyncReportServerFormatEntries', 'WarehouseMgmt.DimReportServerFormatEntries'), ('SyncReportServerReportFormat', 'WarehouseMgmt.DimReportServerReportFormat'), ('StageFactReportServerExecutionLog', 'WarehouseStaging.FactReportServerExecutionLog'), ('SyncFactReportServerExecutionLog', 'WarehouseMgmt.FactReportServerExecutionLog') ) V([SyncJobStep], [SQLDataObjectName]) ON JS.StepName=V.[SyncJobStep] JOIN WarehouseMgmt.SyncSQLData SSD ON V.[SQLDataObjectName]=SSD.ObjectName ) MERGE WarehouseMgmt.SyncJobSteps AS sjs USING NewSyncJobStep AS n ON n.JobStepId = sjs.JobStepId WHEN MATCHED THEN UPDATE SET SyncDataSQLId = n.SyncDataSQLId WHEN NOT MATCHED BY TARGET THEN INSERT (JobStepId, StepType, SyncDataSQLId) VALUES (n.JobStepId, n.StepType, n.SyncDataSQLId);

Collectives™ on Stack Overflow

merge table with update ms sql

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related