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.
| Author |
Topic |
|
rrb
SQLTeam Poet Laureate
1479 Posts |
 Posted - 2003-11-26 : 01:15:38
|
hi my loveliesOK here's the deal. I'm about to roll over to a "new and improved" version of the database. I've dropped a lot of unecessary tables and improved the views, sps, functions etc. I've also standardised types for fields which are commonly used and altered all the tables from nvarchar to varchar as they didn't need to use nvarchar in any case.Apart from that, the remaining tables are identical.What I want to do is to copy all the data from the live db over to the new db, including setting the identity the same etc etc, and email if there are any problems.I was going to use DTS to do this, thinking that I'd create the package to do this for one table and then "hack it" to make it run for all 74 tables. But the VB code it generated did not appear to lend itself to manipulation.So then I wrote a script to generate the dynamic SQL for each table - which produces the following strings for each table:SET IDENTITY_INSERT DB2.dbo.Tablename ONtruncate table DB2.dbo.Tablename DBCC CHECKIDENT( 'DB2.dbo.Tablename , RESEED, 1)insert into DB2.dbo.Tablename (columns...) select columns... from DB1.dbo.Tablenameif (select count(*) from DB1.Tablename) <> (select count(*) from DB2.Tablename) exec spSendMail bla bla bla This worked well for half the tables. The other half was no good because the insert SQL (in red) would need to be more than 4000 chars long, and since sp_executesql wants an nvarchar, it looks like I'm screwed.Any suggestions? Should I manually do each package for all the tables, am I approaching the whole problem from the wrong approach anyway, or am I just an idiot with nothing better to do?Appreciate the usual clever, humorous or poetic responses! You're all beautiful...too beautiful. PS (I think this is only my second ever post in the dark and mysterious world of the "Admin" forum!!)--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-11-26 : 03:02:55
|
| I've not done anything quite so massive before, you you might want to check out SQL Data Compare by Red Gate. I'm a big fan of their SQL compare tool, but I've not tried the data compare one. They have a free trial, so it'd not hurt to try it out. I'm not sure how it's going to handle dissimilar databases, but you said for the most part things are the same.Please note, I do not work for red gate nor do I get any kickbacks.http://www.red-gate.com/SQL_Data_Compare.htmMichael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-11-26 : 17:08:12
|
| Thanks Mike, I'll check it out!--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
The Enigma
Posting Yak Master
179 Posts |
Posted - 2003-11-26 : 17:31:41
|
Any reason you are not using exec(ute) ???From the Holy Book (SQL Server Books Online) :quote:
Using EXECUTE with a Character StringUse the string concatenation operator (+) to create large strings for dynamic execution. Each string expression can be a mixture of Unicode and non-Unicode data types.Although each [N] 'tsql_string' or @string_variable must be less than 8,000 bytes, the concatenation is performed logically in the SQL Server parser and never materializes in memory. For example, this statement never produces the expected 16,000 concatenated character string:EXEC('name_of_8000_char_string' + 'another_name_of_8000_char_string')Statement(s) inside the EXECUTE statement are not compiled until the EXECUTE statement is executed.
Attitude is everything{The Enigma} |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-11-26 : 17:38:18
|
| CHAMPION!! BEWDIFUL!!Ta--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-11-26 : 17:40:35
|
| PS - yes the reason is "IGNORANCE" - (which I was told was bliss! They lied to me!!!)Cheers--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
The Enigma
Posting Yak Master
179 Posts |
Posted - 2003-11-26 : 17:58:42
|
quote:
Originally posted by rrb
PS - yes the reason is "IGNORANCE" - (which I was told was bliss! They lied to me!!!)Cheers--I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Morpheus in the Matrix :quote:
"Most of these people are not ready to be unplugged."
Are you ???
Attitude is everything{The Enigma} |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-11-26 : 19:17:15
|
| hmmmm...strong feelings of love for the plug at this stage...maybe just a little longer - what was it - "I know this steak isn't real, but...mmmm" or something like that.Cheers and thanks--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
|
|
|
|
copying data between DBs