Delete and then Insert into table
Moderators: chulett, rschirm, roy
Delete and then Insert into table
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
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
Where did your SQL statement originate? Is is generated or user-defined? Something looks fishy with your SQL.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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 wrote: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.
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.chulett wrote: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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Right, exactly what I meant. Assuming the date was a single date and all of the deletes should be done before anything is inserted.kcbland wrote: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.
Can't imagine that matching date driven deletes need to be done for every row that gets inserted.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.