The databases are on different machines but for the purpose of the merge we could move them to the same one. This would also make sense in terms of performance.

Unfortunately...

I use a similar strategy but instead of having a nested select, I do an INNER join between the two databases

insert into destinationDB.destinationTable (ID, s...)

select destinationParentTable.ID, s...

from ...

I think i prefer the insert into select option since its basically a the same as MERGE but with better performance.

There are two reasons while i would like to avoid...

I agree..that seems to be my strategy also. And I also have GUID so it sounds like its going to be very similar. Having said that, i need to worry...

I have access and relatively good knowledge of both databases. Unfortunately this is not simply a task of appending the data as the system allows users to maintain lookups, categories...

The reason for the merge is because one client acquired the other and they don't want to maintain separate systems.

I know it's a grand task because the db schema...

Yes its true that the -1 solution would do for this thread although I am a bit hesitant to use it in case the application or reports on this database...

Hi Phil, thanks for the -1 tip, that would work also..Do you know of any tool that would do this instead of scripting this manually? I looked into Redgate and...

Thanks Eric. It seems you did do this before 🙂 I like the ID padding tip you gave me so i can match records that way instead of another field....

I would think different schema is more difficult. Same schema merges would be common in merging departmental databases or even company mergers that have been using the same software. Or...

The reason why I am asking for such a tool is because I m sure combining client databases of the same schema must be somewhat of a common task when...

yeah i know its going to be quite a task...the PKs of the inserted records will get a new ID which is what makes it tricky when those new records...

Yes

ok thanks for your help )

There's enough space on external so i m doing all. But why is this a temporary solution, is there a problem with leaving it on external and keep it plugged...