Create Table As Select and add an Index SQL Server 2008

Question

I have a view that I'd like to create a table from in a query. I'd also like to add a primary key to the table, I was thinking something like the following:

SELECT *
, PK INT IDENTITY(1,1) PRIMARY KEY

INSERT INTO TESTTABLE

FROM my_view

SELECT * FROM TESTTABLE

DROP TESTTABLE

The above does not work, so...

a) is it possible and b) if so, how?

Thank you,

why don't you add the primary key column to the view itself and call it when needed? — Vamsi Prabhala
– Vamsi Prabhala, Commented Oct 22, 2015 at 19:55

Lukasz Szozda · Accepted Answer · 2015-10-22 19:59:31Z

1

You can use SELECT INTO to create new table and ALTER it to add PK:

SELECT *
INTO TESTTABLE
FROM my_view;

ALTER TABLE TESTTABLE
ADD PK INT IDENTITY(1,1) PRIMARY KEY;

SELECT *
FROM TESTTABLE;

DROP TABLE TESTTABLE;

SqlFiddleDemo

edited Oct 22, 2015 at 19:59

answered Oct 22, 2015 at 19:39

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

MCP_infiltrator Over a year ago

When I check the syntax on this one I get the error of Unknown object 'TESTTABLE' used in CREATE, DROP or ALTER statement it seems to be only the DROP that is causing this problem as when I check syntax without it, its fine. Any thoughts?

Lukasz Szozda Over a year ago

@MCP_infiltrator Try with schema part dbo.TESTTABLE

MCP_infiltrator Over a year ago

negative...maybe I do not have DROP table permissions?

Lukasz Szozda Over a year ago

@MCP_infiltrator Check new demo, do you call it in stored procedure?

Lukasz Szozda Over a year ago

@MCP_infiltrator DROP TABLE smsdss.TESTTABLE; You need to specify what are you dropping :)

|

Collectives™ on Stack Overflow

Create Table As Select and add an Index SQL Server 2008

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related