Compare two Table data and insert changed field to the third table

Question

Post reply

Compare two Table data and insert changed field to the third table

F/-\R//-\Z

Valued Member

Points: 54
More actions
August 12, 2014 at 9:20 am

#294364

hi
I want Compare two Table data and insert changed field to the third table
who can help me
thanks a lot:-):hehe:

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Can you give us table DDL for your 3 tables as well as some sample data for the two that you need to compare?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

F/-\R//-\Z Valued Member Points: 54 More actions · Answer 2

yes I want get changed raw in new table

my 3 table struc. is:

CREATE TABLE [dbo].[BedriddenBed2](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[ReferralCode] [char](8) NOT NULL,

[BedDate] [char](10) NOT NULL,

[BedTime] [char](8) NOT NULL,

[BedCode] [char](5) NULL,

[BedDays] [numeric](18, 0) NULL,

[UserCode] [char](7) NULL,

[ISVirtual] [bit] NULL,

[FirstName] [nvarchar](50) NULL,

[LastName] [nvarchar](50) NULL,

[BeneficiaryName] [nvarchar](50) NULL,

[ReferralDate] [char](10) NULL,

CONSTRAINT [PK_BedriddenBed2] PRIMARY KEY CLUSTERED

(

[ReferralCode] ASC,

[BedDate] ASC,

[BedTime] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

F/-\R//-\Z (8/12/2014)
hi
I want Compare two Table data and insert changed field to the third table
who can help me
thanks a lot:-):hehe:

While I suspect that the requirements might possibly need refining, here is my initial interpretation.

😎

USE tempdb;

GO

/*

Select records from the first table that do not match

the second table and insert the results into the

third table.

*/

INSERT INTO dbo.BedriddenBed3

(

[ReferralCode]

,[BedDate]

,[BedTime]

,[BedCode]

,[BedDays]

,[UserCode]

,[ISVirtual]

,[FirstName]

,[LastName]

,[BeneficiaryName]

,[ReferralDate]

)

SELECT

B1.[ReferralCode]

,B1.[BedDate]

,B1.[BedTime]

,B1.[BedCode]

,B1.[BedDays]

,B1.[UserCode]

,B1.[ISVirtual]

,B1.[FirstName]

,B1.[LastName]

,B1.[BeneficiaryName]

,B1.[ReferralDate]

FROM dbo.BedriddenBed1 B1

INNER JOIN dbo.BedriddenBed2 B2

ON B1.ReferralCode = B2.ReferralCode

AND B1.BedDate = B2.BedDate

AND B1.BedTime = B2.BedTime

WHERE B1.[BedCode] <> B2.[BedCode]

OR B1.[BedDays] <> B2.[BedDays]

OR B1.[UserCode] <> B2.[UserCode]

OR B1.[ISVirtual] <> B2.[ISVirtual]

OR B1.[FirstName] <> B2.[FirstName]

OR B1.[LastName] <> B2.[LastName]

OR B1.[BeneficiaryName] <> B2.[BeneficiaryName]

OR B1.[ReferralDate] <> B2.[ReferralDate];

Phil Parkin SSC Guru Points: 246964 More actions · Answer 4

F/-\R//-\Z (8/12/2014)
hi
I want Compare two Table data and insert changed field to the third table
who can help me
thanks a lot:-):hehe:

So all three tables have the same structure?

And table three should contain all of the rows in table 2 which either

a) Do not have a matching row in table one, or

b) Have one or more columns whose value is different.

Is that correct?

In future, please provide sample data as well as DDL. Also you should provide desired results, based on your sample data. This avoids us having to play 'guess the requirement', as we are doing here.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

F/-\R//-\Z Valued Member Points: 54 More actions · Answer 5

hi

I try your code but this is not work!!!

no detect deferent row!

F/-\R//-\Z Valued Member Points: 54 More actions · Answer 6

F/-\R//-\Z

Valued Member

Points: 54

August 17, 2014 at 6:10 am

#1738670

I want do this in picture

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 7

