Skip to main content
A
Anonymous
July 29, 2008
Question

Creat new table from multiple tables in same DB?

  • July 29, 2008
  • 1 reply
  • 796 views
Trying to create a new table bringing in all the records from 3 old tables. All three old tables have common foreign key fields.

I'm not sure how the syntax would look like, but something like:

<cfquery name="qCreateTable" datasource="somedb">
CREATE TABLE newtable
AS
SELECT old_ID as new_ID,

.....

FROM oldtable1, oldtable2, oldtable3 etc.</cfquery

Is this possible in one step, or would I need to create the new table with data from oldtable1, then update with data from 2, 3 etc.? BTW I already tried that with no success (syntax errors).

For now, this is in Access.

Thanks for your help.

rinorman
This topic has been closed for replies.

1 reply

P
paross1
Participating Frequently
July 29, 2008
Are you just consolidating your data or something? Are these tables related to each other? Are you doing this to eventually remove the other tables, or are you planning on having this "other" table in addition to the others? If you aren't going to remove the other tables then you probably don't want to create a new table but to use a SQL query to create a virtual table instead by joining all of the other tables on the appropriate fields.

If you really want to create this other table, then something like this might be what you want to do:

CREATE TABLE newtable
AS
SELECT old_ID as new_ID, ...
FROM oldtable1
UNION
SELECT old_ID as new_ID, ...
FROM oldtable2
UNION
SELECT old_ID as new_ID, ...
FROM oldtable3
.....

Phil
A
Anonymous
July 29, 2008
Phil:

Thanks for you quick response and help.

I will be getting rid of 3 tables and combining the data in one new table.

I inherited this DB and it is unnecessarily complex for what it actually needs to do e.g.contact information, physical address, and company names in 3 separate tables when there is only one record with company, address and one contact info for that company necessary, etc.

Thanks again

newportri
P
paross1
Participating Frequently
July 29, 2008
So, are you going to have to deal with duplicates with the same company appearing in more than one table?

Phil
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices