SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL mirgration
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

LarryC74
Yak Posting Veteran

94 Posts

Posted - 01/14/2013 :  14:31:10  Show Profile  Reply with Quote
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 - 01/14/2013 :  14:39:54  Show Profile  Reply with Quote
I've seen this http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx br / 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

USA
15681 Posts

Posted - 01/14/2013 :  14:50:35  Show Profile  Visit robvolk's Homepage  Reply with Quote
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 - 01/14/2013 :  14:53:51  Show Profile  Reply with Quote
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 - 01/14/2013 :  17:01:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/14/2013 :  17:12:58  Show Profile  Reply with Quote
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 - 01/14/2013 :  17:18:10  Show Profile  Reply with Quote
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 - 01/14/2013 :  17:25:16  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000