Delete and then Insert into table

[gateleys]

WHen I try to read from one table, then delete rows in target(another table in another db--but both SQL server) based on some condition and then insert the rows (parameters have been assigned for both databases) via a transformer, I get the following error.

Transformer_2.DSLink4: DSD.BCIPut call to SQLExecute failed.
SQL statement:DELETE myTable WHERE hiredate < some_date;
INSERT INTO myTable("ID","Name",""hiredate") VALUES (?,?,?)
SQLSTATE=S1009, DBMS.CODE=0
[DataStage][SQL Client][ODBC][Microsoft][ODBC Driver Manager] Invalid argument value

[kcbland]

Where did your SQL statement originate? Is is generated or user-defined? Something looks fishy with your SQL.

[gateleys]

Where did your SQL statement originate? Is is generated or user-defined? Something looks fishy with your SQL.

Its user defined and queries SQL Server. The SQL is-

DELETE myTable WHERE hiredate < some_date;
INSERT INTO myTable("ID","Name",""hiredate") VALUES (?,?,?)

[kcbland]

Well, you can't do that. The SQL is bound, it's not a place to write a script. If you need to execute a delete statement, then do it either as before-SQL or using a command execution of OSQL.

[chulett]

Or perhaps via a second link - which should be the first link sent to the stage when checking row order. Constrain it to only send one row (if appropriate) and you may need to commit the deletes before you do the insert to avoid locking yourself.

[gateleys]

Well, you can't do that. The SQL is bound, it's not a place to write a script. If you need to execute a delete statement, then do it either as before-SQL or using a command execution of OSQL.

Can you please help me with this? How should I proceed and what/where should I put the delete statement? What would be the command and syntax in the ExecDOS before stage routine?

[kcbland]

Or perhaps via a second link - which should be the first link sent to the stage when checking row order. Constrain it to only send one row (if appropriate) and you may need to commit the deletes before you do the insert to avoid locking yourself.

Check his delete statement, it's doing a WHERE clause with a range, it's not expecting a streaming of values to feed a DELETE cursor.

That method would work if a single row, single column of data was fed to the ODBC stage using custom SQL to just perform the delete. Another stream would then send the data to the insert statement.

The cleaner method would be to write a .bat script using the I/OSQL command line. Put it into a source code directory somewhere and execute it whenever you need.

You could also write a stored procedure to do what you need. You could also load the data into another table, then use a stored procedure to delete and copy the data over.

Probably the best method would be to write a job that selects the keys of the rows you want to delete and write them to a file. Then, read that file and stream it into the ODBC stage and use the generated SQL action of "DELETE EXISTING ROWS". So, to recap, you've selected and spooled the keys to the rows you want to delete, staged to a file, then turn around an stream the keys into a DELETE cursor. Now, just write a new job to stream your inserts.

[chulett]

Check his delete statement, it's doing a WHERE clause with a range, it's not expecting a streaming of values to feed a DELETE cursor.

That method would work if a single row, single column of data was fed to the ODBC stage using custom SQL to just perform the delete. Another stream would then send the data to the insert statement.

Right, exactly what I meant. Assuming the date was a single date and all of the deletes should be done before anything is inserted.

Can't imagine that matching date driven deletes need to be done for every row that gets inserted.

[dylsing]

I have a similar situation where I need to delete then later insert.
I did it by using two links (the ordering is done in transformer), the first will contain only one value which I use as a parameter for the condition to delete and the 2nd to do the insert the rows.

If the first link doing the delete takes very long to execute,
1) will the 2nd link execute at the same time?
2) will the 2nd link execute even before the delete has completed its execution?

So far with my small amount of test data, it is working as I expected, that it gets deleted first, then the insert occurs but I don't know what happens with huge chunks of data to delete.

[chulett]

The links will execute in order, the 2nd waiting on the 1st.