F/-\R//-\Z (8/17/2014)
hi
I try your code but this is not work!!!
no detect deferent row!

In a way that is not too bad, we just have to figure out why;-)

😎

Now I have few questions:

1. Do you have some sample data we can use?

2. Does the code have to detect missing rows?

3. If 2 = Yes, in which tables?

4. Can you also answer Phil's questions?

F/-\R//-\Z Valued Member Points: 54 More actions · Answer 8

hi and thanks

answer of Eirikur's questions

1. Do you have some sample data we can use?

I'm sorry no I do not have

2. Does the code have to detect missing rows?

no code is not detected missing rows(table 3 row sending to our hardware for see this information on LED board)

3. If 2 = Yes, in which tables?

4. Can you also answer Phil's questions?

answer of Phil's questions

a) Do not have a matching row in table one, or?

no do not matching row in table 1, [BedCode] is unique

b) Have one or more columns whose value is different.

i need if field of [FirstName] [LastName] [BeneficiaryName] [ReferralDate] and age is changed

i copy row to table 3

avijit2988 Valued Member Points: 59 More actions · Answer 9

avijit2988

Valued Member

Points: 59

August 19, 2014 at 9:50 am

#1739280

You may try using Merge statement

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 10

F/-\R//-\Z (8/17/2014)
hi
I try your code but this is not work!!!
no detect deferent row!

Here is the the code I previously posted with some sample data, works fine on my end;-)

😎

USE [tempdb]

GO

INSERT INTO [dbo].[BedriddenBed1]

([ReferralCode]

,[BedDate]

,[BedTime]

,[BedCode]

,[BedDays]

,[UserCode]

,[ISVirtual]

,[FirstName]

,[LastName]

,[BeneficiaryName]

,[ReferralDate])

VALUES

('A00001','2014-01-01','22:01','B00',10,'ABC0',0,'Abcd010','Efgh001','Qwert Yuio','2013-12-31')

,('A00002','2014-01-02','22:02','B00',10,'ABC0',0,'Abcd020','Efgh002','Qwert Yuio','2013-12-31')

,('A00003','2014-01-03','22:03','B00',10,'ABC0',0,'Abcd030','Efgh003','Qwert Yuio','2013-12-31')

,('A00004','2014-01-04','22:04','B00',10,'ABC0',0,'Abcd040','Efgh004','Qwert Yuio','2013-12-31')

,('A00005','2014-01-05','22:05','B00',10,'ABC0',0,'Abcd050','Efgh005','Qwert Yuio','2013-12-31')

,('A00006','2014-01-06','22:06','B00',10,'ABC0',0,'Abcd060','Efgh006','Qwert Yuio','2013-12-31')

,('A00007','2014-01-07','22:07','B00',10,'ABC0',0,'Abcd070','Efgh007','Qwert Yuio','2013-12-31')

,('A00008','2014-01-08','22:08','B00',10,'ABC0',0,'Abcd080','Efgh008','Qwert Yuio','2013-12-31')

,('A00009','2014-01-09','22:09','B00',10,'ABC0',0,'Abcd090','Efgh009','Qwert Yuio','2013-12-31')

,('A00010','2014-01-10','22:10','B00',10,'ABC0',0,'Abcd100','Efgh010','Qwert Yuio','2013-12-31');

INSERT INTO [dbo].[BedriddenBed2]

([ReferralCode]

,[BedDate]

,[BedTime]

,[BedCode]

,[BedDays]

,[UserCode]

,[ISVirtual]

,[FirstName]

,[LastName]

,[BeneficiaryName]

,[ReferralDate])

VALUES

('A00001','2014-01-01','22:01','B00',10,'ABC0',0,'Abcd010','Efgh001','Qwert Yuio','2013-12-31')

,('A00002','2014-01-02','22:02','B00',10,'ABC0',0,'Abcd020','Efgh002','Qwert Yuio','2013-12-31')

,('A00003','2014-01-03','22:03','B00',10,'ABC0',0,'Abcd030','Efgh003','Qwert Yuio','2013-12-31')

