July 12, 2017 at 4:02 pm
I am working on a stored procedure that is supposed to insert or update rows in a remote table. I tried to use MERGE, but it is not possible to use MERGE when the target table is a remote.
So far all I have is the INSERT statement:
ALTER PROCEDURE [dbo].[mn_Upsert_RecordInfo]
AS
BEGIN
INSERT INTO [devbox].[test].[dbo].[RecordInfo]
(
[ProductID]
, [FlagValue]
)
SELECT DISTINCT
ProductID
, FlagValue
FROM
[dbo].[Product]
WHERE
[Category] = 2;
END;
Can someone show me how can I also do the update within the sproc? Do I need to first read the records into a temp table and then compare the values? The column value I am interested in updating is FlagValue.
This would be my UPDATE statement:
UPDATE
[ri]
SET
[ri].[FlagValue] = [p].[FlagValue]
FROM
[RecordInfo] [ri]
INNER JOIN [dbo].[Product] [p]
ON [ri].[SKU] = [p].[ProductID]
WHERE
[ri].[SKU] = [p].[ProductID]
AND [p].[ReportCategory] = 2;
Thank you for your recommendations.
July 12, 2017 at 8:16 pm
If you are just doing one row operations just pass in the remote row values as parameters for the sproc. Then you can use MERGE if you wish. NOTE: MERGE has all sorts of issues and bugs.
Otherwise I think the best solution is probably to read the data into a local temp table and then use that for the UPSERT.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 13, 2017 at 11:29 am
TheSQLGuru - Wednesday, July 12, 2017 8:16 PMIf you are just doing one row operations just pass in the remote row values as parameters for the sproc. Then you can use MERGE if you wish. NOTE: MERGE has all sorts of issues and bugs.
Otherwise I think the best solution is probably to read the data into a local temp table and then use that for the UPSERT.
Hi Kevin, do you have an example script of a stored procedure that uses a temp table to to UPSERT operation? Thank you.
July 17, 2017 at 3:51 pm
A stored procedure can have more than one statement in it. If you want to break it down into two simple actions, you can.-- Update existing rows:
UPDATE DESTINATION
SET DESTINATION.Column = Source.Column, etc.
INNER JOIN SOURCE ON DESTINATION.PK = SOURCE.PK
-- Insert new rows:
INSERT INTO DESTINATION (Columns...)
SELECT (Columns...)
FROM SOURCE
WHERE NOT EXISTS
(SELECT NULL FROM DESTINATION WHERE SOURCE.PK = DESTINATION.PK)
July 17, 2017 at 4:37 pm
autoexcrement - Monday, July 17, 2017 3:51 PMA stored procedure can have more than one statement in it. If you want to break it down into two simple actions, you can.-- Update existing rows:
UPDATE DESTINATION
SET DESTINATION.Column = Source.Column, etc.
INNER JOIN SOURCE ON DESTINATION.PK = SOURCE.PK-- Insert new rows:
INSERT INTO DESTINATION (Columns...)
SELECT (Columns...)
FROM SOURCE
WHERE NOT EXISTS
(SELECT NULL FROM DESTINATION WHERE SOURCE.PK = DESTINATION.PK)
If this is an attempt to sync data from one server to another, then I think this is the right direction to move in. I like to use an EXCEPT operator to first isolate the rows that need to be inserted. Example:
WITH cteNewIDs AS (
SELECT ID
FROM SourceDB
EXCEPT
SELECT ID
FROM NewDB
)
INSERT INTO NewDB.dbo.TableA(Column1, Column2, ...)
SELECT Column1, Column2, ...
FROM SourceDB.dbo.TableA
WHERE ID IN (SELECT ID FROM cteNewIDs);
July 17, 2017 at 5:40 pm
Can I ask why you prefer EXCEPT over NOT EXISTS?
July 17, 2017 at 5:45 pm
autoexcrement - Monday, July 17, 2017 5:40 PMCan I ask why you prefer EXCEPT over NOT EXISTS?
More often than not, I find that using EXCEPT to isolate the new rows in the source is faster. I do consider data types, but I'm almost always querying against integer keys. I test it every time, but 90%+ of the time, EXCEPT wins, sometimes by a wide margin. I also know there are exceptions to everything.
July 17, 2017 at 5:49 pm
I woulda thought a straight INNER JOIN would be the easiest for the engine to optimize for. But good food for thought.
July 18, 2017 at 8:20 am
autoexcrement - Monday, July 17, 2017 3:51 PMA stored procedure can have more than one statement in it. If you want to break it down into two simple actions, you can.-- Update existing rows:
UPDATE DESTINATION
SET DESTINATION.Column = Source.Column, etc.
INNER JOIN SOURCE ON DESTINATION.PK = SOURCE.PK-- Insert new rows:
INSERT INTO DESTINATION (Columns...)
SELECT (Columns...)
FROM SOURCE
WHERE NOT EXISTS
(SELECT NULL FROM DESTINATION WHERE SOURCE.PK = DESTINATION.PK)
Hello Ten Centuries, I completed the code of the stored procedure using a MERGE. I was wondering if you or any of the other guys that have posted their help on to this question could give me feedback and let me know if the code looks correct. I had to modify some of the column names to be able to post the code, I mention this in case you see something among those type of errors.
Here is my completed stored procedure (so far):
ALTER PROCEDURE [dbo].[ArrangementInfo_Upsert]
AS
BEGIN
--Synchronize the target table with refreshed data from source table
MERGE [dbo].[ArrangementInfo] AS [t]
USING
( SELECT
[a].[SKU]
, [a].[Vendor]
, [a].[Arrangement]
FROM
( SELECT TOP ( 100 ) PERCENT
[p].[SKU]
, [v].[Vendor]
, MIN([af].[PublicationDate]) AS [FirstPub]
, [p].[Arrangement]
FROM [dbo].[Vendors] [v]
RIGHT OUTER JOIN [dbo].[Production] [p]
ON [v].[ID] = [p].[vendor_id]
INNER JOIN [dev].[db].[dbo].[ProductionStatus] [ps]
ON [p].[StatusID] = [ps].[StatusID]
INNER JOIN [dbo].[ProductMetaData] [pmd]
ON [p].[SKU] = [pmd].[pf_id]
INNER JOIN [dbo].[Arrangement] [a]
ON [p].[SKU] = [a].[ArrangementID]
INNER JOIN [dbo].[Arrangement_File] [af]
ON [a].[ArrangementID] = [af].[ArrangementID]
WHERE
( [p].[Arrangement] = 1 )
OR (
[p].[Text] LIKE '%FindIt%'
OR [v].[ID] = 34 )
GROUP BY
[v].[Vendor]
, [p].[SKU]
, [p].[Arrangement]
ORDER BY
MIN([af].[PublicationDate]) DESC
, ( CASE WHEN [p].[vendor_id] <> 34 THEN 0
ELSE 1
END )
, [p].[Arrangement]
) [a]
)
ON ( [t].[SKU] = .[SKU] )
--When records are matched by sku, update the records if there is any change in the persistence value (0 now is 1)
WHEN MATCHED AND [t].[Persistence] <> .[Arrangement]
AND [t].[SKU] = .[SKU] THEN
UPDATE SET
[t].[Persistence] = .[Arrangement]
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
[SKU]
, [Submitter]
, [MNArrangementPersistence] )
VALUES
(
.[SKU]
, .[Submitter]
, .[MNArrangement]
)
--When there is a row that exists in target table and same record does not exist in source table then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT
$action
, DELETED.*
, INSERTED.*;
SELECT @@ROWCOUNT;
END;
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply