CODEWITHSUNDEEP
sql-server
Sadat Mainuddin
Sadat Mainuddin

Reputation: 309

A simple help needed about creating table

I am totally new in SQL and SQL Server without any prior IT knowledge. However I try to learn SQL Server just from the love of it. My question is very simple. I am having problem creating table using sql code. I want to create a table named new_table from another table old_table. my code is:

CREATE TABLE new_table
AS (SELECT * FROM old_table)

but it is giving following error: Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '('.

I have done many things but could not figure out what is the problem. Can any one please guide me what is the problem?

Upvotes: 0

Views: 62

Answers (3)

spencer7593
spencer7593

Reputation: 108370

The CREATE TABLE new_table AS is not valid in SQL Server. (That answers the question you asked, about what the problem is.)

Equivalent functionality is available in Microsoft SQL Server (T-SQL), but it uses T-SQL syntax which is different:

SELECT * INTO new_table FROM old_table

That will allow you to create new table contains all of the columns and rows from an existing table.

http://msdn.microsoft.com/en-us/library/ms188029(v=sql.105).aspx

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300489

SELECT * INTO new_table 
FROM SourceTable

new_table must not already exist.

Ref.

In response to your comment:

SELECT * INTO dbo.new_table 
FROM (SELECT DISTINCT col1 FROM old_table) tmptable

Upvotes: 2

Appyks
Appyks

Reputation: 496

Include Top 0 if you just need the table structure(without data)

SELECT TOP 0 * INTO new_table FROM old_table

Upvotes: 1

Related Questions