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

 

 

 



Software/Hardware used:
ASKED: September 4, 2009  10:28 AM
UPDATED: September 5, 2009  2:54 AM
RELATED QUESTIONS
Answer Wiki:
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. <pre> 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.
Last Wiki Answer Submitted:  September 4, 2009  6:44 pm  by  RajeshRamadas   30 pts.
All Answer Wiki Contributors:  RajeshRamadas   30 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

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)
Denny Cherry
 64,520 pts.
 

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.

Jsql
 155 pts.