continue insert if one row fails

Question

Hi I have one doubt in sql server
How to continue insert if one row fails

I have two table emp and empref table .
I want insert records into empref table using emp table .
while loading time one of record getting unexpected format .
emp and empref both tables have same columns but date types are different.

source : emp
CREATE TABLE [dbo].[emp](
[id] varchar NULL,
[name] varchar NULL,
[sal] [int] NULL
)
INSERT [dbo].[emp] ([id], [name], [sal]) VALUES (N'1', N'abc', 100)
INSERT [dbo].[emp] ([id], [name], [sal]) VALUES (N'2', N'xyz', 200)
INSERT [dbo].[emp] ([id], [name], [sal]) VALUES (N'a4', N'un', 300)

in empref ref expected int value but one values alphanumerice values.
Target : empref
CREATE TABLE [dbo].[empref](
[id] [int] NULL,
[name] varchar NULL,
[sal] [int] NULL
)

expecte result in empref table :

id |name | name
1 |abc | 100
2 |xyz | 200
I tried like below :
begin try
insert into empref
select * from emp
end try
begin catch
select ERROR_MESSAGE()erro
end catch
above query not giving expected result .
please tell me how to write query to achive this task in sql server

Answer 1

You can't.

insert into empref
select * from emp

Is a single statement. It either fully completes, or fully fails. The only alternative is to insert the rows 1 at a time.

Answer 2

Try

INSERT [dbo].[empref] ([id], [name], [sal])
select try_cast(id as int), name, sal
from emp where try_cast(Id as int) is not null

Answer 3

You could check your code first

 insert into empref
 select * from emp
where isnumeric(id)=1

Answer 4

As others have said, you cannot to this with a single statement, since statements are atomic.

The simple but slow way out is to run a cursor and insert the rows one by one, and catch and log errors.

A faster way is to process the rows reasonably sized chunks and if a chunk has an error you divide the chunk into smaller ones and eventually you have process some of them one-by-one.

Answer 5

Hi，@harinathu

Welcome to Microsoft T-SQL Q&A Forum!

According to your description, there are two ways to achieve it, the first is insert into select, the second is select into, for the first one, the experts have given the answer. Here is the second way to achieve it.

select id,name,sal  
into empref  
from emp  
where try_cast(Id as int) is not null  
select * from empref  

Remarks: I do not recommend using isnumeric for filter conditions here. I use this function in my daily work, but there is always a bug, please see when selecting isNumeric('1d00'), according to our understanding, we will think that it will return 0 here , because the value is not a numeric type, but the SQL prompt is 1. Sometimes it seems that it should not be a numeric type. In fact, it can be converted into a number to determine what value the function will return. According to this principle, the characters '+', '-', 'd', 'e', '$', '£' are returned as 1 in the incoming data.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.