On Error Resume Next SQL Server 2005

155 pts.
Tags:
SQL Server 2005
SQL Server errors
Is there to achieve On Error Resume Next? insert into table1 select top 1000 columnname from table2 It should continue inserting the next record even if there is an error. It should not stop the execution. It should simply skip the errors. Thanks in advance.

Answer Wiki

Thanks. We'll let you know when a new response is added.

There is no one command to skip errors. This has to be handled programmatically with Try Catch block. I tried this with two tables t1 and t2. Table t1 has a column c1 defined as tinyint. Table T2 has a column c1 defined as smallint. T2 has some rows within the range of tinyint and some rows outside the range of tinyint. If we do a plain insert into t1 select * from t2, since some of the rows are out of range for tinyint, the statement will error out. You can achieve skipping errors by handling it programmatically like shown in the below code.

USE tempdb
CREATE TABLE t1 (c1 TINYINT);
CREATE TABLE t2 (c1 SMALLINT);

INSERT INTO t2 VALUES (10)
INSERT INTO t2 VALUES (260)
INSERT INTO t2 VALUES (20)
INSERT INTO t2 VALUES (330)
INSERT INTO t2 VALUES (30)
INSERT INTO t2 VALUES (440)
INSERT INTO t2 VALUES (40)
INSERT INTO t2 VALUES (550)
INSERT INTO t2 VALUES (50)

DECLARE @c1 smallINT
DECLARE t2cur CURSOR FOR SELECT TOP 10 c1 FROM t2
OPEN t2cur
FETCH NEXT FROM t2cur INTO @c1
insert_block:
BEGIN TRY
WHILE @@fetch_status = 0
BEGIN
insert into t1 VALUES (@c1)
FETCH NEXT FROM t2cur INTO @c1
END
END TRY
BEGIN CATCH
FETCH NEXT FROM t2cur INTO @c1
GOTO insert_block
END CATCH;
CLOSE t2cur; DEALLOCATE t2cur;
</pre>

——————– [kccrosser]
Actually, “on error resume next” is simpler than that. Use the try/catch blocks and just put a dummy statement in the catch block. E.g.:

declare @dummy int
...
fetch ... --
while @@fetch_status = 0
begin
...
begin try
-- your sql statement here
end try
begin catch
set @dummy = 1
end catch
...
fetch next ...
end

On any error, the catch block is invoked. The dummy statement is executed and code resumes after the catch block.

HOWEVER… I am always concerned when I see “on error resume next”, or code like the above. I hope you at least put lots of comments explaining why you are choosing to ignore ANY error. A more correct approach would be to put an error handler in the Catch block and test for the “expected” errors – ignore those, but do something useful when an unexpected error is detected.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Doing a row by row insert of rows into the SQL Server will be many times slower than doing a single recordset insert. When doing your insert into the table you should check the destination table, and only insert rows which do not exist.
    insert into table1 
    select top 1000 columnname 
    from table2
    WHERE NOT EXISTS (SELECT * FROM table1 WHERE table1.id = table2.id)
    
    66,360 pointsBadges:
    report
  • Jsql
    Thanks very much. Inserting row by row is a very slow process and using cursor on 50 million records is just not going to end the process. Its good if we can capture the error and store it in log to know the cause. I was looking for, if there is a way to ignore errors while Bulk Insert like I've mentioned in the query. Looks like the solution is to insert row by row only to ignore errors.. Thanks all again.
    155 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following