19

I have a database table that is created using the SELECT INTO SQL syntax. The database is in Access and consists of roughly 500,000 rows. The problem is when I do the join, the unique is the entire row - what I would like is an auto number ID field as the Primary Key.

Code I currently have is something like:

SELECT INTO new_table
FROM 
(SELECT * FROM table a, table b WHERE a.name = b.name)

I was hoping there was a way to add a clause into my SELECT INTO query so I could add a primary key and create the table in one pass - is this possible?

If not, what is the best way to do this using SQL only?

LittleBobbyTables - Au Revoir
  • 32,008
  • 25
  • 109
  • 114
user559142
  • 12,279
  • 49
  • 116
  • 179

3 Answers3

17

Adding a Primary Key during a INSERT INTO statement is not possible as far as I'm aware. What you can do however, is add an IDENTITY column that auto increments by using SQL Server's IDENTITY() function.

Something like this:

SELECT 
    ID = IDENTITY(INT, 1, 1),
    col1,
    col2
INTO new_table 
FROM (SELECT * FROM table a, table b WHERE a.name = b.name)
Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
JodyT
  • 4,324
  • 2
  • 19
  • 31
  • 2
    There must be a way to make this a primary key.? – user559142 Mar 19 '13 at 20:13
  • @user559142 This function is available in SS2008 and later, not SQL Azure (yet?). – Gert Arnold Sep 29 '17 at 09:36
  • @user559142 Primary key as of today can be added as ALTER TABLE statement. – user482745 Jun 18 '18 at 09:58
  • 2
    This is NOT a valid answer to the OP's question. He specifically said the database is in Access, and Access does not have an Identity function. SQL Server and Azure databases (not warehouses) do, but Access does not, and it will return the Undefined error the OP quoted in his second comment on this reply. – DaveInAZ Aug 13 '19 at 17:40
  • 1
    Awesome option inline IDENTITY. Works perfect with 'SELECT.. INTO...' exactly what I needed!! after that add 'ALTER TABLE [xxx] ADD CONSTRAINT xxxx_PK PRIMARY KEY (ID) – fcm Oct 12 '19 at 13:00
9

In Access I don't believe that you can accomplish what you want in a single statement. What I think you'll need to do is...

  1. Create the new table as you described in the question.

  2. Add the AutoNumber column like this:

    ALTER TABLE [new_table] ADD COLUMN [ID] AUTOINCREMENT NOT NULL

  3. Make the column you just added the Primary Key:

    ALTER TABLE [new_table] ADD CONSTRAINT NewTable_PK PRIMARY KEY (ID)

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
0

What is the need for creating a primary key and inserting at the same time?

You can create a primary key and table at the same time, but not at insert to my knowledge. You can do this.

create table Tester 
(
Id int identity
    constraint PK_Id primary key(Id),
    description varchar(256)
)


insert into Tester
Select description
from table

or add a drop and create too if you want to ensure the table does not already exist and run this script a bunch. A temp table '#(something)' can follow many of the rules of permanent tables except be used for code level movement and then cleanup when done.

if object_id('(schema).(tablename)') is not null 
   drop table (schema).(tablename)

I created an ever increasing seed with 'identity'. The default is to start at 1 and increment by 1, which serves 95% of simple database primary key wants. It increments automatically so you don't need to worry about it. You can also insert into just specific values if you have more with things like:

insert into table (col1, col2)

This eliminates the need to insert EVERYTHING and just be the columns you want.

djangojazz
  • 14,131
  • 10
  • 56
  • 94
  • You may be limited then as this was done in SQL Management Studio. You probably can get away with the identity but I am assuming NOT the constraint portion. – djangojazz Mar 19 '13 at 20:58