?
Solved

Creare Unique/Primary Key when using SELECT* INTO

Posted on 2014-10-21
7
Medium Priority
?
619 Views
Last Modified: 2014-10-21
I have not been able to find a definitive solution ...

I am using Access 2010 to Pass a Make Table Query to SQL 2012 Express, which creates a new table based on a value in some column data and my query does work; however I always get an Access prompt to select a Primary/Unique Key.

Is there anyway to specify this Primary/Unique Key in the SELECT * INTO statement?

Here is my query ...

/****** Object:  Table [SQL_DB_Name].[dbo].[Raw_Data] Script Date: 10/21/2014 12:53:41 PM ******/
 
USE [SQL_DB_Name]
 
SELECT * INTO [ALL_Zero_ID_Rows]  FROM  [Raw_Data]
WHERE CAST([The_Number_in_This_Column] AS INTEGER) = 0;
0
Comment
Question by:Frank Bryant
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40395382
No, you can't designate a PK or unique key as part of a SELECT ... INTO.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40395408
The low-brain solution here would be...

1.

Create the table, and include a column named id (or whatever floats your boat) that is an int identity(1,1) (Same as Access AutoNumber).  Set id as the primary key.

2.

Instead of SELECT INTO, do a DELETE FROM ALL_Zero_ID_Rows.  
TRUNCATE instead of DELETE works if you want to re-seed the id values starting at 1

3.

Do an insert, explicitly defining all rows, except for the id of course:

INSERT INTO ALL_Zero_ID_Rows (Col1, col2, col3) 
SELECT Col1, Col2, Col3 
FROM  [Raw_Data] 
WHERE CAST([The_Number_in_This_Column] AS INTEGER) = 0;

Open in new window

0
 
LVL 85
ID: 40395416
What is Raw_Data? If it's a table, then you could define a Primary Key on that, and the SELECT INTO should pick that up.
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 7

Expert Comment

by:slubek
ID: 40395423
What about:

SELECT IDENTITY(int,1,1) as ID, * 
INTO [ALL_Zero_ID_Rows]  FROM  [Raw_Data]
WHERE CAST([The_Number_in_This_Column] AS INTEGER) = 0; 

Open in new window


and later:

ALTER TABLE [ALL_Zero_ID_Rows]
  ADD CONSTRAINT PK_[ALL_Zero_ID_Rows]
  PRIMARY KEY (ID);

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40395424
>> the SELECT INTO should pick [a PK] up. <<

I've never seen this happen.  What leads you to believe a PK constraint is copied into a table created using a SELECT ... INTO?
0
 
LVL 85
ID: 40395746
Not the Primary Key constraint, but rather the VALUE of the Primary Key ...
0
 

Author Closing Comment

by:Frank Bryant
ID: 40395985
Thanks, Scott.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

752 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