Solved

Microsoft Access SQL Select Into Statement

Posted on 2012-03-27
6
458 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

808 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