Updating the database structure

Question

Post reply

Updating the database structure

zizo

SSC Journeyman

Points: 91
More actions
September 29, 2003 at 4:22 am

#81061

We have a large website of around 100,000 users. The site is in english.
We are trying to 1 more language to the site, ie: users can browse the website either in English or Arabic.
So, in our development enviroment (Our local mirror of the database which we work on), we added
extra fields in each table carrying the arabic data.
The problem now is how to export this new database structure to the host so that it can be available online
with no data loss keeping in mind that the 2 databases (Our local development database & the other online one) are inconsistent.
Do we have to re-do all those changes manually once again @ the online database, or is there a way to preserve the
online data & @ the same time have the new tables structure.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

Just use the same scripts as you created to alter the tables on the development server.

--Jonathan

zizo SSC Journeyman Points: 91 More actions · Answer 2

That will fine, but after creating the new structure how can I ensure data is the same on both sides? How can I export the new arabic data in the arabic fields ?

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

quote:
That will fine, but after creating the new structure how can I ensure data is the same on both sides? How can I export the new arabic data in the arabic fields ?

Something like this, perhaps, if the servers are linked:

UPDATE p

SET ArabCol1 = d.ArabCol1,

ArabCol2 = d.ArabCol2,

...

FROM ProdServer.DBName.dbo.Table1 p JOIN DevServer.DBName.dbo.Table1 d ON p.PKCol = d.PKCol

--Jonathan

EdH334 SSCrazy Points: 2857 More actions · Answer 4

You can coax SQL into scripting table updates. Try running something like:

SELECT 'UPDATE Messages SET ArabicMsg = ''' + REPLACE([ArabicMsg],'''', '''''') + ''' WHERE [MsgId]=' + CONVERT(varchar(30), [MsgId]) AS UpdateSQL FROM Messages

On your development systems table in Query Analyser with the results in text. This should produce a valid UPDATE command for each row in the Messages table. (The REPLACE function converts single quotes to two single quotes, just incase there are any in the text.)