Creating a blank table from another

Hi again,

I want to create a blank table with the same column names as another but with no rows i.e. it is empty. Does anyone know how to do the last part?

I have so far

create table newtest as select * from shared.test .... (empty bit here?)

Any good advanced sql tutorial urls would be good to.

Thanks in advance :)select *
from OldTable
into Newtable
where 1 = 0

This will not copy triggers, indexes, and the such. Just column names and datatypes.|||Ok i will try that but why does that work?

Cheers :)|||select *
from OldTable
into Newtable

...selects columns from the old table into a Newtable (created on the fly).

where 1 = 0

...always evaluates to false (in my universe, but I'm a Democrat), so no rows match the filter criteria and thus no rows are actually inserted.|||Cheers, Thanks

:D|||U can try like This Also

create table NewTable
as select * from OldTable
where rownum < 1;

Even this will not copy triggers, Indexes and Constraints...|||if you need to copy the table as is (with all indexes, primary/foreign keys, triggers), you can use the option "generate sql script":

Enterprise Manager > Server > DataBase > Tables > put your cursor on the selected table/s > All Tasks (right mouse click) > Generate SQL Script

Then you get a wizard which allow you to create scripts that drops/create your selected objects. on the options tab you may decide if you would like to have the indexes, triggers, relationships...

If you want to create the same table BUT with different name, make sure you rename all the objects (indexes, primary/foreign keys, triggers) before running.

good luck|||Originally posted by Sowmyam
U can try like This Also

create table NewTable
as select * from OldTable
where rownum < 1;

Even this will not copy triggers, Indexes and Constraints...

Ahhhh...the smell of Oracle (Or is it UDB) in the morning...it smells like....confusion

USE Northwind
GO

CREATE TABLE myTable99
AS
SELECT * FROM Orders
WHERE rownum < 1;
GO


And nope...that won't work...|||Hi Brett,
I didn't get u clearly, are u telling

create table NewTable
as select * from OldTable
where rownum < 1;

this code won't work.

I tried it works.
Ok this query Creates a Table NewTable of same structure as OldTable and NewTable will not have any rows.
This won't copy any indxes, constraints and all, but creates a table.

pls give a feedback for this.

Thanks In Advance.|||Hi Brett,
I am very sorry. yes it is confusion.
it will work in Oracle, this is Microsoft SQL server, by thinking it is Oracle i replied.

I am very sorry about it.|||hi
just add truncate command after your code!!

hope this will solve ur issue.

Cheers

Deepak K