• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 526
  • Last Modified:

Microsoft Access SQL Select Into Statement

I have 2 tables, and use 1 of the tables to copy into the 2nd table.  Both tables have the same Primary Keys.  I use a Select into Statement: SELECT * INTO <Target> FROM <Source>

The SQL query works just fine, but, the primary key on the target table gets wiped out.  How can I use the SELECT INTO sql and not wipe out the primary key?
1 Solution
Are you looking for an Append query (just adds rows to an existing table)?

INSERT INTO TableB (pk, fld1, fld...) SELECT (pk, fld1,fld2...  FROM TableA)

In order to copy the PK from tableA to tableB, the corresponding field in tableB should Not be an autonumber.
Dale FyeCommented:
You would have to change the datatype of the primary key in the target table from Autonumber to Long Integer.
You can also ignore the copy of the primary key
(if it is only a counter and have another natural-key, for example some fields together):

INSERT INTO TableB (fld1, fld...) SELECT (fld1,fld2...  FROM TableA)
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

vfinatoAuthor Commented:
The Primary Key Field is not a "Autonumber", the field type is text, and the primary key still gets wiped out.

Also, if I don't include the Primary Key Field on the SQL query, then, that field does not get included in the <target> table.
If you use an insert query like in my first post, nothing gets wiped out.
vfinatoAuthor Commented:
This worked, thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now