Geek asks and answers

Create a new table and add the primary key using SELECT INTO

I have a database table that is created using SELECT INTO SQL syntax. The database is located in Access and consists of approximately 500,000 rows. The problem is that when I make the connection, the unique one is the whole line - I would like this field with an automatic number as the primary key.

The code I have now looks something like this:

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 sentence to my SELECT INTO query so that I could add a primary key and create a table in one pass - is this possible?

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

+5
source share
3 answers

Primary Key INSERT INTO , . IDENTITY, SQL Server IDENTITY().

- :

SELECT 
    ID = IDENTITY(INT, 1, 1),
    col1,
    col2
INTO new_table 
FROM (SELECT * FROM table a, table b WHERE a.name = b.name)
+7

Access , , . , ...

  • , .

  • AutoNumber :

    ALTER TABLE [new_table] [ID] NOT NULL

  • , :

    ALTER TABLE [new_table] ADD CONSTRAINT NewTable_PK (ID)

+1

?

, . .

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


insert into Tester
Select description
from table

, , script . "# (-)" , , , .

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

"". - 1 1, 95% . , . , :

insert into table (col1, col2)

, .

0

More articles:


All Articles