inserting values a table using a data in proc fedsql

[Aexor]

I am getting error while inserting data using proc fedsql

Proc fedsql;

 create table paylist

 (IdNum char(4),

 Gender char(1),

 Jobcode char(3),

 Salary double,

 Birth date,

 Hired date);

insert into paylist

 values('1639','F','TA1',42260,date'26JUN70',date'28JAN91')

 values('1065','M','ME3',38090,date'26JAN54',date'07JAN92')

 values('1400','M','ME1',29769,date'05NOV67',date'16OCT90')

 values('1561','M',null,36514,date'30NOV63',date'07OCT87')

 values('1221','F','FA3',.,date'22SEP63',date'04OCT94');

quit;

error : syntax error at or near values.

Please help me to resolve this issue.

[ChrisNZ]

Like this?

INSERT INTO MyTable ( Column1, Column2 ) VALUES ( Value1, Value2 ), ( Value1, Value2 )

[Patrick · Accepted solution]

According to the documentation here you have to repeat the insert into statement. Additionally the date string format must be as in below code.

proc datasets lib=work nolist nowarn;
  delete paylist;
  run;
quit;

Proc fedsql;
  create table paylist
    (IdNum char(4),
    Gender char(1),
    Jobcode char(3),
    Salary double,
    Birth date,
    Hired date)
    ;
  insert into paylist values ('1639','F','TA1',42260,date'1970-06-26',date'1991-01-28');
  insert into paylist values ('1639','F','TA1',42260,date'1970-06-26',date'1991-01-28');
quit;

[ChrisNZ]

Oh that's (slightly) disappointing. The comma separated syntax would be useful (and potentially faster).

I should have tested, but then I never use proc fedsql, and I simply assumed this standard syntax would be supported. My bad.