Creating tables to mirror an existing table

[stonecoldjk]

Hi,
I need to have a function which creates a table in such a manner that it is a copy of an already existing table with the same column names and datatype etc .Of course it will have a different table name.I could pass the existing table name to the function and the new table name. How do i achieve this functionality in SQL ?
Thanks in advance.
Jay

[HardCoded]

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

[stonecoldjk]

I am sorry but i am not able to get the piece of code that you gave to work. Maybe i am not doing it properly .I tried executing the query in query analyser.
"CREATE TABLE new_tbl SELECT * FROM orig_tbl;"
Could you please tell me whats going wrong.

Jay

[HardCoded]

So what error do you get?
1. The database user must have create permissions.
2. You replace new_tbl and orig_tbl with actual table names.
3. It's for MySQL. And it's right from the MySQL manual.

[stonecoldjk]

Hi,
I am using sql server 2000 .When i run the statement in query analyser i get the following error:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'

My query is

CREATE TABLE ps_title_report1 SELECT * FROM ps_title_report

Maybe it does not work for sql2000.Please advice
Jay

[HardCoded]

Sorry, MS is not my bag. In MySQL this operation is literally as simple as copying a couple of physical files on disk. It doesn't look that easy in SQL Server

[sandis.viksna]

IT is very simply:


SELECT client_guid, name
INTO NewClients
FROM Clients
WHERE (type_code = 2)

like this : )

it will copy only 2 columns where type_code is 2 into the new table (NewClients)

--
Sandis Viksna

[Islands]

stonecold,
You may also have to write separate SQL to get the column data types you want. This will depend on what database you are using. Create your new table and check the data types to see.

For instance to change data types:
ALTER TABLE new_table_name
ALTER COLUMN a_column_name text(24) PRIMARY KEY

This is just an example for setting a primary key on a 24 character text field.

[stonecoldjk]

Sandis,

Your solution worked perfectly.
Thank You.

Jay