Insert Into

[Simon Harris]

Hi All,

I wish ti import data from an one Access database into another one. I want
to retain the autonumber values on my ID Column. Am i right in thinking that
if I switch my destinations tables ID column to number, rather than
autonumber, then run an 'insert into' query, this should work?

I have tried this:

insert into tbl_alloc (select * from tbl_allocSource)

tbl_allocSource is a linked table, linking to the source database. tbl_alloc
is a new table matching the structure of the source table. When I run this,
I get an error in insert into statement.

Any help will be mucho appreciated!

Cheers!
Simon.

--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!

 

[Smartin]

Hi All,

I wish ti import data from an one Access database into another one. I want
to retain the autonumber values on my ID Column. Am i right in thinking that
if I switch my destinations tables ID column to number, rather than
autonumber, then run an 'insert into' query, this should work?

I have tried this:

insert into tbl_alloc (select * from tbl_allocSource)

tbl_allocSource is a linked table, linking to the source database. tbl_alloc
is a new table matching the structure of the source table. When I run this,
I get an error in insert into statement.

Any help will be mucho appreciated!

Cheers!
Simon.

What's the error? What's in tbl_alloc before you start? Are there keys
that would create duplicate issues when you import the new stuff?

If you are starting with an empty tbl_alloc, delete tbl_alloc, then run
this query:
SELECT * INTO tbl_alloc FROM tbl_allocSource;

This will populate your new table, preserving autonumber keys from the
old table. This will not however create an indexes, so you will have to
do that yourself afterward.

 

[Guest]

Simon:

Remove the parentheses:

INSERT INTO tbl_alloc
SELECT * from tbl_allocSource;

Parentheses around a SELECT statement are used to identify it as a subquery,
which is not the case here. The ID column of tbl_alloc can be an autonumber.
If the destination table is empty there will be no problem; nor will there
be a problem if it contains data but no values in the ID column duplicate
those in the source table's ID column. If it contains data and there are
duplicated ID values however, the key violations will prevent the duplicate
value rows being inserted. This is true whether the column is an autonumber
or not if it is the primary key or otherwise uniquely indexed.

Ken Sheridan
Stafford, England

 

[adotek]

Simon:

Remove the parentheses:

INSERT INTO tbl_alloc
SELECT * from tbl_allocSource;

Parentheses around a SELECT statement are used to identify it as a subquery,
which is not the case here. The ID column of tbl_alloc can be an autonumber.
If the destination table is empty there will be no problem; nor will there
be a problem if it contains data but no values in the ID column duplicate
those in the source table's ID column. If it contains data and there are
duplicated ID values however, the key violations will prevent the duplicate
value rows being inserted. This is true whether the column is an autonumber
or not if it is the primary key or otherwise uniquely indexed.

Ken Sheridan
Stafford, England

Ken,

Thank you! Working AOK now! I expected to get new auto-number values,
handy that it retains these for relationships.

Simon.