0

I have the below 2 commands (insert and drop) in a query. When I run the query I get an error saying:

String or binary data would be truncated.

When I go to check the source table I find out that the table does not exist. It was deleted by the drop statement. I thought the second statement would not be executed because the first one (the insert) failed.

How can I write the below code so that it will fail the entire query and not drop the table unless the first part (the insert) succeeds?

Do I need to use Begin, End and Go?

INSERT INTO SomeTable
SELECT * FROM SomeOtherTable

DROP table SomeOtherTable
4

Basically, you need to test to see if the insert succeeded. It will either succeed and everything will be inserted, or it fails and nothing is inserted. Check @@ROWCOUNT like this:

create table SomeTable (someVale nvarchar(10));
create table SomeOtherTable (someVal nvarchar(11));

insert SomeOtherTable values
(N'1234567890'),
(N'12345678910');

select * from SomeOtherTable;

INSERT INTO SomeTable
SELECT * FROM SomeOtherTable;

if @@ROWCOUNT > 0
    DROP table SomeOtherTable;

select * from SomeOtherTable;

You could wrap it in a transaction if you want or use a GOTO with a label. It all depends on how you want to terminate.

For a full list of options and considerations, see Erland Sommarskog's reference articles:

Error and Transaction Handling in SQL Server

0

I would use XACT_ABORT setting. This will abort the entire transaction if any error occurs.

SET XACT_ABORT ON
BEGIN TRAN
INSERT INTO SomeTable
SELECT * FROM SomeOtherTable

DROP table SomeOtherTable
COMMIT
  • This works too. I didn't add this as an answer because it affects the entire session. That may or may not be desirable. – Steve Mangiameli Jun 2 '16 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.