Solved

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

Posted on 2011-02-25
13
459 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:John86a
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 334 total points
ID: 34985795
0
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 166 total points
ID: 34985842
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
 
LVL 10

Expert Comment

by:Asim Nazir
ID: 34986210
hi,

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

Asim
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:John86a
ID: 34988692
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
 
LVL 15

Expert Comment

by:derekkromm
ID: 34988860
What errors did you get in SQL Compare? If both utilities are reporting errors, there's probably an underlying cause
0
 

Author Comment

by:John86a
ID: 34989832
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
 
LVL 15

Expert Comment

by:derekkromm
ID: 34989836
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
 

Author Comment

by:John86a
ID: 34989861

(38 row(s) affected)

That command returned a bunch of values. Column fund_tec was all 'NULL'.
0
 

Author Comment

by:John86a
ID: 34989864
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34989907
>>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
 

Author Comment

by:John86a
ID: 34989940
Oh, I get it now.. how would I make them all allow null?
0
 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 334 total points
ID: 34989967
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
 

Author Closing Comment

by:John86a
ID: 34993847
Thanks.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question