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
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.