Comparing Source Table Old to New

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

General SQL Server Forums

New to SQL Server Programming

Comparing Source Table Old to New

Author

Topic

cvdm
Starting Member

4 Posts

Posted - 2011-07-25 : 09:08:02

Good Day

First time posting so i am hoping someone would be able to assist me with the following.

Table A compair against Table B

TableA has column "ID_Num_Soccer" as int,
"@Name1" as int,
"@Name2" as varchar(20),
"@Name3" as decimal(18,0),
"@Name50" as varchar(50)

TableB has column "ID_Num_Ball" as varchar(25),
"@Name4" as int,
"@Name5" as datetime,
"@Name6" as int,
"@Name60" as varchar(30)

I have to currently run for example

select A.ID_Num_Soccer, A.Name1, A.Name2, A.Name3, A.Name50, B.ID_Num_Ball, B.name4, B.Name5, B.Name6, B.Name60 from TableA A inner join TableB B on cast(B.ID_Num_Ball as bigint) = A.ID_Num_Soccer

[If it fails due to casting to bigint from varchar that would fail the test right away.]

And then Manualy see that
Name2 = Name5 where eg.
Name2 = "20110705" and Name5 = "2011-07-05 00:00:00"

And @name1 = @name6 eg.
Name1 = 500 and Name6 = 500

Name50 = Name60 eg.
Name50 = "Field" and name60 = "field"

But Name3 does not match to any value from TableB
Name3 = 123456 and Name4 = 123456789, Name60 = "field", Name5 = "2011-07-05 00:00:00", Name6 = 500

I Would like to script something to see a Table List where it would check if A.Name1,Name2,Name3,Name50 = B.*, and where the value is the same, it would set the value in a temp table to the Column Name of B where it matched.

eg.

TableC (Temp table) would contain according to the above example

ID | A.Name1 | A.Name2 | A.Name3 | A.Name50
95 | B.Name6 | 0 | 0 | B.Name60
96 | B.Name6 | 0 | 0 | B.Name60
97 | B.Name6 | 0 | 0 | B.Name60
98 | B.Name6 | 0 | B.Name4 | B.Name60
99 | 0 | 0 | 0 | 0
100| B.Name6, B.Name4 | 0 | 0 | B.Name60

For all Matching Records based on the ID

Would it be possible to script something like this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-25 : 11:06:23

sounds like what you need is a set of case when statements. why do you want to store the column names in this way?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cvdm
Starting Member

4 Posts

Posted - 2011-07-26 : 04:13:42

Dont think it would be effective with case statements, due to the constant changing table and column names. Also old and new source tables would/could have up to 100 columns taking into account matching source to 100 new source would end up with a fairly large number of case statements

Storing the column names would assist me in the old to new source mapping.

Its like you have a source table and some new source provider says "here is our data why not see if it could work for you" kinda situation.

Same product/s and identifier just different column names and table names.

Been thinking by myself how to do it and had someone assist in scripting this but just wondering if there isnt allready a application able to do this like a SSIS Data Profiling Task (or something like it) where you could specify the "from" table as a source and the compare to table as the current feed "destination" and let it show you what is going with the data after joining the 2.

Thanks for reply.

cvdm
Starting Member

4 Posts

Posted - 2011-07-26 : 04:25:17

Case where could work if it could accept running it 10000 times for say 2 records

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-26 : 12:13:27

Been thinking by myself how to do it and had someone assist in scripting this but just wondering if there isnt allready a application able to do this like a SSIS Data Profiling Task (or something like it) where you could specify the "from" table as a source and the compare to table as the current feed "destination" and let it show you what is going with the data after joining the 2.

sounds like a merge join task then

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cvdm
Starting Member

4 Posts

Posted - 2011-07-27 : 09:41:20

I did the following to compare 2 unrelated tables, using adventureworks db as an example

1)First create a staging table, populating it with table name and column from sys

USE [AdventureWorksDW]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempTable]') AND type = 'U')
DROP TABLE [dbo].[TempTable]
GO

USE [AdventureWorksDW]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TempTable](
[Table] [nvarchar](128) NULL,
[Column] [nvarchar](128) NULL,
[Value] [nvarchar](255) NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
[KeyColumn] [nvarchar](128) NULL
) ON [PRIMARY]

GO

declare @NewTableIDColumn varchar(30),
@OldTableIDColumn varchar(30),
@NewTable varchar (30),
@OldTable varchar(30)

-- change these to compare different Tables
set @NewTableIDColumn = 'Customerkey'
set @OldTableIDColumn = 'Customerkey'
set @NewTable = 'DimCustomer'
set @OldTable = 'FactInternetSales'

insert into TempTable ([Table],[Column])
select t.name[Table], c.name[Column]

from sys.sysobjects t
inner join sys.syscolumns c on c.id = t.id
where t.name = @NewTable or t.name = @OldTable

update TempTable
set KeyColumn = @NewTableIDColumn where [Table] = @NewTable

update TempTable
set KeyColumn = @OldTableIDColumn where [Table] = @OldTable

2) Then retrieve the data based on a unique id key i can change and update the table using a cursor

DECLARE db_cursorC CURSOR FOR
SELECT [Table]
,[Column]
,[KeyColumn]

FROM [AdventureWorksDW].[dbo].[TempTable]

Declare @Table nvarchar(128) ,
@column nvarchar(128),
@KeyColumn nvarchar(128),
@cmd nvarchar(max),
@ID varchar(20)

-- change this to compare different customers to confirm findings
set @ID = '11000'

OPEN db_cursorC
FETCH NEXT FROM db_cursorC INTO @Table,@column,@KeyColumn
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd = 'update temptable set [value] = (' +
'select top 1 [' + @column + '] from [' + @Table + '] where [' + @keycolumn + '] = ' + @ID + ') '
+ 'where [table] = ''' + @Table + ''' and [column] = ''' + @column + ''''
print @cmd
exec (@cmd);
FETCH NEXT FROM db_cursorC INTO @Table,@column,@KeyColumn
END
CLOSE db_cursorC
DEALLOCATE db_cursorC

3) View retrieved Data to compare matching rows from old source to new source

SELECT t1.[Table]
,t1.[Column]
,t1.[Value]
,t1.[ID]
,t1.[KeyColumn]
,t2.[Table]
,t2.[Column]
,t2.[Value]
,t2.[ID]
,t2.[KeyColumn]
FROM [AdventureWorksDW].[dbo].[TempTable] t1
left join [AdventureWorksDW].[dbo].[TempTable] t2 on t2.value = t1.value where T1.id != T2.id
and t1.[table] != T2.[Table]

If you copy paste the above and have the AdventureWorksDW testing db installed you could see the results.
you can change it to any 2 tables you wish just aslong as they can be linked via keys

Will be testing this soon so will change where needed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources