Creating table from select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Creating table from select statement

Author

Topic

herman404
Starting Member

10 Posts

Posted - 2006-05-02 : 12:23:40

Hi, I'm trying to copy a table by doing this:

CREATE TABLE @tablename SELECT * FROM @originaltable

However, SQL Server keeps complaining about incorrect syntax or the variables being declared, even though I am declaring them above and outputting them via SELECT @tablename just to make sure. Can this be done via a script/stored procedure? What am I doing wrong?

Thanks,

Kristen
Test

22859 Posts

Posted - 2006-05-02 : 12:34:34

You can't do it that way using variables.

You can do

SELECT Column1, Column2, ...
INTO MyNewTable
FROM MyOriginalTable

- and if you don't want to actually copy any of the data just add WHERE 1=0

But if you want to declare a TableVar then you will need to use a formal declaration:


DECLARE @tablename TABLE
(
    Column1 int NOT NULL,
    Column2 varchar(100) NULL,
    PRIMARY KEY
    (
        Column1
    )
)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources