compare tables - SQL Server Forums

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)

compare tables

Author

Topic

jdub_92
Starting Member

13 Posts

Posted - 2008-10-20 : 13:29:21

Hi,

Ive tried to use this script from a post and i getting errors, could you help me with this,.

My table1 is Vendors with columnnames = vendorid(char), companyname(varchar), repfname(varchar), replname(varchar), referredby(char)

My table2 is MyTable with columnnames = Col1(int), Col2(varchar), Col3(datetime)

USE Master
GO

-- Compare the data in two tables for a subset of columns
--
SELECT Max(vendors) as TableName, vendorid,referredby
FROM (
SELECT 'vendorsTest' AS TableName, vendorid,referredby
FROM vendors
UNION ALL
SELECT 'MyTableTest' As TableName, Col1,Col2
FROM Mytable
) A
GROUP BY vendorid,referredby
HAVING COUNT(*) = 1
ORDER BY vendorid,referredby

GO
-- Compare the data in two tables for all columns
-- except for XML data Types
--
DECLARE @SQL varchar(8000)
DECLARE @vendors varchar(8000)
DECLARE @MyTable varchar(8000)
SET @vendors = 'vendors'
SET @MyTable = 'MyTable'

DECLARE @Cols varchar(8000)
SET @Cols = ''
SELECT @Cols = @Cols + '[' + Column_Name + '],'
FROM INFORMATION_SCHEMA.Columns
WHERE Table_Name = @vendors and DATA_TYPE <> 'xml'
SET @Cols=left(@cols,LEN(@cols)-1) -- Remove trailing ,

