need help on how to approach a mege

[sparks]

My boss says ok here are 2 sql backups.
We think they have the same tables and columns.

merge them.

this is sqlserver 2008 r2

oh wait here is another database that I think has all the tables and
columns so delete all the data in it and then merge the other two
databases using the third and use the schema of the third one.

help
I don't have a clue how to do this.
any pointers on info I can find and read up on this or about how to do
it and how to track errors?

ps I know in a merge there are 3 ways to do a mergematched, not
matched and not matched by source (at least that is the 3 I know)
when I asked this he said I just want all the data from both of them.
ok merge matched.
Isn't that an append, at least it is in access.
but I don't know about his schema or even what is the identifying
variables in this 3rd database.
And I assume I don't care, let sql populate them. But I still need to
know an order of the tables to merge the data don't I ?


Wow maybe this is easy for some of you but I are confused.

[Erland Sommarskog]

Post by sparks
My boss says ok here are 2 sql backups.
We think they have the same tables and columns.
merge them.
this is sqlserver 2008 r2
oh wait here is another database that I think has all the tables and
columns so delete all the data in it and then merge the other two
databases using the third and use the schema of the third one.
help
I don't have a clue how to do this.
any pointers on info I can find and read up on this or about how to do
it and how to track errors?

It seems that you need to first identify your requirements. You have two
or three databases, that you want to "merge", whatever that means. You
think that the databases have the same tables and columns. Hm, so what
about verifying that first.

And then you need to define what you really mean with "merge".

[sparks]

Thanks for saying this.
I asked him what he wanted with this merge.
He said ok the A and B database should be the same tables and columns.
C is an older version of them and we want to use the schema in it for
the merged data.

I asked ok on this merge there are 3 different types of merges that I
know of.
Are you sure you want to do that or an insert.
you know like an append query in access.
(I have been doing access stuff like this for 15 years and even an
append needs the same variables or someone to tell me what variables
you are expecting.

at this time all he said was I don't care what you do but I want the
schema from database C.
ok the schema, are you talking about the key variables that and the
way they are linked to other tables.
he kinda looked puzzled...the schema I want that.
ok if you define what you are expecting to get.
is there a different structure in A B and C and C is what you want to
end up with?

"YES"

(I don't know if he has a clue what he is really wanting or how to say
where he wants to go)

After talking to the person that sent him the tables.
A was created 5 years ago and put into production, about 15,000
records and 13 tables.
B was created from a copy of A about 3 years ago and some changes were
made, but basically its the same as A with some variables changed,
added or deleted.
And then put into production at another site.

C was the final structure and was done about a year ago.
It was again put into production somewhere, not A or B.

He got a copy of all 3.

Deleted the data in C and wants to put the data from A and B into C.
The person said that C will be the FINAL with A and B being deleted
and replaced with C.

1) I will have to find out what the differences are between A,B and C.
tables, columns, and data type.

2) I would think I would just insert A into C and then B into C.
I was looking at the primary keys and one table called clientsID seems
to link to all other tables.

I will start on this table which only has 12 variables.
Since A and B are from 2 different sites I said well this is going to
be all duplicates of all the client ID's that were assigned.
at least they are not duplicated it seems that they used a different
format in each database. One is all numbers and the other used a
leading 3 text string a - and then a number.




On Tue, 20 Mar 2012 23:52:08 +0100, Erland Sommarskog

Post by Erland Sommarskog

Post by sparks
My boss says ok here are 2 sql backups.
We think they have the same tables and columns.
merge them.
this is sqlserver 2008 r2
oh wait here is another database that I think has all the tables and
columns so delete all the data in it and then merge the other two
databases using the third and use the schema of the third one.
help
I don't have a clue how to do this.
any pointers on info I can find and read up on this or about how to do
it and how to track errors?

It seems that you need to first identify your requirements. You have two
or three databases, that you want to "merge", whatever that means. You
think that the databases have the same tables and columns. Hm, so what
about verifying that first.
And then you need to define what you really mean with "merge".

[Erland Sommarskog]

Post by sparks
I asked ok on this merge there are 3 different types of merges that I
know of.

Well, this merge operation sounds a little more complicated that you
would do in a source-code control system.

Most likely there will be conflicts and a lot decisions to make. Good luck!

[sparks]

Yes it is way more complicated than I expected.

like the main table that has the pk and controls all the data.
one have 49 variables while the other has 51.
The main table in the new one has 6 variables.
the other variables are in 4 other tables AHHHHHH lol





On Wed, 21 Mar 2012 23:54:50 +0100, Erland Sommarskog

Post by Erland Sommarskog

Post by sparks
I asked ok on this merge there are 3 different types of merges that I
know of.

Well, this merge operation sounds a little more complicated that you
would do in a source-code control system.
Most likely there will be conflicts and a lot decisions to make. Good luck!

[Bob Barrows]

Oh! You're the one who refers to "columns" as "variables". Doing so caused
some major communication problems when you posted questions to the Access
group. Several people, including myself, tried to correct you about it, but
you never responded.

Why do you do that? Are you using a translation program that's doing it?

As for your question, will there be key violations when merging the data? If
so, you have a couple options:
1. Modify the key values from one of the source databases so the violations
do not occur. If the foreign keys are not set to cascade, then you will need
to make the same change to the foreign key values at the same time so as not
to orphan the child records.
2. Add a "source" column to the destination tables and include it in the key
definitions, then populate it with a "source" value to distinguish the
records being added from each source.

You will need further direction from your boss as to what to do with the
data in the columns in the source databases that do not exist in the
destination.
I echo what Erland said: good luck with this.