,('A00004','2014-01-04','22:04','B00',10,'ABC02',0,'Abcd040','Efgh004','Qwert Yuio','2013-12-31')

,('A00005','2014-01-05','22:05','B00',10,'ABC02',0,'Abcd050','Efgh005','Qwert Yuio','2013-12-31')

,('A00006','2014-01-06','22:06','B00',10,'ABC0',0,'Abcd060','Efgh006','Qwert Yuio','2013-12-31')

,('A00007','2014-01-07','22:07','B00',10,'ABC0',0,'Abcd070','Efgh007','Qwert Yuio','2013-12-31')

,('A00008','2014-01-08','22:08','B01',10,'ABC0',0,'Abcd080','Efgh008','Qwert Yuio','2013-12-31')

,('A00009','2014-01-09','22:09','B01',10,'ABC0',0,'Abcd090','Efgh009','Qwert Yuio','2013-12-31')

,('A00010','2014-01-10','22:10','B01',10,'ABC0',0,'Abcd100','Efgh010','Qwert Yuio','2013-12-31');

USE tempdb;

GO

/*

Select records from the first table that do not match

the second table and insert the results into the

third table.

*/

INSERT INTO dbo.BedriddenBed3

(

[ReferralCode]

,[BedDate]

,[BedTime]

,[BedCode]

,[BedDays]

,[UserCode]

,[ISVirtual]

,[FirstName]

,[LastName]

,[BeneficiaryName]

,[ReferralDate]

)

SELECT

B1.[ReferralCode]

,B1.[BedDate]

,B1.[BedTime]

,B1.[BedCode]

,B1.[BedDays]

,B1.[UserCode]

,B1.[ISVirtual]

,B1.[FirstName]

,B1.[LastName]

,B1.[BeneficiaryName]

,B1.[ReferralDate]

FROM dbo.BedriddenBed1 B1

INNER JOIN dbo.BedriddenBed2 B2

ON B1.ReferralCode = B2.ReferralCode

AND B1.BedDate = B2.BedDate

AND B1.BedTime = B2.BedTime

WHERE B1.[BedCode] <> B2.[BedCode]

OR B1.[BedDays] <> B2.[BedDays]

OR B1.[UserCode] <> B2.[UserCode]

OR B1.[ISVirtual] <> B2.[ISVirtual]

OR B1.[FirstName] <> B2.[FirstName]

OR B1.[LastName] <> B2.[LastName]

OR B1.[BeneficiaryName] <> B2.[BeneficiaryName]

OR B1.[ReferralDate] <> B2.[ReferralDate];

Phil Parkin SSC Guru Points: 246964 More actions · Answer 11

Maybe this would be a slightly better WHERE clause (handles NULLs)?

WHERE NOT EXISTS ( SELECT B1.[BedDays]

,B1.[UserCode]

,B1.[ISVirtual]

,B1.[FirstName]

,B1.[LastName]

,B1.[BeneficiaryName]

,B1.[ReferralDate]

INTERSECT

SELECT B2.[BedDays]

,B2.[UserCode]

,B2.[ISVirtual]

,B2.[FirstName]

,B2.[LastName]

,B2.[BeneficiaryName]

,B2.[ReferralDate] )

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 12

Phil Parkin (8/21/2014)
Maybe this would be a slightly better WHERE clause (handles NULLs)?
WHERE NOT EXISTS ( SELECT B1.[BedDays]
,B1.[UserCode]
,B1.[ISVirtual]
,B1.[FirstName]
,B1.[LastName]
,B1.[BeneficiaryName]
,B1.[ReferralDate]
INTERSECT
SELECT B2.[BedDays]
,B2.[UserCode]
,B2.[ISVirtual]
,B2.[FirstName]
,B2.[LastName]
,B2.[BeneficiaryName]
,B2.[ReferralDate] )

Good point, thanks Phil!

😎

F/-\R//-\Z Valued Member Points: 54 More actions · Answer 13

thanks everybody

also I try this code and this is working fine

insert into diff

select *

from (

select *

from c1

except

select *

from c2

) as T