[MS SQL 2008] Merging the difference of two SQL database (tables, procedures, etc) ???

I have two databases similar to each other, Base A and Base B.

Base A is clean, no data inside.
Base B however, is highly populated, but has more procedures, triggers and views than Base A.

What I need to do is to import Base B structural differences into Base A, the thing is, I can't migrate the data inside Base B, only the structural content (tables, new columns inside existing tables, procedures, triggers, etc).

Again, I just need to get the structural differences from Base B and insert them on Base A.

How can I do this?

Thanks in advance.
John86aAsked:
Who is Participating?
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
There are many tools to compare sql server database and sychronize the change.
Top most tools that I know
1. Apex SQL Comparison Studio
2. RedGate SQL Compare

It is very easy - You can see what object are in TableA and not in TableB, viceversa and what are the objects that have difference. Then select the objects that you want to sync and click on 'Sync' button or similar button and take care to select proper destination database. If you select wrongly, it will cause lose of your objects

Raj
0
 
Asim NazirCommented:
hi,

no need to use third party tools. Simply use Visual Studio compare schema option.
You can use VS2oo8 or 2o1o

Asim
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
John86aAuthor Commented:
Red Gate SQL Compare almost worked, until it returned an error while in the merging process.
Apex SQL Comparison Studio return an error as well, but at the very beginning of the merge.

Visual Studio? Care to explain?
0
 
derekkrommCommented:
What errors did you get in SQL Compare? If both utilities are reporting errors, there's probably an underlying cause
0
 
John86aAuthor Commented:
This is the error for Red Gate:
The following error message was returned from the SQL Server:

[515] Cannot insert the value NULL into column 'fund_tec', table 'Expbase.dbo.CTFPlanoGer'; column does not allow nulls. UPDATE fails.

The following SQL command caused the error:

ALTER TABLE [dbo].[XPCGeneralPl] ALTER COLUMN [interg_ctb_titulo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [dbo].[XPCGeneralPl] ALTER COLUMN [interg_ctb_pgto] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [dbo].[XPCGeneralPl] ALTER COLUMN [fluxo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [dbo].[XPCGeneralPl] ALTER COLUMN [tp_atividade] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [dbo].[XPCGeneralPl] ALTER COLUMN [despesa_antec] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

This is the error for Apex:
 Apex Error
0
 
derekkrommCommented:
So judging from the RedGate errors, it looks like you're attempting to change a column definition from NULL to NOT NULL, but there are pre-existing NULLS already present in the table.

Can you verify by doing this:

select * from Expbase.dbo.CTFPlanoGer where fund_tec IS NULL

If there are NULLs, you'll need to update them to a non-NULL value, or you won't be able to sync that column definition.
0
 
John86aAuthor Commented:

(38 row(s) affected)

That command returned a bunch of values. Column fund_tec was all 'NULL'.
0
 
John86aAuthor Commented:
I thought it would be an automated process where I wouldn't have to fill data in. I just wanted to sync the structure.. is it possible at all?
0
 
Anthony PerkinsCommented:
>>I just wanted to sync the structure.. is it possible at all?<<
SQL Compare cannot guess what value to add to those columns that do not allow NULLs, nor would it be responsible for them to do it.
0
 
John86aAuthor Commented:
Oh, I get it now.. how would I make them all allow null?
0
 
derekkrommConnect With a Mentor Commented:
There should be an option to just generate the script that would sync the databases.

From there, you'd have to change any new columns that are NOT NULL to NULL before executing and any existing columns that change to NOT NULL back to NULL
0
 
John86aAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.