Hi guys! I've tried insert data to sql server table. If the primary keys are not duplicated, I want it to just insert that row to the table. If the primary keys are duplicated, I want it just keep the first row and skip the latter.
the data; table named test_utf8_output
valuedate and TDR_Flag are the primary keys
What I expect
My code:
/* create new table in sql server) */
PROC SQL;
connect using mylib;
execute(
CREATE TABLE PK_TEST (
string nvarchar(255)
, ValueDate nvarchar(255)
, TDR_Flag tinyint
, CONSTRAINT PK_col PRIMARY KEY(ValueDate,TDR_Flag)
)
) by mylib;
quit;
/* insert data*/
proc sql;INSERT INTO mylib.pk_test (string, ValueDate, TDR_Flag)SELECT string, V_Date,TDR_Flag FROM test_utf8_output;quit;
error I got:
ERROR: CLI execute error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Violation of PRIMARY KEY constraint
'PK_col'. Cannot insert duplicate key in object 'dbo.PK_TEST'. The duplicate key value is (20231030, 0). : [SAS][ODBC SQL
Server Wire Protocol driver][Microsoft SQL Server]The statement has been terminated.
ERROR: ROLLBACK issued due to errors for data set MYLIB.pk_test.DATA.
I've tried WITH (IGNORE_DUP_KEY = ON) :
PROC SQL;
connect using mylib;
execute(
CREATE TABLE PK_TEST (
string nvarchar(255)
, ValueDate nvarchar(255)
, TDR_Flag tinyint
, CONSTRAINT PK_col PRIMARY KEY(ValueDate,TDR_Flag)
WITH (IGNORE_DUP_KEY = ON))
) by mylib;
quit;
/* insert data*/
proc sql;
INSERT INTO mylib.pk_test (string, ValueDate, TDR_Flag)
SELECT string, V_Date,TDR_Flag FROM test_utf8_output;
quit;
still error
ERROR: CLI execute error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Duplicate key was ignored.
ERROR: ROLLBACK issued due to errors for data set MYLIB.pk_test.DATA.
... View more
