Create a new table and adding a primary key using SELECT INTO

Question

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?

Answer 1

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)

Answer 2

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)