Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Compare two Table data and insert changed field to the third table Expand / Collapse
Author
Message
Posted Tuesday, August 12, 2014 9:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:52 AM
Points: 6, Visits: 18
hi
I want Compare two Table data and insert changed field to the third table
who can help me
thanks a lot
Post #1602340
Posted Tuesday, August 12, 2014 9:45 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 10:01 AM
Points: 3,844, Visits: 3,841
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 - by Jeff Moden
Post #1602354
Posted Tuesday, August 12, 2014 12:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:52 AM
Points: 6, Visits: 18
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]
Post #1602422
Posted Tuesday, August 12, 2014 11:39 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 1,986, Visits: 5,168
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


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];

Post #1602570
Posted Wednesday, August 13, 2014 4:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:23 AM
Points: 5,074, Visits: 11,852
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


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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1602637
Posted Sunday, August 17, 2014 5:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:52 AM
Points: 6, Visits: 18
hi
I try your code but this is not work!!!
no detect deferent row!
Post #1604177
Posted Sunday, August 17, 2014 6:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:52 AM
Points: 6, Visits: 18
I want do this in picture



Post #1604185
Posted Sunday, August 17, 2014 6:11 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 1,986, Visits: 5,168
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?
Post #1604186
Posted Sunday, August 17, 2014 10:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:52 AM
Points: 6, Visits: 18
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
Post #1604209
Posted Tuesday, August 19, 2014 9:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 6:25 AM
Points: 34, Visits: 157
You may try using Merge statement
Post #1605007
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse