CODEWITHSUNDEEP
sqlsql-serverprocedure
Muhabutti
Muhabutti

Reputation: 1306

How to compare two table columns and insert it into new table stored procedure in SQL Server

I am new to SQL Server. I want to create a procedure which should check two tables and insert the different rows into another table. I need some code example thanks in advance. And if the row is same do not insert else insert.

Table1 is like 

username product shippeddate
Muha     car1     15.08.2014

Table2 is like 

username product shippedate
Muha      car1

Upvotes: 1

Views: 4199

Answers (3)

ram_sql
ram_sql

Reputation: 404

Even you can use the "Except" operator to compare 2 result set and insert the output.

select * from table1
except
select * from table2

Upvotes: 1

Dave C
Dave C

Reputation: 7392

Here is an example using a LEFT OUTER JOIN:

declare @table1 table (username varchar(10), product varchar(10), shippeddate datetime)
declare @table2 table (username varchar(10), product varchar(10), shippeddate datetime)

insert into @table1
select 'Muha','car1','2014-08-15' union
select 'Steve','car2','2014-08-12' 

insert into @table2
select 'Muha','car1',null

insert into @table1
select t2.*
from @table2 t2
left outer join @table1 t1
   on isnull(t1.username,'') = isnull(t2.username,'')
   and isnull(t1.product,'') = isnull(t2.product,'')
   and t1.shippeddate = t2.shippeddate
where t1.username is null

select * from @table1

Here is a second example using NOT EXISTS.

declare @table1 table (username varchar(10), product varchar(10), shippeddate datetime)
declare @table2 table (username varchar(10), product varchar(10), shippeddate datetime)

insert into @table1
select 'Muha','car1','2014-08-15' union
select 'Steve','car2','2014-08-12' 

insert into @table2
select 'Muha','car1',null

insert into @table1
select t2.*
from @table2 t2
where not exists (select * from @table1 t1 where isnull(t1.username,'')=isnull(t2.username,'') and isnull(t1.product,'')=isnull(t2.product,'') and t1.shippeddate=t2.shippeddate)

select * from @table1

In both cases you need to be cognizant of NULL values, as they do not evaluate as equal using =. I left the date comparison without a null handler intentionally in both scenarios -- you would need to decide based on your business rules how they should be treated.

Upvotes: 0

Mark Wojciechowicz
Mark Wojciechowicz

Reputation: 4477

If the point of this is to review the differences between the two tables, you could add a unique index to both and use Data Compare from SSDT to compare them. This would give you a visual representation of the differences and even generate a script to update a target table if you wished.

you can get SSDT here: http://msdn.microsoft.com/en-us/hh297027.aspx Once installed, Go to SQL > Data Compare > New Data Comparison.

Upvotes: 0

Related Questions