Inserting data into a table that has more columns

[Myurathan]

Hi Helpers,

I have a table with 200 columns. I would push this table's data into a master table that has 250 columns. Column names are same. When I push the data I want unavailable columns to get a NULL value. I am using following code to push my data to a table that has same columns.

Proc SQL;
  Insert Into one.tbl_one Select * From tble_two;
Quit;

Please help.

Thanks in advance.

[RW9]

Some starter questions.  

Why have a dataset with 200+ variables, sounds like bad modelling in either SAS or Database.

Why use SQL, unless your connecting to a database, it would be easier to set two datasets in datastep.

[Myurathan]

It has 200+ variables as many units use the table for various purposes. It is pushing the data to a SQL database. There might be a better way to do it which I am not aware as I am a beginner. Thanks, @RW9.

[RW9]

Well, if you continue with the bad model, and continue to use SQL, then your only option is to put the variables you want to insert into the code:

proc sql;
  insert into one.tbl_one (var1,var2,var3...)
  select * From tble_two;
quit;

SQL wasn't designed to be used with lots of variables, so its a chore to work with if you have a bad model.  Vice versa, SAS datastep is easier to work with more variables.

[singhsahab · Accepted solution]

Hello ,

In your case proc SQL insert will not work. You can use PROC APPEND procedure with FORCE options. 

proc append base=tble_one data=tble_two force;
run;