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

database, dataset, ms-access, sql

Solution

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)

Problem

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?

Original source