On Error Resume Next SQL Server 2005
Home > IT Answers > SQL Server > On Error Resume Next SQL Server 2005
Jsql
140 pts.
0
Q:
On Error Resume Next SQL Server 2005
SQL Server 2005, SQL Server errors, SQL Server error messages
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.

 

 

 

ASKED: Sep 4 2009  10:28 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Kccrosser
1850 pts.
0
A:
 RATE THIS ANSWER
+1
Click to Vote:
  •   1
  •  0
  • AddThis Social Bookmark Button
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;



-------------------- [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.
Last Answered: Sep 4 2009  6:44 PM GMT by Kccrosser   1850 pts.
Latest Contributors: RajeshRamadas   30 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

mrdenny   46765 pts.  |   Sep 4 2009  6:42PM GMT

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)

 

Jsql   140 pts.  |   Sep 5 2009  2:54AM GMT

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.

 
0