Synchronization

cthornburg

Okay I love this product...OMG...but I have a question. I have some table mods to move from a development DB into a production DB...but I need to leave all the data intact. Will this tool only add the fields to the table??? I can't do a drop and create.

david connell

Hi there,
This SQL Compare will move the schema across, but keeping the existing data intact.
for example it will add a column to a table and not drop and recreate the table. Sometimes it is necessary to drop and recreate the tables. If this is the case SQL Compare will copy the data into a temporary table and then copy the data back into the newly constructed table.
Nb. SQL Compare will not copy data between your database schemas. If you want to do that check out SQL Data Compare.
Hope that helps.
David

Brian Donahue

Hi,

I'd just like to add one thing: be wary if you're in the habit of renaming columns. There is a possibility of data loss because SQL Compare will drop the column from the destination database and create a new one with the new name. It tries as best as it can to preserve the data, but it has no way of knowing yet when a column exists in the second database under a different name.

cthornburg

Thanks for the info...I will of course make a backup first...and then move my changes into production.

Thank you!!

mshiker

Brian, I'm not clear of the exact situation you are getting at in your post of Wed Jul 26, 2006 10:02 am.

What I'm not clear on is your statement of "but it has no way of knowing yet when a column exists in the second database under a different name". You stated that SQL Compare will drop the old column and create the new one. So, at that point SQL Compare does know that that column now exists. Why would there be an issue with moving the preserved data into that new column?

Thanks,
Marc

Brian Donahue

Suppose you change the name of a column, then run SQL Compare

CREATE TABLE x (
ID int,
data varchar(50) <-- Rename to newdata varchar(50) in db2
)

SQL Compare sees this:
Database1:
CREATE TABLE x(
ID int,
data varchar(50)
)
Database2:
CREATE TABLE x(
ID int,
newdata varchar(50)
)

SQL Compare generates these actions for db2:
ALTER TABLE x DROP COLUMN data
ALTER TABLE x ADD COLUMN newdata varchar(50)

How can SQL Compare know that data and newdata are the same column with a different name?

mshiker

Brian,

Thanks for the response. I thought I understood what you were saying but I went and tested it and, I'm sorry, but I still must be missing something. It appears that SQL Compare is able to keep the mapping from renaming the column in either database (db1 or db2).

I first tried renaming the column in db2 (from first_name to new_first_name) then did a SQL Compare. When I view the difference in the SQL Compare UI for that particular table I see the the old column name in db1 and the new column name in db2 highlighted in blue (indicating there was a change and not a drop on one side and an add on the other). In the generated script I can see where the data is inserted into the temp table with both column names in the correct position indicating the correct mapping:

INSERT INTO [dbo].[tmp_rg_xx_Contact]([contact_id], [prefix_id], [first_name], [middle_name], [last_name], [suffix], [preferred_name], [gender_id], [personality_profile_type_id], [ethnicity_id], [birth_date], [birth_month], [file_set_id], [company_id], [create_date], [create_id], [modify_date], [modify_id]) SELECT [contact_id], [prefix_id], [new_first_name], [middle_name], [last_name], [suffix], [preferred_name], [gender_id], [personality_profile_type_id], [ethnicity_id], [birth_date], [birth_month], [file_set_id], [company_id], [create_date], [create_id], [modify_date], [modify_id] FROM [dbo].[Contact]

I did not commit that change.

Then I tried renaming the column in db1 (from first_name to new_first_name) and did a SQL Compare. When I view the difference in the SQL Compare UI for that particular table I see the the new column name in db1 and the column name in db2 highlighted in blue (indicating there was a change and not a drop on one side and an add on the other). In the generated script I can see the sp_rename:

PRINT N'Altering [dbo].[Contact]'
GO
sp_rename N'[dbo].[Contact].[first_name]', N'new_first_name', 'COLUMN'

Are there situations where SQL Compare can do a successful renaming (mapping of old column name to the new name) and there are some more specific situations where I should be concerned?

Thansk again,
Marc