Post by sparks
Yes it is way more complicated than I expected.
like the main table that has the pk and controls all the data.
one have 49 variables while the other has 51.
The main table in the new one has 6 variables.
the other variables are in 4 other tables AHHHHHH lol
On Wed, 21 Mar 2012 23:54:50 +0100, Erland Sommarskog

Post by Erland Sommarskog

Post by sparks
I asked ok on this merge there are 3 different types of merges that
I know of.

Well, this merge operation sounds a little more complicated that you
would do in a source-code control system.
Most likely there will be conflicts and a lot decisions to make. Good luck!

[sparks]

I really want to appologise for saying variables.
Here everyone says variable in access databases.
You know in all of access work I only remember seeing column when
referring to a combo box lol


I have just been moved to the sql side after 15 years of access.
I am trying to say column from now on.

What really confused my boss and me was this sql 2008 database he
wants me to use it will all its references and things in place.
Delete all the data, and put the data from 2 other sites into it.
The databases are not the same structure.

let me say A,B and C

C is the one he wants me to remove all the data, and then some how put
A and B into it.

I went thru the tables in A and B and when they deleted about 30% of
the tables as useless I can merge them now.

what I have problems with is when you have references in C do I have
to delete all the references in the tables, insert the data and then
rebuild the references.

I want to ask is there a way to capture all the references insert the
data and then put the references back without doing it manually?

Overall I think this is a big assignment for my first sql project.
But the other guy quit so I am stuck with it.
(maybe I am just realizing why he quit ROFL)


I know that is a simplification of what I am doing but with the
complex problem of building new "COLUMNS" to identify whether the data
comes from A or B and trying to use their schema this is a nightmare
to me.

Any pointers, ideas or whatever are welcome

[Erland Sommarskog]

Post by sparks
what I have problems with is when you have references in C do I have
to delete all the references in the tables, insert the data and then
rebuild the references.

What references? You mean the foreign key definitions?

If you want to perform this operation with no foriegn keys getting in your
way, you can say:

ALTER TABLE tbl NOCHECK CONSTRAINT constraint_name

To do this on all tables, runs this query:

SELECT 'ALTER TABLE ' + quotename(s.name) + '.' + quotename(o.name) +
' NOCHECK ' + quotename(fko.name)
FROM sys.foreign_keys fk
JOIN sys.objects fko ON fk.object_id = fko.object_id
JOIN sys.objects o ON o.object_id = fk.parent_object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id

and then run the result.

To enable the constraints again, the command is somewhat funny:

ALTER TABLE tbl WITH CHECK CHECK CONSTRAINT constraint_name

The extra WITH CHECK is needed to have SQL Server actually validate that
the constraints are valid.

It goes without saying that you should keep backups so that you can
go back if one step of the operation does not complete successfully.

[sparks]

Thank you for the help.
Sorry it too soooo long to get back.
I have 3 other projects and have been running in circles.

one more thing


if you have a column with values such as 1,2,3,4

not I have to replace them with 5,6,7,8
is there a single way to say if 1 replace with 5, if 2 replace with 6
etc

I see how on a replace 1 with 5
then another statement replace 2 with 6

but not all at once,can that be done







On Sun, 25 Mar 2012 11:28:27 +0200, Erland Sommarskog

Post by Erland Sommarskog

Post by sparks
what I have problems with is when you have references in C do I have
to delete all the references in the tables, insert the data and then
rebuild the references.

What references? You mean the foreign key definitions?
If you want to perform this operation with no foriegn keys getting in your
ALTER TABLE tbl NOCHECK CONSTRAINT constraint_name
SELECT 'ALTER TABLE ' + quotename(s.name) + '.' + quotename(o.name) +
' NOCHECK ' + quotename(fko.name)
FROM sys.foreign_keys fk
JOIN sys.objects fko ON fk.object_id = fko.object_id
JOIN sys.objects o ON o.object_id = fk.parent_object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
and then run the result.
ALTER TABLE tbl WITH CHECK CHECK CONSTRAINT constraint_name
The extra WITH CHECK is needed to have SQL Server actually validate that
the constraints are valid.
It goes without saying that you should keep backups so that you can
go back if one step of the operation does not complete successfully.

[Bob Barrows]

Post by sparks
Thank you for the help.
Sorry it too soooo long to get back.
I have 3 other projects and have been running in circles.
one more thing
if you have a column with values such as 1,2,3,4
not I have to replace them with 5,6,7,8
is there a single way to say if 1 replace with 5, if 2 replace with 6
etc
I see how on a replace 1 with 5
then another statement replace 2 with 6
but not all at once,can that be done

Sure - use a CASE statement:

update table set column = case column, when 1 then 5 when 2 then 6 ... end

I'm assuming the actual scenario is more complex than the simple one you've
described - in your simple scenario the solution is to simply say:

update table set column = column + 4

[sparks]

thanks again..worked perfectly

I am now at the part of this so I have to remap about half the columns
because they tablelookups to look up everything and nothing matches.

I understand now why the first bunch of people (I guess I am a bunch
now) failed on this after working for about 300 hours total. I have
been working on it for 3 days and they want to know why I am not
finished.

I said I am, all I lack if finishing up


On Thu, 29 Mar 2012 11:06:49 -0500, "Bob Barrows"

Post by Bob Barrows

Post by sparks
Thank you for the help.
Sorry it too soooo long to get back.
I have 3 other projects and have been running in circles.
one more thing
if you have a column with values such as 1,2,3,4
not I have to replace them with 5,6,7,8
is there a single way to say if 1 replace with 5, if 2 replace with 6
etc
I see how on a replace 1 with 5
then another statement replace 2 with 6
but not all at once,can that be done

update table set column = case column, when 1 then 5 when 2 then 6 ... end
I'm assuming the actual scenario is more complex than the simple one you've
update table set column = column + 4