how to copy a table

[awise]

In Enterprise manager when you click on a table I see "COPY" as an option. When I click on Copy, the screen flashes and nothing, that I can tell, happens.

I'm looking for a way to copy an existing table and have the ability to rename it with all the data types/structure associated with the original table.

What is the best way to get this done?

Thanks,

zaw

 

[rac2]

Yeah, that is interesting. Looks like someone forgot to do something with that context menu.

I dont know what the best way to copy a table might be, but I use Export to copy tables. Select the table, right-click, select All Tasks and Export Data. This will start the DTS Import/Export Wizard which has many options for renaming and copying data or not.

 

[awise]

That's where I get stuck.
After I would export it, I want to import back into an exact copy of the original table as far as data structure.

The table in question has approximately 80 columns, so I was hoping for an automated method for creating this "copy" table and avoid all that manual entry.

 

[gmmastros]

How about query analyzer...

Select * Into Table_Copy_Name From TableName

This will copy the structure and the data. Some things are NOT copied, like triggers and constraints, but if you don't care about that stuff, then it may qork for you.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[awise]

That sounds good. I'm looking for simply the data structure. No data, or any triggers, indexes, etc. .
I'm not interested in as well.

I'll give this a try.

Thanks,

zaw

 

[gmmastros]

No data !

Select * 
Into   Table_Copy_Name 
From   TableName
[!]Where  1=0[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[awise]

now I looking at this from a different angle.

what would be the proper syntax to update "tablename" with all the data from "table_copy_name".

Both tables have exact structures, but "table_copy_name" has more recent data then "tablename".

Sorry for any confusion. The responses are showing a potential better way to accomplish my original goal.

Thanks again,

zaw

 

[gmmastros]

You already have 2 tables with the exact same structure, but you want to put all the data from 1 table in to another table?

Better have a good backup in case this doesn't work the way you expect it to.

Truncate Table BadTable

Insert Into BadTable
Select * From GoodTable

Truncate table BadTable will delete all the records from a table named BadTable

insert into BadTable Select * from GoodTable will copy all the date from 'GoodTable' in to 'BadTable'. This will ONLY work if the structure of the tables match exactly. Usually the syntax is...

Insert
Into BadTable(Field1, Field2, field3)
Select Field1, Field2, Field3
From GoodTable

You can leave out the fields when the tables match exactly.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom