TSQL : Check inserting data into the table

Question

Let's say I have this table :

CREATE TABLE [dbo].[finaleTable](
    [VENDId] [bigint] NOT NULL,
    [companyName] [nvarchar](4) NULL,
    ----Others field

    CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED   ([VENDId])
) ON [PRIMARY]
GO

And this query :

INSERT INTO dbo.finaleTable
SELECT *
FROM Tmp1

Before inserting data into the table i have to check the integrity constraint on these 2 fields. If the values exist then move to the next line of data taken from table TMP1. if they do not exist then insert the line.

Could you suggest how can i progress to do this?

`where Tmp1.VENDId not in (select VENDId from dbo.finaleTable)` ? — Alex K., Feb 26 '16 at 16:57
Possible duplicate of [How to avoid duplicates in INSERT INTO SELECT query in SQL Server?](http://stackoverflow.com/questions/2513174/how-to-avoid-duplicates-in-insert-into-select-query-in-sql-server) — Tab Alleman, Feb 26 '16 at 16:58
@AlexK. Thanks! this one seems like it would be really inefficient if i have more than one field as primary key — stoner, Feb 26 '16 at 17:26

score 1 · Accepted Answer · answered Feb 27 '16 at 19:20

I think you need MERGE like this:

MERGE dbo.finaleTable AS target
USING (SELECT VENDId, companyName... FROM Tmp1) AS source
ON target.VENDId = source.VENDId AND ISNULL(target.companyName,'') = ISNULL(source.companyName,'')
WHEN NOT MATCHED THEN
    INSERT (VENDId, companyName...)
    VALUES (source.VENDId, source.companyName...)

If companyName needed to be checked it must be NOT NULL

score 1 · Answer 2 · answered Feb 29 '16 at 10:01

1

You can also use where exists -

insert into finaleTable
select * from table1 as a
where not exists(select 1 from finaleTable as b where b.VENDId = a.VENDId)

answered Feb 29 '16 at 10:01

sam

1,242
3
12
31

TSQL : Check inserting data into the table

2 Answers2