Continue insert if one row fails in SQL Server

Question

I have a question about SQL Server - how to continue insert if one row fails?

I have two tables, emp and empref. I want insert rows into the empref table using emp table.

While inserting, one of the rows fails with an "unexpected format" error. emp and empref both have the same columns, but datatypes are different.

Source : emp

CREATE TABLE [dbo].[emp]
(
    [id] [varchar](50) NULL,
    [name] [varchar](50) NULL,
    [sal] [int] NULL
)

INSERT INTO [dbo].[emp] ([id], [name], [sal]) VALUES (N'1', N'abc', 100)
INSERT INTO [dbo].[emp] ([id], [name], [sal]) VALUES (N'2', N'xyz', 200)
INSERT INTO [dbo].[emp] ([id], [name], [sal]) VALUES (N'a4', N'un', 300)

In the empref ref expected int value but one values alphanumeric values.

CREATE TABLE [dbo].[empref]
(
    [id] [int] NULL,
    [name] [varchar](50) NULL,
    [sal] [int] NULL
) 

Expected result in empref table :

id |name | name 
---+-----+------
1  |abc  | 100
2  |xyz  | 200

I tried like this:

BEGIN TRY
    INSERT INTO empref 
        SELECT * FROM emp 
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() erro
END CATCH

This query is not returning the expected result.

Please tell me how to write the query to achieve this task in SQL Server

Answer 1

You can use TRY_CAST to check if the conversion will work

INSERT INTO empref
  (id, name, sal)
SELECT
  TRY_CAST(e.id AS int),
  e.name,
  e.sal
FROM emp e
WHERE TRY_CAST(e.id AS int) IS NOT NULL;

db<>fiddle

Note: Always specify columns to be inserted into.