How do I copy one table to another.

[Dennis]

Hi,

I am finshing up an major upgrade for some church membership project I'm
working on.

I have added a LOT of new fields to the tblMembership table. I want to copy
the data in the old tblMembership table to the new tblMembership table. The
new tblMembership table is empty.

Specifically, how would I do this? A sample SQL statement would be most
helpful.

Thanks

Dennis

 

[Stefan Hoffmann]

hi Dennis,

I have added a LOT of new fields to the tblMembership table. I want to copy
the data in the old tblMembership table to the new tblMembership table. The
new tblMembership table is empty.

Specifically, how would I do this? A sample SQL statement would be most
helpful.

INSERT INTO newTable (fieldList)
SELECT fieldlist
FROM oldTable


mfG
--> stefan <--

 

[Dennis]

Stefan,

I entered this into query builder in SQL mode / view:

INSERT INTO tblMailingList-Prod-18 (CanteenNo)
SELECT CanteenNo
FROM tblMailingList-Prod;


When I tried to swith to Query Design view, I received the error message
"Syntax error in INSERT INTO statement". Any suggestions?

Thanks,

Dennis

 

[KenSheridan via AccessMonster.com]

Dennis:

You need to wrap the table name in square brackets as it includes the hyphen
characters. Table or field name which include spaces or special character
need to be bracketed like this. If in doubt include the brackets.

If your new table includes all the original columns of the old table, with
the same names, you don't in fact need to include a column list. You can use
an asterisk to denote all columns and the query will then append the values
from them all into the same named columns in the new table and leave the new
columns empty:

INSERT INTO [tblMailingList-Prod-18]
SELECT *
FROM [tblMailingList-Prod];

Make sure that none of the new columns have their Required property set to
True (Yes), or if it is, have the DefaultValue property set to something
appropriate. Otherwise the 'append' query will fail.

Ken Sheridan
Stafford, England

 

[John W. Vinson]

Hi,

I am finshing up an major upgrade for some church membership project I'm
working on.

I have added a LOT of new fields to the tblMembership table.

That statement makes my neck hairs bristle. VERY few tables need "a LOT" of
fields! "Fields are expensive, records are cheap"; if you're adding a lot of
Yes/No fields or few-choice fields, you may well have "committed spreadsheet",
and may be better off adding a new *table* related one to many to
tblMembership. What sort of fields do you mean?

I want to copy
the data in the old tblMembership table to the new tblMembership table. The
new tblMembership table is empty.

Specifically, how would I do this? A sample SQL statement would be most
helpful.

An Append query will let you add *all the new fields at one go*, not just one
field at a time as in your response in this thread. We can't see your tabl or
fieldnames from here, but the syntax would be something like

INSERT INTO tblMembershipNew([thisfield], [thatfield], [anotherfield], <etc
through all the fields you want to migrate into>)
SELECT [this], [that], [another], <etc.> FROM tblMembershipOld;

The details will of course depend on the structure of the tables, and since
your new fields do not have any corresponding field in the old table, they
will need to either be left NULL to be filled in manually later, or with some
sort of default or calculated value.

 

[Dennis]

Ken,

That worked great!

And thats for the explanation. I learned a little a little more each date.

Thanks for the help.

Dennis

 

[Dennis]

John,

My Comment “I am finishing up an major upgrade for some church membership
project. I have added a LOT of new fields to the tblMembership table. “

Your Response “That statement makes my neck hairs bristle. VERY few tables
need "a LOT" of fields! “

My Response: I added about 30 fields. The first release worked so well, the
user want to greatly expand that data that was being collected. As a result,
we added about 20 to 30 data fields. The updated member record has about 70
fields.

I have to disagree with you regarding the comment “VERY few tables need a
lot of fieldâ€. I’ve been developing commercial insurance processing system
using relational databases and third normal form for about 30 year. Some
policy, claims, and reinsurance tables can contain a lot of data with a lot
of fields. Obviously, projects of that size do not belong on Access. With
respect to Access, you are probably correct.


Your Response: "Fields are expensive, records are cheap"; if you're adding a
lot of Yes/No fields or few-choice fields, you may well have "committed
spreadsheet",

My Response: I don’t understand “Fields are expensive, records are cheap.â€
It has been my understanding that you want to minimize the number of time you
go to the hard drive, especially over the network. With other databases, it
has been my experience that “Fields are cheap, records are expensiveâ€. What
makes Access an exception to this?

I don’t know what you mean by “committed spreadsheetâ€.

Are you trying to tell me that Access works better if I break up a large
master record into multiple small master records in multiple tables?


Your Response “and may be better off adding a new *table* related one to
many to tblMembership. What sort of fields do you mean?â€

My Response: Why would I create a new table relating one to many when all
tables are already in third normal form? The data in each row is a property
of the member and is in a one to one relationship.


Your comment “An Append query will let you add *all the new fields at one
go*, not just one field at a time as in your response in this thread. We
can't see your table or fieldnames from here, but the syntax would be
something like

My Comment: I stated that wanted to copy all of the fields from my old
table to the new table. When it did not work, I used a simpler version so I
could provide the exact syntax in the hope that someone could tell me what I
did wrong.

Ken Sheridan’s response resolved my issue. I wanted to copy all of the
existing fields from the old table to the new table. You are correct in that
I have a series of update queries to fill in some of the new data fields.
For the other new data fields, there will be nothing in them until the users
enters the data.


Thanks for you comment. I learn something for everything I read. Thanks
again. I hoped I explained my thought process.



Dennis