asked on

MS SQL + Insert Into Table - If Doesnt Exist

I have a table that I want to insert new data at the end of each day. The problem is I only want to insert data if the row doesnt already exist, the problem is to find out if the data already exists, depends on 3 different columns.

So Columns - OrderNumber, OrderDate, CustomerID need to be completely different, as OrderNumber, CustomerID could have multiple OrderDates and so on.

I was thinking of making OrderNumber, OrderDate, CustomerID as primary keys, but then when i try inserting I get an error and not sure if rows not in will still insert.

Thanks

ASKER CERTIFIED SOLUTION

Mike Eghtebas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

Hi,
Please use below-

IF NOT EXISTS ( SELECT TOP 1 1 FROM Yourtable WHERE OrderNumber = @OrderNumber	
				AND OrderDate = @OrderDate AND CustomerID = @CustomerID )

		BEGIN

				INSERT INTO Yourtable(OrderNumber, OrderDate, CustomerID)
				VALUES (@OrderNumber, @OrderDate, @CustomerID)

				SELECT 'RowInserted'
				
		END

ELSE

		BEGIN
				
				SELECT 'Row Already exists'

		END

Open in new window

Hope it helps!

Pawan Kumar

Above method is the best one. Other method you can use is below-

IF ( SELECT COUNT(1) FROM Yourtable WHERE OrderNumber = @OrderNumber AND OrderDate = @OrderDate AND CustomerID = @CustomerID ) = 0 

		BEGIN

				INSERT INTO Yourtable(OrderNumber, OrderDate, CustomerID)
				VALUES (@OrderNumber, @OrderDate, @CustomerID)

				SELECT 'RowInserted'
				
		END

ELSE

		BEGIN
				
				SELECT 'Row Already exists'

		END

Open in new window

Hope it helps!

dkilby

ASKER

Thanks

Pawan Kumar

I was thinking of making OrderNumber, OrderDate, CustomerID as primary keys, but then when i try inserting I get an error and not sure if rows not in will still insert.

Also note that you can create the primary key. But when try to insert the row it will throw the error and the rows will not be inserted. So the best method is to use the EXISTS one. (My First Comment).

Hope it helps!

Pawan Kumar

Hi dkilby,
Seems like you have selected the incorrect comment as solution.

I dont think we can use EXCEPT for this.

Mike Eghtebas

Hi All,

To earn my points, and to get an ok from Pawan, I have made the following sample code to make sure EXCEPT operator in fact does the job.

Setup: Created to tables tSource and tTarget. Please tell us the data in it is what you have typically. If different, I will be glad to revise my test tables and make sure it will work per your instruction.

use EE

create table tSource(CustomerID char(2), OrderNumber varchar(10), OrderDate date); 
create table tTarget(CustomerID char(2), OrderNumber varchar(10), OrderDate date);

GO 

Insert into tSource(CustomerID, OrderNumber, OrderDate) values
('c1', 'Order_01', '1/1/2017')      -- because this already exist in tTarget, it will not be added again.
, ('c1', 'Order_02', '1/1/2017')    -- this will be added
, ('c2', 'Order_03', '1/1/2017')    -- this will be added
, ('c2', 'Order_04', '1/1/2017')    -- this will be added
, ('c3', 'Order_05', '1/1/2017')    -- this will be added
, ('c3', 'Order_06', '1/1/2017')    -- this will be added

Insert into tTarget(CustomerID, OrderNumber, OrderDate) values
('c1', 'Order_01', '1/1/2017');

GO

select * from tSource;
select * from tTarget;

CustomerID OrderNumber OrderDate
---------- ----------- ----------
c1         Order_01    2017-01-01
c1         Order_02    2017-01-01
c2         Order_03    2017-01-01
c2         Order_04    2017-01-01
c3         Order_05    2017-01-01
c3         Order_06    2017-01-01

(6 row(s) affected)

CustomerID OrderNumber OrderDate
---------- ----------- ----------
c1         Order_01    2017-01-01

(1 row(s) affected)

Open in new window

Use EXCEPT operator to see if correct rows could be isolated before we could use Insert Into:

SELECT CustomerID, OrderNumber, OrderDate   
FROM tSource  
EXCEPT  
SELECT CustomerID, OrderNumber, OrderDate   
FROM tTarget;  

CustomerID OrderNumber OrderDate
---------- ----------- ----------
c1         Order_02    2017-01-01
c2         Order_03    2017-01-01
c2         Order_04    2017-01-01
c3         Order_05    2017-01-01
c3         Order_06    2017-01-01

(5 row(s) affected)

Open in new window

As our last step, now we can use Insert Into with Select...Except we have above:

Insert into tTarget(CustomerID, OrderNumber, OrderDate)
SELECT CustomerID, OrderNumber, OrderDate   
FROM tSource  
EXCEPT  
SELECT CustomerID, OrderNumber, OrderDate   
FROM tTarget;  

select * from tTarget;

CustomerID OrderNumber OrderDate
---------- ----------- ----------
c1         Order_01    2017-01-01
c1         Order_02    2017-01-01
c2         Order_03    2017-01-01
c2         Order_04    2017-01-01
c3         Order_05    2017-01-01
c3         Order_06    2017-01-01

(6 row(s) affected)

Open in new window

Thanks,

Mike

dkilby

ASKER

The Except statement worked perfectly for me and why I gave the points as it was the first answer I saw and when I tried it with my insert statement it was exactly what I needed

Mike Eghtebas

Also, I was impressed when I saw you already have done it. The truth to be told, I didn't clearly see all of the process when I added my comment before I left home but I knew using except, we will be able to isolate the desired rows per your description.

Thank you for the points.

Mike