SET @SQL = +
'SELECT Max(vendors) as TableName, ' + @Cols +
' FROM ( ' +
' SELECT ''' + @Vendors + ''' AS TableName, ' + @Cols +
' FROM ' + @Vendors +
' UNION ALL ' +
' SELECT ''' + @MyTable + ''' As TableName, ' + @Cols +
' FROM ' + @MyTable +
' ) A ' +
' GROUP BY ' + @Cols +
' HAVING COUNT(*) = 1 ' +
' ORDER BY ' + @Cols

-- SELECT @SQL
EXEC ( @SQL)

thanks,

jwill

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 13:38:41

quote:
Originally posted by jdub_92

Hi,

Ive tried to use this script from a post and i getting errors, could you help me with this,.

My table1 is Vendors with columnnames = vendorid(char), companyname(varchar), repfname(varchar), replname(varchar), referredby(char)

My table2 is MyTable with columnnames = Col1(int), Col2(varchar), Col3(datetime)

USE Master
GO

-- Compare the data in two tables for a subset of columns
--
SELECT Max(~~vendors~~TableName) as TableName, vendorid,referredby
FROM (
SELECT 'vendorsTest' AS TableName, vendorid,referredby
FROM vendors
UNION ALL
SELECT 'MyTableTest' As TableName, Col1,Col2
FROM Mytable
) A
GROUP BY vendorid,referredby
HAVING COUNT(*) = 1
ORDER BY vendorid,referredby

GO
-- Compare the data in two tables for all columns
-- except for XML data Types
--
DECLARE @SQL varchar(8000)
DECLARE @vendors varchar(8000)
DECLARE @MyTable varchar(8000)
SET @vendors = 'vendors'
SET @MyTable = 'MyTable'

DECLARE @Cols varchar(8000)
SET @Cols = ''
SELECT @Cols = @Cols + '[' + Column_Name + '],'
FROM INFORMATION_SCHEMA.Columns
WHERE Table_Name = @vendors and DATA_TYPE <> 'xml'
SET @Cols=left(@cols,LEN(@cols)-1) -- Remove trailing ,

SET @SQL = +
'SELECT Max(~~vendors~~TableName) as TableName, ' + @Cols +
' FROM ( ' +
' SELECT ''' + @Vendors + ''' AS TableName, ' + @Cols +
' FROM ' + @Vendors +
' UNION ALL ' +
' SELECT ''' + @MyTable + ''' As TableName, ' + @Cols +
' FROM ' + @MyTable +
' ) A ' +
' GROUP BY ' + @Cols +
' HAVING COUNT(*) = 1 ' +
' ORDER BY ' + @Cols

-- SELECT @SQL
EXEC ( @SQL)

thanks,

jwill

change like above and try

jdub_92
Starting Member

13 Posts

Posted - 2008-10-20 : 13:49:04

thanks for the reply,. i tried it but the errors are

Msg 207, Level 16, State 1, Line 4
Invalid column name 'TableName'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'vendorid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'companyname'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'repfname'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'replname'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'referredby'.

jwill

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 13:58:52

replace EXEC(@SQL) with PRINT(@SQL) and post the result here

jdub_92
Starting Member

13 Posts

Posted - 2008-10-20 : 14:01:11

here is the result:

Msg 207, Level 16, State 1, Line 4
Invalid column name 'TableName'.
SELECT Max(TableName) as TableName, [vendorid],[companyname],[repfname],[replname],[referredby] FROM ( SELECT 'vendors' AS TableName, [vendorid],[companyname],[repfname],[replname],[referredby] FROM vendors UNION ALL SELECT 'MyTable' As TableName, [vendorid],[companyname],[repfname],[replname],[referredby] FROM MyTable ) A GROUP BY [vendorid],[companyname],[repfname],[replname],[referredby] HAVING COUNT(*) = 1 ORDER BY [vendorid],[companyname],[repfname],[replname],[referredby]

jwill

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 14:06:12

do you have columns [vendorid],[companyname],[repfname],[replname],[referredby] in both tables?

jdub_92
Starting Member

13 Posts

Posted - 2008-10-20 : 14:08:53

nope, thats only in the vendors table

My table1 is Vendors with columnnames = vendorid(char), companyname(varchar), repfname(varchar), replname(varchar), referredby(char)

My table2 is MyTable with columnnames = Col1(int), Col2(varchar), Col3(datetime)

jwill

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 14:12:17

quote:
Originally posted by jdub_92

nope, thats only in the vendors table

My table1 is Vendors with columnnames = vendorid(char), companyname(varchar), repfname(varchar), replname(varchar), referredby(char)

My table2 is MyTable with columnnames = Col1(int), Col2(varchar), Col3(datetime)

jwill

then whats the purpose of below select batch after UNION ALL? this is why its throwing error as you dont have columns in it. so remove it if it doesnt have columns

SELECT 'MyTable' As TableName, [vendorid],[companyname],[repfname],[replname],[referredby] FROM MyTable

jdub_92
Starting Member

13 Posts

Posted - 2008-10-20 : 14:21:45

where in this line i'll remove?,and where is the comparing of the two table? and also it says that invalid column name 'TableName',. i'm not known this much,. thanks for the response,.

SET @SQL = +
'SELECT Max(TableName) as TableName, ' + @Cols +
' FROM ( ' +
' SELECT ''' + @Vendors + ''' AS TableName, ' + @Cols +
' FROM ' + @Vendors +
' UNION ALL ' +
' SELECT ''' + @MyTable + ''' As TableName, ' + @Cols +
' FROM ' + @MyTable +
' ) A ' +
' GROUP BY ' + @Cols +
' HAVING COUNT(*) = 1 ' +
' ORDER BY ' + @Cols

jwill

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 14:47:26

quote:
Originally posted by jdub_92

where in this line i'll remove?,and where is the comparing of the two table? and also it says that invalid column name 'TableName',. i'm not known this much,. thanks for the response,.

SET @SQL = +
'SELECT Max(TableName) as TableName, ' + @Cols +
' FROM ( ' +
' SELECT ''' + @Vendors + ''' AS TableName, ' + @Cols +
' FROM ' + @Vendors +
' UNION ALL ' +
' SELECT ''' + @MyTable + ''' As TableName, ' + @Cols +
' FROM ' + @MyTable +' ) A ' +
' GROUP BY ' + @Cols +
' HAVING COUNT(*) = 1 ' +
' ORDER BY ' + @Cols

jwill

remove the striked off portion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources