November 17, 2016 at 9:06 pm
MERGE M_SUPPLIER A --- TARGET
USING TEMP_SUPPLIER B --- SOURCE DUMMY
ON A.SUPPLIER_CODE =B.SUPPLIER_CODE
WHEN MATCHED AND A.SUPPLIER_CODE <> B.SUPPLIER_CODE OR
A.SUPPLIER_NAME <> B.SUPPLIER_NAME THEN
UPDATE
SET A.SUPPLIER_CODE = B.SUPPLIER_CODE,
A.SUPPLIER_NAME = B.SUPPLIER_NAME,
WHEN NOT MATCHED BY TARGET THEN
INSERT (SUPPLIER_CODE,
SUPPLIER_NAME)
VALUES (B.SUPPLIER_CODE,
B.SUPPLIER_NAME);
Dear all,
when I execute the syntax, it result :
Violation of PRIMARY KEY constraint 'PK_M_SUPPLIER'. Cannot insert duplicate key in object 'dbo.M_SUPPLIER'. The duplicate key value is (0001).
Please advice what can I do to insert the data to Target ?
November 17, 2016 at 10:37 pm
MERGE M_SUPPLIER A --- TARGET
USING TEMP_SUPPLIER B --- SOURCE DUMMY
ON A.SUPPLIER_CODE = B.SUPPLIER_CODE
-- You are matching on A.SUPPLIER_CODE = B.SUPPLIER_CODE
-- Then you say WHEN A.SUPPLIER_CODE <> B.SUPPLIER_CODE
WHEN MATCHED --AND A.SUPPLIER_CODE <> B.SUPPLIER_CODE OR
-- A.SUPPLIER_NAME <> B.SUPPLIER_NAME THEN
UPDATE
-- Why are you trying to update A.SUPPLIER_CODE, which is they key used to join on
SET A.SUPPLIER_CODE = B.SUPPLIER_CODE,
A.SUPPLIER_NAME = B.SUPPLIER_NAME,
WHEN NOT MATCHED BY TARGET THEN
INSERT (SUPPLIER_CODE,
SUPPLIER_NAME)
VALUES (B.SUPPLIER_CODE,
B.SUPPLIER_NAME);
November 17, 2016 at 11:12 pm
DesNorton (11/17/2016)
MERGE M_SUPPLIER A --- TARGETUSING TEMP_SUPPLIER B --- SOURCE DUMMY
ON A.SUPPLIER_CODE = B.SUPPLIER_CODE
-- You are matching on A.SUPPLIER_CODE = B.SUPPLIER_CODE
-- Then you say WHEN A.SUPPLIER_CODE <> B.SUPPLIER_CODE
WHEN MATCHED --AND A.SUPPLIER_CODE <> B.SUPPLIER_CODE OR
-- A.SUPPLIER_NAME <> B.SUPPLIER_NAME THEN
UPDATE
-- Why are you trying to update A.SUPPLIER_CODE, which is they key used to join on
SET A.SUPPLIER_CODE = B.SUPPLIER_CODE,
A.SUPPLIER_NAME = B.SUPPLIER_NAME,
WHEN NOT MATCHED BY TARGET THEN
INSERT (SUPPLIER_CODE,
SUPPLIER_NAME)
VALUES (B.SUPPLIER_CODE,
B.SUPPLIER_NAME);
1. I want to synchronize the target table with the refreshed data coming from the source table
2. Because the Target still empty and already locked with Primary Key on Supplier_Code
kindly help how to insert the data ?
November 17, 2016 at 11:33 pm
Please supply table structures for M_SUPPLIER and TEMP_SUPPLIER, as well as sample data that will consistantly cause the error that you are getting.
November 17, 2016 at 11:58 pm
DesNorton (11/17/2016)
Please supply table structures for M_SUPPLIER and TEMP_SUPPLIER, as well as sample data that will consistantly cause the error that you are getting.
--M_SUPPLIER
Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation
SUPPLIER_CODE(PK) varcharno6 nononoSQL_Latin1_General_CP1_CI_AS
SUPPLIER_NAMEvarcharno40 yesnoyesSQL_Latin1_General_CP1_CI_AS
PAYMENT_TERMvarcharno3 yesnoyesSQL_Latin1_General_CP1_CI_AS
CREATED_BYvarcharno20 nononoSQL_Latin1_General_CP1_CI_AS
CREATED_DTdatetimeno8 no(n/a)(n/a)NULL
CHANGED_BYvarcharno20 yesnoyesSQL_Latin1_General_CP1_CI_AS
CHANGED_DTdatetimeno8 yes(n/a)(n/a)NULL
SEND_TXT_FILEcharno1 yesnoyesSQL_Latin1_General_CP1_CI_AS
--TEMP_SUPPLIER
SUPPLIER_CODEvarcharno10 nononoSQL_Latin1_General_CP1_CI_AS
SUPPLIER_PLANT_CDcharno1 nononoSQL_Latin1_General_CP1_CI_AS
SUPPLIER_ABBREVIATIONvarcharno3 yesnoyesSQL_Latin1_General_CP1_CI_AS
SUPPLIER_NAMEvarcharno40 yesnoyesSQL_Latin1_General_CP1_CI_AS
JUNBIKI_FLAGbitno1 yes(n/a)(n/a)NULL
CREATED_BYvarcharno20 nononoSQL_Latin1_General_CP1_CI_AS
CREATED_DTdatetimeno8 no(n/a)(n/a)NULL
CHANGED_BYvarcharno20 yesnoyesSQL_Latin1_General_CP1_CI_AS
CHANGED_DTdatetimeno8 yes(n/a)(n/a)NULL
SEND_TXT_FILEcharno1 yesnoyesSQL_Latin1_General_CP1_CI_AS
November 18, 2016 at 1:06 am
OK. So it seems that your TEMP_SUPPLIER has duplicate values for SUPPLIER_CODE.
To prove this, let's create a simple data set that will cause the PK error
CREATE TABLE #M_SUPPLIER (
SUPPLIER_CODE VARCHAR(6) NOT NULL PRIMARY KEY CLUSTERED
, SUPPLIER_NAME VARCHAR(40) NULL
);
CREATE TABLE #TEMP_SUPPLIER (
SUPPLIER_CODE VARCHAR(10) NOT NULL
, SUPPLIER_NAME VARCHAR(40) NULL
);
INSERT INTO #TEMP_SUPPLIER ( SUPPLIER_CODE, SUPPLIER_NAME )
VALUES ( '0001', 'SUPPLIER_NAME 1' )
, ( '0001', 'SUPPLIER_NAME 2' )
MERGE #M_SUPPLIER A --- TARGET
USING #TEMP_SUPPLIER B --- SOURCE DUMMY
ON A.SUPPLIER_CODE = B.SUPPLIER_CODE
WHEN MATCHED THEN
UPDATE SET SUPPLIER_NAME = B.SUPPLIER_NAME
WHEN NOT MATCHED BY TARGET THEN
INSERT ( SUPPLIER_CODE, SUPPLIER_NAME )
VALUES ( B.SUPPLIER_CODE, B.SUPPLIER_NAME );
SELECT * FROM #M_SUPPLIER;
DROP TABLE #TEMP_SUPPLIER;
DROP TABLE #M_SUPPLIER;
How do we fix this. By removing the duplicates, either before trying to do the merge,
or by using a query as our source, where we have logic to de-duplicate the data.
CREATE TABLE #M_SUPPLIER (
SUPPLIER_CODE VARCHAR(6) NOT NULL PRIMARY KEY CLUSTERED
, SUPPLIER_NAME VARCHAR(40) NULL
);
CREATE TABLE #TEMP_SUPPLIER (
SUPPLIER_CODE VARCHAR(10) NOT NULL
, SUPPLIER_NAME VARCHAR(40) NULL
);
INSERT INTO #TEMP_SUPPLIER ( SUPPLIER_CODE, SUPPLIER_NAME )
VALUES ( '0001', 'SUPPLIER_NAME 1' )
, ( '0001', 'SUPPLIER_NAME 2' )
MERGE #M_SUPPLIER A --- TARGET
USING (-- This is a very simplistic proof of concept.
--You will need to find a way to make your source data unique.
SELECT SUPPLIER_CODE, SUPPLIER_NAME
FROM #TEMP_SUPPLIER
WHERE SUPPLIER_NAME = 'SUPPLIER_NAME 1'
) B --- SOURCE DUMMY
ON A.SUPPLIER_CODE = B.SUPPLIER_CODE
WHEN MATCHED THEN
UPDATE SET SUPPLIER_NAME = B.SUPPLIER_NAME
WHEN NOT MATCHED BY TARGET THEN
INSERT ( SUPPLIER_CODE, SUPPLIER_NAME )
VALUES ( B.SUPPLIER_CODE, B.SUPPLIER_NAME );
SELECT * FROM #M_SUPPLIER;
DROP TABLE #TEMP_SUPPLIER;
DROP TABLE #M_SUPPLIER;
PLEASE NOTE how I provide CREATE TABLE scripts for table definitions as well as INSERT scripts for sample data.
November 18, 2016 at 1:24 am
DesNorton (11/18/2016)
OK. So it seems that your TEMP_SUPPLIER has duplicate values for SUPPLIER_CODE.To prove this, let's create a simple data set that will cause the PK error
CREATE TABLE #M_SUPPLIER (
SUPPLIER_CODE VARCHAR(6) NOT NULL PRIMARY KEY CLUSTERED
, SUPPLIER_NAME VARCHAR(40) NULL
);
CREATE TABLE #TEMP_SUPPLIER (
SUPPLIER_CODE VARCHAR(10) NOT NULL
, SUPPLIER_NAME VARCHAR(40) NULL
);
INSERT INTO #TEMP_SUPPLIER ( SUPPLIER_CODE, SUPPLIER_NAME )
VALUES ( '0001', 'SUPPLIER_NAME 1' )
, ( '0001', 'SUPPLIER_NAME 2' )
MERGE #M_SUPPLIER A --- TARGET
USING #TEMP_SUPPLIER B --- SOURCE DUMMY
ON A.SUPPLIER_CODE = B.SUPPLIER_CODE
WHEN MATCHED THEN
UPDATE SET SUPPLIER_NAME = B.SUPPLIER_NAME
WHEN NOT MATCHED BY TARGET THEN
INSERT ( SUPPLIER_CODE, SUPPLIER_NAME )
VALUES ( B.SUPPLIER_CODE, B.SUPPLIER_NAME );
SELECT * FROM #M_SUPPLIER;
DROP TABLE #TEMP_SUPPLIER;
DROP TABLE #M_SUPPLIER;
How do we fix this. By removing the duplicates, either before trying to do the merge,
or by using a query as our source, where we have logic to de-duplicate the data.
CREATE TABLE #M_SUPPLIER (
SUPPLIER_CODE VARCHAR(6) NOT NULL PRIMARY KEY CLUSTERED
, SUPPLIER_NAME VARCHAR(40) NULL
);
CREATE TABLE #TEMP_SUPPLIER (
SUPPLIER_CODE VARCHAR(10) NOT NULL
, SUPPLIER_NAME VARCHAR(40) NULL
);
INSERT INTO #TEMP_SUPPLIER ( SUPPLIER_CODE, SUPPLIER_NAME )
VALUES ( '0001', 'SUPPLIER_NAME 1' )
, ( '0001', 'SUPPLIER_NAME 2' )
MERGE #M_SUPPLIER A --- TARGET
USING (-- This is a very simplistic proof of concept.
--You will need to find a way to make your source data unique.
SELECT SUPPLIER_CODE, SUPPLIER_NAME
FROM #TEMP_SUPPLIER
WHERE SUPPLIER_NAME = 'SUPPLIER_NAME 1'
) B --- SOURCE DUMMY
ON A.SUPPLIER_CODE = B.SUPPLIER_CODE
WHEN MATCHED THEN
UPDATE SET SUPPLIER_NAME = B.SUPPLIER_NAME
WHEN NOT MATCHED BY TARGET THEN
INSERT ( SUPPLIER_CODE, SUPPLIER_NAME )
VALUES ( B.SUPPLIER_CODE, B.SUPPLIER_NAME );
SELECT * FROM #M_SUPPLIER;
DROP TABLE #TEMP_SUPPLIER;
DROP TABLE #M_SUPPLIER;
PLEASE NOTE how I provide CREATE TABLE scripts for table definitions as well as INSERT scripts for sample data.
So, it will take only 1 data if there are 3 data at the Source ?
November 18, 2016 at 1:28 am
Deny Christian (11/18/2016)
So, it will take only 1 data if there are 3 data at the Source ?
The MERGE statement will take all of the data that you provide it.
However, you need to make sure that you only give it the data that will not break the rules of the destination table. That could be Primary Key, Unique Index, Check Constraint, etc
November 18, 2016 at 2:16 am
DesNorton (11/18/2016)
Deny Christian (11/18/2016)
So, it will take only 1 data if there are 3 data at the Source ?The MERGE statement will take all of the data that you provide it.
However, you need to make sure that you only give it the data that will not break the rules of the destination table. That could be Primary Key, Unique Index, Check Constraint, etc
noted then,,thank you for your help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply