COMPARING TWO TABLES...need help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

COMPARING TWO TABLES...need help

Author

Topic

pat.maat4u
Starting Member

3 Posts

Posted - 2008-01-31 : 12:33:54

I saw the previous post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23054 by jeff that describes how two tables are compared.
In my scenario i have two tables students and students2 and columns for each tables are as follows:
Students: StudentsId,Fname,Lname,Street,Zip,SSN
Students2: StudID,Firstname,Lastname,Street,Zipcode,SSN

Now i need to compare these two tables.

Jeff's code goes as follows:

CREATE PROCEDURE CompareTables(@table1 varchar(100), @table2 Varchar(100), @T1ColumnList varchar(1000), @T2ColumnList varchar(1000) = '')AS-- Table1, Table2 are the tables or views to compare.-- T1ColumnList is the list of columns to compare, from table1.-- Just list them comma-separated, like in a GROUP BY clause.-- If T2ColumnList is not specified, it is assumed to be the same-- as T1ColumnList. Otherwise, list the columns of Table2 in-- the same order as the columns in table1 that you wish to compare.---- The result is all records from either table that do NOT match-- the other table, along with which table the record is from.declare @SQL varchar(8000);IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnListset @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList + ' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' + @t2ColumnList + ' FROM ' + @Table2set @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList + ' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList + ' HAVING COUNT(*) = 1'exec ( @SQL)

Can any one help me putting the the tablenames and values(eg.@t1columnlist, @t2columnlist) what exactly do i need to put in my case that will help me run this procedure successfully and also the EXECUTE command with parameters that will help me executing it.
Thankyou,
Pat

hi

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-31 : 16:26:54

moved from script library

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

pat.maat4u
Starting Member

3 Posts

Posted - 2008-02-01 : 00:43:57

Can any one help me with this.
I really need it urgent.
Thanks

hi

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2008-02-01 : 04:39:09

Jeff's comments give you all the instructions you need! What have you tried so far?
After running the DDL to create the procedure, you should execute the following:

EXEC CompareTables
        @Table1='Students', 
        @Table2='Students2',
        @T1ColumnList='StudentsId,Fname,Lname,Street,Zipcode,SSN'

Mark

pat.maat4u
Starting Member

3 Posts

Posted - 2008-02-02 : 18:02:11

Thanks mark.

hi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources