Compare two tables: Which fields are different?

Question

Compare two tables: Which fields are different?

Viewing 12 posts - 16 through 26 (of 26 total)

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

mikeg13 SSCommitted Points: 1851 More actions · Answer 1

It looks like I forgot to add the dynamic SQL that puts all these pieces together.

-- Piece together everything into a single dynamic INSERT statement

SET @SQL = 'INSERT INTO Stage.DATA_CHANGES (Tablename, Action, Key_Fields, Key_Values, Changes) SELECT * FROM (SELECT ''' +

@p_changes_tablename + ''' tname, ''UPDATE'' action, ''' + @Key_Fields + ''' kfields, ' + @Key_Values + ' kvalues, ' +

@Changes + ' Changes ' + @From + ' WHERE ' + @Join_Keys +

') alias1 WHERE len(replace(changes,''~'','''')) > 0'

-- Run the dynamic SQL to populate the staging table

EXECUTE (@SQL )

As for the "Tally" table, that is just a table of numbers. The idea for this can be found by searching this forum for "tally table" or try [/url]

Hopefully this helps. I would be interested to know if you, or anyone else, gets any use out of this. Good luck.

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

Kudos on sticking with a thread that is 2 years old!

John Rowan

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

mikeg13 SSCommitted Points: 1851 More actions · Answer 3

Kudos to whomever allowed me to subscribe to a topic and get an email. 🙂

mohammad.kamranpk Mr or Mrs. 500 Points: 507 More actions · Answer 4

I have two tables which contain the same records but those records are entered by different users and i want to calculate similairty between same records(by every comapring every single field) entered by different users, and if a field in one table is similar to corresponding field in the second table then i want to store 1 in a column in third table (Table_C).

For Example I have two tables Table_A and Table_B as below:

Table_A

----------------------

RecordID StudentID Dept BookID

1 123 CS 456

2 123 CS 345

3 223 TE 190

Table_B

----------------------

RecordID StudentID Dept BookID

1 123 CS 456

2 223 TE 345

3 223 TE 190

and i have another table Table_C in which is store the similarity between the similar fields in Table_A and Table_B. The Sturcutre of the table is as follows:

Table_C

----------------------

Sim_RecordID Sim_StudentID Sim_Dept SimBookID

1 1 1 1

1 0 0 1

1 1 1 1

Note: I want to comapre only those records in Table_A and Table_B where RecordID and StudentID are same in both tables. i.e. i want a query or simple stored procedure to compare all columns of Table_A with corresponding columns of Table_B where Table_A.RecorID = Table_B.RecordID and Table_A.StudentID = Table_B.StudentID and Store 1 if the fields are similar otherwise store 0 in Table_C in the corresponding field. I hope i have clearly defined my probelm. any help would be appreciated.

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

Mohammed,

You posted your question on a thread that is 2 years old. I suggest that you open a new thread with your question.

John Rowan

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

mikeg13 SSCommitted Points: 1851 More actions · Answer 6

I would agree about creating a new thread. However, if your problem is as simple as your question, then the following SQL, or something like it, should suffice to get your answer. Note that the SQL is based on your requirement that RecordID and StudentID must match, whereas your example data seems to rely simply on RecordID matching.

insert into Table_C (Sim_RecordID, Sim_StudentID, Sim_Dept, Sim_BookID)

select 1, 1

, case when Table_A.Dept = Table_B.Dept then 1 else 0 end

, case when Table_A.BookID = Table_B.BookID then 1 else 0 end

from Table_A

join Table_B

on Table_A.RecordID = Table_B.RecordID and Table_A.StudentID = Table_B.StudentID

If your problem requires a more dynamic solution for a table with an unknown structure, then perhaps you could use the technique mentioned in this thread to create an audit of what changed and produce your Table_C from that. Good luck.

mohammad.kamranpk Mr or Mrs. 500 Points: 507 More actions · Answer 7

assalam o alaikum

now i have tried to explain my problem clearly in a seperate thread. please if you can spare some time to help me out. the link of the thread is

http://www.sqlservercentral.com/Forums/Topic945934-149-1.aspx

Regards,

Kamran

mohammad.kamranpk Mr or Mrs. 500 Points: 507 More actions · Answer 8

assalam o alaikum

now i have tried to explain my problem clearly in a seperate thread. please if you can spare some time to help me out. the link of the thread is

http://www.sqlservercentral.com/Forums/Topic945934-149-1.aspx

Regards,

Kamran

uprajkumar Grasshopper Points: 11 More actions · Answer 9

I Have two tables, first table as to be cross checked the feilds like EMP name & Emp no into second table & include the same in first table.

Regards

Raj

mikeg13 SSCommitted Points: 1851 More actions · Answer 10

I am not sure I understand. Can you give simple example of what tables you have and what you want?

manub22 Hall of Fame Points: 3163 More actions · Answer 11

Check the following blog post: http://sqlwithmanoj.wordpress.com/2011/05/16/database-schema-diff/[/url]

Here I've created a script to list out differences in schema of two different databases.

You can add filter WHERE clause for tables, or tweak the script according to your needs.

~manoj | LinkedIn | Facebook | YouTube
Blog: SQLwithManoj.com

mikeg13 SSCommitted Points: 1851 More actions · Answer 12

The SQL mentioned in this post many years ago refers to a way to compare the DATA in two different tables. It would tell you that Field7 has changed and give the old and new values.