SQL Server stop/exit query on error

Question

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

Answer 1

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

Answer 2

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