Re: How do I copy records and their relational records too?

dterrors-at-hotmail.com wrote:
> On Feb 4, 4:55 pm, "Paul Lautman" wrote:
>> dterr...@hotmail.com wrote:
>> > I can't do insert...select because the keys will all be different
>> > on the newly copied tables.
>>
>> Why?
>
> Because I'm copying records. So therefore their keys will be
> different. Unique keys? Two records can't have the same key?

But you are copying to new tables, so there will still only be one of each
key in the table.

On Feb 4, 5:17 pm, "Paul Lautman" wrote:
> dterr...@hotmail.com wrote:
> > On Feb 4, 4:55 pm, "Paul Lautman" wrote:
> >> dterr...@hotmail.com wrote:
> >> > I can't do insert...select because the keys will all be different
> >> > on the newly copied tables.
>
> >> Why?
>
> > Because I'm copying records. So therefore their keys will be
> > different. Unique keys? Two records can't have the same key?
>
> But you are copying to new tables, so there will still only be one of each
> key in the table.

I'm copying to the same table.

Although even if it was to a new table- how do I link up the binding
table (table c)? Table c's value for db_a_id is no longer relevant.
It may have "15" where there is no 15 in the new table (or 15 is
something else).

I'm trying to copy individual (set of records). As in "copy record 12
of table a and include (also make copies of) everything that relates
to it in table b, including the relationship record in table c"

The new relationship record needs to be aware of the new a and b keys.

dterrors-at-hotmail.com wrote:
> On Feb 4, 5:17 pm, "Paul Lautman" wrote:
>> dterr...@hotmail.com wrote:
>> > On Feb 4, 4:55 pm, "Paul Lautman"
>> > wrote:
>> >> dterr...@hotmail.com wrote:
>> >> > I can't do insert...select because the keys will all be
>> >> > different on the newly copied tables.
>>
>> >> Why?
>>
>> > Because I'm copying records. So therefore their keys will be
>> > different. Unique keys? Two records can't have the same key?
>>
>> But you are copying to new tables, so there will still only be one
>> of each key in the table.
>
> I'm copying to the same table.
>
> Although even if it was to a new table- how do I link up the binding
> table (table c)? Table c's value for db_a_id is no longer relevant.
> It may have "15" where there is no 15 in the new table (or 15 is
> something else).
>
> I'm trying to copy individual (set of records). As in "copy record 12
> of table a and include (also make copies of) everything that relates
> to it in table b, including the relationship record in table c"
>
> The new relationship record needs to be aware of the new a and b keys.

I quote: "I can't do insert...select because the keys will all be different
on the newly copied tables."

If you were doing what you said you were doing, copying to new tables, then
there would be no reason for the keys to change, in any of the 3 tables.
That is the reason I asked "why", because your question did not make sense.

On Feb 4, 5:43 pm, "Paul Lautman" wrote:
> dterr...@hotmail.com wrote:
> > On Feb 4, 5:17 pm, "Paul Lautman" wrote:
> >> dterr...@hotmail.com wrote:
> >> > On Feb 4, 4:55 pm, "Paul Lautman"
> >> > wrote:
> >> >> dterr...@hotmail.com wrote:
> >> >> > I can't do insert...select because the keys will all be
> >> >> > different on the newly copied tables.
>
> >> >> Why?
>
> >> > Because I'm copying records. So therefore their keys will be
> >> > different. Unique keys? Two records can't have the same key?
>
> >> But you are copying to new tables, so there will still only be one
> >> of each key in the table.
>
> > I'm copying to the same table.
>
> > Although even if it was to a new table- how do I link up the binding
> > table (table c)? Table c's value for db_a_id is no longer relevant.
> > It may have "15" where there is no 15 in the new table (or 15 is
> > something else).
>
> > I'm trying to copy individual (set of records). As in "copy record 12
> > of table a and include (also make copies of) everything that relates
> > to it in table b, including the relationship record in table c"
>
> > The new relationship record needs to be aware of the new a and b keys.
>
> I quote: "I can't do insert...select because the keys will all be different
> on the newly copied tables."
>
> If you were doing what you said you were doing, copying to new tables, then
> there would be no reason for the keys to change, in any of the 3 tables.
> That is the reason I asked "why", because your question did not make sense.

Good point. I said "tables" where I meant "records" in that sentence.

On Feb 4, 5:23*pm, dterr...@hotmail.com wrote:
> On Feb 4, 5:17 pm, "Paul Lautman" wrote:
>
> > dterr...@hotmail.com wrote:
> > > On Feb 4, 4:55 pm, "Paul Lautman" wrote:
> > >> dterr...@hotmail.com wrote:
> > >> > I can't do insert...select because the keys will all be different
> > >> > on the newly copied tables.
>
> > >> Why?
>
> > > Because I'm copying records. *So therefore their keys will be
> > > different. *Unique keys? *Two records can't have the same key?
>
> > But you are copying to new tables, so there will still only be one of each
> > key in the table.
>
> I'm copying to the same table.
>
> Although even if it was to a new table- how do I link up the binding
> table (table c)? *Table c's value for db_a_id is no longer relevant.
> It may have "15" where there is no 15 in the new table (or 15 is
> something else).
>
> I'm trying to copy individual (set of records). *As in "copy record 12
> of table a and include (also make copies of) everything that relates
> to it in table b, including the relationship record in table c"
>
> The new relationship record needs to be aware of the new a and b keys.

such are the troubles of those blindly using autonum for the primary
key.
Regular readers of this group have been warned about blindly using
autonum PKs.

You will have to basically follow the same logic you did for inserting
the first set of data. This is beyond straight SQL to solve. You will
need to write a copy program in a procedural language like PERL or C.
It shouldn't take too long to write.

Good luck.
Ed

Ed Prochak wrote:

> such are the troubles of those blindly using autonum for the primary
> key.
> Regular readers of this group have been warned about blindly using
> autonum PKs.

Heh. This is half true. The OP cross-posted to comp.databases.mysql, and
also mentioned 50 columns per table so there could be some fun in
normalizing that.