CODEWITHSUNDEEP
c#ddlsqlbulkcopydmlsqltransaction
sergeidave
sergeidave

Reputation: 692

Is it possible to ALTER TABLE (DDL) in the middle of an DML transaction on the same table?

Say, you begin a SqlTransaction from C# inside which you are inserting data from a DataTable into a SQL table, but this data is A LOT of data, so you do it 'in chunks' with a SqlBulkCopy inside the SQL transaction and only commit the transaction if ALL the chunks copied succesfully.

Now, let's say you know that at times the length of the values being inserted will surpass the max column size in the target table, so the SqlBulkCopy will fail at that point for that particular 'chunk'. Is it possible (or even advisable) to catch this exception and alter the table/column to increase the max length of the target column, then continue SqlBulkCopy'ing data picking up the next 'chunk' to be copied even though you haven't committed your 'insert transaction' you began with?

So, trying to paraphrase this, is it possible to start a DML transaction, stop in the middle of this uncommitted transaction, perform a DDL to alter the target table, then complete the original DML and commit it?

Upvotes: 0

Views: 244

Answers (1)

JeremiahDotNet
JeremiahDotNet

Reputation: 910

To answer your question, it is possible to modify a table between insert statements. If you scope the transaction to each batch then you would only need to rollback 1 batch to update the column length. There is more overhead with this approach than setting the column lengths before starting the data insert.

A good approach would be to check each column of your source data for a maximum length and then update the target database with the new column length(s) before starting the data import. That way you know the target database can accept all the data it will be provided.

Upvotes: 2

Related Questions