Solved

Microsoft Access SQL Select Into Statement

Posted on 2012-03-27
6
453 Views
Last Modified: 2012-03-31
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?
0
Comment
Question by:vfinato
6 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37773245
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37773247
You would have to change the datatype of the primary key in the target table from Autonumber to Long Integer.
0
 
LVL 8

Expert Comment

by:gpizzuto
ID: 37773410
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)
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:vfinato
ID: 37774120
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.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 37774191
If you use an insert query like in my first post, nothing gets wiped out.
0
 

Author Closing Comment

by:vfinato
ID: 37791525
This worked, thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now