SQL Server stop/exit query on error
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 &gt; 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