SAS Insert clumn ERROR

[sathya66]

All,

proc sql;
create table TEST.SS1 AS /*SS1 sas table  now 3 coumns*/
select * from ssmeta.ss2; /* metadata Teradata table has 3 coumns */
quit;

If I do this again with insert, I am getting below ERROR.
proc sql;
insert into TEST.SS1 (procid, queryid)
select * from ssmeta.ss2;
quit;
ERROR: Attempt to insert more columns than specified after the INSERT table name.

But If Ido like below then that will work.

data TEST.SS1(keep=procid queryid);
set ssmeta.ss2;
run;

What could be the reason for this.

[FredrikE]

Hi!

You try to insert 3 columns into 2...

Why not just select the two you have?

insert into TEST.SS1 (procid, queryid)
select procid, queryid from ssmeta.ss2;

It differs how SQL and data step handles kept variables.

[sathya66]

Hi,

I Agree with you .

why it is working with data step?

and why it is not working with insert .what is the logic behind it for not work.

[ChrisNZ]

> why it is working with data step?

A data step does not modify the table, it creates a new table (I assume you use the SET statement) .

An insert statement adds data (rows) into an existing table.

cheese and chalk.

[ballardw]

The data step behaves entirely different than SQL table syntax.

SET by default brings all of the variables in a source data set. You have just told the output to keep a selected set of variables that exist.

You do not show any details of what Test.SS1 might contain as far as variables after the first run. But you cannot insert a variable that does not already exist.

Instead of SELECT * use a select that only lists variables that already exist in Test.SS1 (if that target data set already exists).

[Tom]

No really an apples to apples comparison.

The equivalent SQL statement would be:

create table TEST.SS1(keep=procid queryid) as 
  select * from ssmeta.ss2
;

The main problem with the INSERT statement in SQL is it does not have a method for you to tell it to match the columns by NAME instead of POSITION.  So you either have to name them.

insert into TEST.SS1 (procid, queryid)
  select procid, queryid from ssmeta.ss2
;

Or if you are positive they are in the same order in SS1 and SS2.

insert into TEST.SS1
  select * from ssmeta.ss2(keep=procid queryid)
;

[sathya66]

Thanks All,

this is what I was trying to do.

proc sql;
create table TEST.SS1 AS /* SAS Table*/
select * from ssmeta.ss2 /*metadata Teradata table*/;
quit;

proc sql;
insert into TEST.SS1 (procid, queryid)
select * from ssmeta.ss2;
quit;

proc sql;
select * from TEST.SS1;
quit;


	ALL OK

Teradata:
ALTER TABLE ssmeta.ss2 ADD p_id decimal

proc sql;
insert into TEST.SS1 (procid, queryid)
select * from ssmeta.ss2;
quit;

proc sql;
select * from TEST.SS1;
quit;

	ALL OK

SAS : update metadata from DI studio

proc sql;
insert into TEST.SS1 (procid, queryid)
select * from ssmeta.ss2;
quit;


	ERROR: Attempt to insert more columns than specified after the INSERT table name.

if source table changes and then if I update a metadata table without informing the user. Will it impact the users or not.

if it is a proper DI job then I think there is no issue. will it impact the user written code?

[Tom]

I would say don't modify the tables. That is what change control processes are for.

You might want to tell users how to code defensively to protect their code from unstable sources.

If they only want some of the variables then use the list of what they want, that is put the KEEP= option on the INPUT not the OUTPUT.

If you want to insert records from an table and only use the variables that the target table has you can use PROC APPEND.

proc append base=TEST.SS1 data=ssmeta.ss2 force;
run;

[Patrick]

Updating the metadata doesn't update already existing physical tables.

For user written code metadata table definitions come only into play if you're using the meta engine in the libname statement. ...but the main issue here is the Select * so once you add a column on the Teradata side and you then try to insert columns into an already existing physical SAS table which doesn't have this column added then you'll get the error you observe.

For DIS: Adding a column to table metadata will have an impact

- new columns won't be mapped from source to target

- the underlying physical table doesn't get changed automatically. If a transformation always drops and recreates the table then it's no issue but else you either need to drop the SAS table manually so DIS re-creates it based on the new metadata or you need to add the column to the physical SAS table via a one off script.

-> Don't change table metadata used by DIS jobs without impact analysis as it might require change for the impacted DIS jobs.