CREATE TABLE "MAIN"."TABLE_FOR_TEST2"
( "ONE" VARCHAR2(20 BYTE),
"TWO" VARCHAR2(20 BYTE),
CONSTRAINT "PRIMARY" PRIMARY KEY ("TWO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "U" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "U" ;
%macro test;
%macro dummy; %mend dummy;
data test(keep=ONE TWO);
length ONE $20 TWO $20;
ONE="wdq";
TWO="dadf";
run;
proc sql noprint;
create table test2(ONE char(20), TWO char(20), constraint prim primary key(TWO)); insert into test2 select * from test;
quit;
proc append base MAIN.TABLE_FOR_TEST2 data=test2 nowarn force ; quit;
Why do you need foreign keys? It would be better to upload your SAS data to a staging table in the database first which doesn't have constraints or indexes. Then load from the staging table using passthru SQL designed to handle constraints.
Your data has to meet the integrity constraints defined for that table. Consult your DBA people, they should be able to help you make your data compatible.
When a target table has constraints set, new data added to the table has to meet those constraints. That is the purpose of constraints and integrity rules.
I have never seen the necessity to have integrity rules on data warehouse tables. In fact, they would only get in the way.
If this is not a production database table, have the constraints/IR removed. Otherwise you have to prepare your data so that it meets the IR
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.