How do I make a copy of a table within SQL Server

Question

This is one of those "I probably should know this, but I don't" questions. How do I make a copy of a table in Enterprise Manager? Not export the data into a different database, just make a copy of the table within the same database. At this point, I don't care whether the data comes with or not, but the table definition should definitely be duplicated.

If I do Ctrl+C on a selected table and paste the results into a text editor, it gives me a Create Table statement that looks promising, but I can't for the life of me find any place to run that statement.

Edit: note that I'm asking about SQL Server Enterprise Manager. This is NOT the same thing as "SQL Server Management Studio". There's no "New Query" button, neither in the top left corner nor anywhere else. In fact, the word 'query' does not occur anywhere in EM except on the Tools menu, and there it's a link to Query Analyzer.

Answer 1

SELECT * INTO MyNewTable FROM MyOldTable

The new table will be created without primary keys, foregin keys nor anything else

Answer 2

If you're talking 2000 enterprise manager: Tools -> Query analyzer Will give you a place to execute the query.

Answer 3

Copy a table with all content (without keys and constraints)

select * into tablecopy from table 

Copy a table without content (without keys and constraints)

select top 0 * into tablecopy from table 

There's a way to copy it with all constraints, in the right click menu (of the table) then all tasks then script as then create to then new query editor window (or a file). You will have to modify the generated script so the constraints and keys are named differently though, and then do an

insert into tablecopy select * from table

to populate it.

EDIT: If what you are actually asking is a place to run SQL in the Enterprise Manager, try either the Query Analyzer or the Management Studio.

Answer 4

Try this:

SELECT *
INTO new_table_name
FROM old_table_name

Answer 5

If you want primary keys, etc., right-click on the table, select "Script table as", then "Create to", then "New Window". Change the name of the table to the name of the copy and run the script. Then you can select into as per the other answers if you'd like the data.

Answer 6

Using TSQL...

SELECT * INTO MyTableCopy FROM MyTable WHERE 1=2

Using EM, right-click on the object > All Tasks > Generate SQL Script.