SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Insert default row into tables

Reply
Occasional Contributor
Posts: 17

Insert default row into tables

I have quite a basic requirement to insert a record into a table and based on a hard coded sql statement using SAS DI.

 

Initially I thought I could use a insert row transform but this requires a source table. So what is the best way of doing this?

Super User
Super User
Posts: 8,174

Re: Insert default row into tables

?

 

Perhaps providing some information, examples about your question would be helpful.

At a guess, SQL needs at least one source record to insert a record in a dataset, so just choose one of the sashlpe datasets which you can identify the one record from and use that with a where - or alternatively, create a dataset with one row and use that.  Without any other info not sure what to say.

 

Occasional Contributor
Posts: 17

Re: Insert default row into tables

If it helps I'll be more specific. I have a bunch of empty dimension table following an initial deployment. I need to insert a default unknown record in each of them. As an example:
-1, 'Unknown'
This will be hardcoded as a sql statement so there is no source table.
Let me know what you need I'll provide additional information.
Super User
Super User
Posts: 8,174

Re: Insert default row into tables

Its advisable to show specific examples, I can't see your process.  I also don't know what "This will be hardcoded as a sql statement so there is no source table." means.  At a guess, and I show a specific example to show what I have taken from your text:

data have;
  length a b $50;
  output;
  output;
run;

proc sql;
  update have
  set a="1-Unknown",
      b="1-Unknown";
quit;
Respected Advisor
Posts: 4,186

Re: Insert default row into tables

@amorts

Assuming the target table exists and is never empty: Just use the target table also as source table, select only 1 record (if it's a SAS table then use obs=1) and then use the expressions to set the columns to the values of your choosing before loading into the target table. 

You can use the APPEND transformation for all of this.

Occasional Contributor
Posts: 17

Re: Insert default row into tables

Thanks both for your help! I will give that a go.
Ask a Question
Discussion stats
  • 5 replies
  • 142 views
  • 0 likes
  • 3 in conversation