CODEWITHSUNDEEP
sqlsql-serverinsert
LeandroC
LeandroC

Reputation: 131

SQL Server : How to make a Insert Into with Condition

How is the syntax to make inserts with conditions? For example: verify if the value I want to insert isn't already in the table? (considering that the column could be null)

Upvotes: 0

Views: 258

Answers (5)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Supposing values you want to insert are variables.

You can use IF NOT EXISTS:

IF NOT EXISTS (SELECT * FROM Table1 WHERE Col1 = @Val1 AND Col2 = @Val2)
INSERT INTO Table1 (Col1, COl2) VALUES (@Val1, @Val2)

or you can use SELECT..WHERE with EXISTS

INSERT INTO Table1 (Col1, COl2)
SELECT @Val1, @Val2
WHERE NOT EXISTS (SELECT * FROM Table1 WHERE Col1 = @Val1 AND Col2 = @Val2)

or probably few more methods (NOT IN, LEFT JOIN, MERGE...)

Upvotes: 2

HotblackDesiato
HotblackDesiato

Reputation: 348

You could use a left or right join e.g.

WITH SourceTable AS
(
    SELECT
     *
    FROM ( VALUES
             ('A', 1)
            ,('A', 2)
            ,('B', 1)
            ,('C', 10)) nTable(nCol1, nCol2)
)
SELECT
 *
INTO #SourceTable
FROM SourceTable;

WITH NewRows AS
(
    SELECT
     *
    FROM ( VALUES
             ('A', 2)
            ,('A', 3)
            ,('B', 1)
            ,('C', 11)) nTable(nCol1, nCol2)
)
INSERT #SourceTable
(nCol1
,nCol2)
SELECT
 n.nCol1
,n.nCol2
FROM #SourceTable s
RIGHT JOIN NewRows n
ON s.nCol1=n.nCol1
AND s.nCol2=n.nCol2
WHERE s.nCol1 IS NULL;

Upvotes: 1

Bohemian
Bohemian

Reputation: 425348

Try this:

insert into target_table (col1, col2, ...)
select col1, col2, ...
from source_table
where col_x not in (
    select col_y from target_tabke where col_y is not null)
or (col_x is null and not exists (
    select * from target_table where col_y is null))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Do you mean something like this?

insert into t(col)
    select 'value'
    where not exists (select 1 from t where col = 'value');

However, I would recommend that you use a unique index, filtered index, or foreign key constraint to maintain data integrity.

Upvotes: 0

Timeout
Timeout

Reputation: 7909

Your description is very brief but it sounds like you want the MERGE statement.

http://technet.microsoft.com/en-us/library/bb510625.aspx

Which can be used to insert/update/delete based on if data exists or not all in one statement.

Upvotes: 2

Related Questions