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 2008 Forums
 Transact-SQL (2008)
 SQL mirgration

Author  Topic 

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2013-01-14 : 14:31:10
Hey Everyone,
I'm part of team to move this massive database from a 2000 server to 2008R2 server. My part is to check the tables to make sure that "All" data is the "Exact" same. So they want me to run a query (preferrably a sproc) to check each column.

This will go to a test enviornment first where we'll manipulate some of the data to check the code.

What's the best approach to capture all the columns?



Everyday life brings me back to reality

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2013-01-14 : 14:39:54
I've seen this [url]http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx
[/url]but I'm not sure this will do what I need it to do.


Everyday life brings me back to reality
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-01-14 : 14:50:35
Can you simply back up and restore the database from SQL 2000 to 2008 R2? As long as the backup file is good and there are no consistency errors you'll guarantee all data is copied. This will involve downtime to perform the restore however.
Go to Top of Page

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2013-01-14 : 14:53:51
Thanks for the response Rob!

That's what we're going to do...taken everything down for a day and restoring it. it's a big project.

I don't feel like we'll have any issues by my boss is very detailed and wants to and will check the tables. If I know her, she'll put in some dummy records to see if I can find them.

Everyday life brings me back to reality
Go to Top of Page

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2013-01-14 : 17:01:46
Hey So I put together a small test table(s).

The question here is, how do I get "Pete" to show by himself, if the change in the value in the columns is unknown?



Create Table TestTable
(RegID int,
FirstName varchar(10),
Lastname varchar(10)
)

Insert Into TestTable Values (1, 'Larry', 'Cook')
Insert Into TestTable Values(2, 'John' , 'Adams')
Insert Into TestTable Values(3, 'Hariett','Conner')

Select *
From TestTable

Select *
Into TestTable_1
From TestTable

Insert Into TestTable Values(4, 'Paul','Sorbet')

Select *
From TestTable_1

Select Distinct t2.*
From TestTable t1
Inner Join
TestTable_1 t2 ON t2.RegID = t1.RegID

Select Distinct t2.*
From TestTable t1
Left Join
TestTable_1 t2 ON t2.RegID = t1.RegID

Select Distinct t2.*
From TestTable t1
Right Join
TestTable_1 t2 ON t2.RegID = t1.RegID

Select Distinct t2.*
From TestTable t1
,
TestTable_1 t2
WHERE
t2.RegID <> t1.RegID
OR t2.FirstName <> t1.FirstName
OR t2.LastName <> t1.LastName

Update TestTable_1
Set FirstName = 'Pete'
WHERE RegID = 4



Everyday life brings me back to reality
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-14 : 17:12:58
I think the EXCEPT operator may be helpful here
SELECT RegID,FirstName,LastName
FROM TestTable
EXCEPT
SELECT RegID,FirstName,LastName
FROM TestTable_1

will give you everything in TestTable that isn't in TestTable_1. Exchanging the table names in the above will give you everything in
TestTable_1 that isn't in TestTable

Jim


Everyday I learn something that somebody else already knew
Go to Top of Page

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2013-01-14 : 17:18:10
I think I got it...

This worked

Select t2.*
From TestTable t1
Join TestTable_1 t2 On t2.RegId = t1.RegId

WHERE
t1.RegId <> t2.RegId
OR t1.FirstName <> t2.FirstName
OR t1.LastName <> t2.LastName



So now the questions is how do I turn that in a Stored Procdure? Some tables have about 30 columns each and will be on completely different servers.

Everyday life brings me back to reality
Go to Top of Page

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2013-01-14 : 17:25:16
Thanks Jim! I didn't see that until after I posted my last comment.

Everyday life brings me back to reality
Go to Top of Page
   

- Advertisement -