BookmarkSubscribeRSS Feed
Tom_Bailey
Calcite | Level 5

I'm running an extract from a SQL Server table.  Before I load into my destination table, I want to remove any keys in the destination table that have a match in the work table (W8T0KH11) I produced from my extract task.

 

The destination table has a PK on 'TransactionKey'.  If I don't run the deletion, the proc append that is run in the Table Loader errors due to the key already existing.  The reason I want to delete and re-append is because the rows have been updated at source.

 

In the delete task, I have said where W8T0KH11.TransactionKey = DirectoryBaskets.TransactionKey.  This fails when I try to run the delete stating "ERROR: Unresolved reference to table/correlation name W8T0KH11."  

 

I'm of the opinion that for some reason the work table is not being created before I try to run the delete.  From the log it is defined and then immediately moves to process delete.

 

I want to have these deletions run before the table loader and appreciate any pointers.

 

Flow screenshot :

Delete key flow task.png

3 REPLIES 3
LinusH
Tourmaline | Level 20
Spontanously it sounds more of a syntax rather than an execution error, a full log might answer that.
But I think you are doing this a bit more complicated than needed. The Table Loader transform lets you either update/overwrite rows on existing keys, or just skip adding them (which to chose depends on the purpose of the target table).
Data never sleeps
Tom_Bailey
Calcite | Level 5

Hi,

 

Thanks for the response.

 

If you mean the Update/Insert Load style, I found this was performing individual loops per row.  I have 700k+ rows to analyse and it was taking far too long (and had run up some impressive size logs).

Tom_Bailey
Calcite | Level 5

.... actually looks like that was due to the technique I was using.  Modify by index looks OK.

 

Thanks

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 1286 views
  • 0 likes
  • 2 in conversation