continue insert if one row fails

harinathu 6 Reputation points

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

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,565 questions
0 comments No comments
{count} votes

5 answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points

    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.

    0 comments No comments

  2. Naomi 7,361 Reputation points


    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
    0 comments No comments

  3. Tom Phillips 17,716 Reputation points

    You could check your code first

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

  4. Erland Sommarskog 102.4K Reputation points

    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.

    0 comments No comments

  5. Bert Zhou-msft 3,421 Reputation points


    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.

    0 comments No comments