Copy &Inserting the dataStructure from one table to other table?

[Hanuma]

Hi
I want the structure of the one table should copy to new table.
Can i do this one in our sql server?

please tell me the solution.


thanks in advance and will appreciated.

[Delerna]

What version of SQL server are you using, the procedure for a method will vary, depending on the version.
Within any version there are several methods. Here are a couple that I use in SQL server 2000.
1) DTS Import/Export method copies the table structure and data
In Enterprise manager
....navigate to the database where you want the table to go
....Right click on Tables and select AllTasks/ImportData
....On the "Choose a data source" window
........choose the server and provide login details
........select the database having the source table and click next
....On the "Choose a destination" window it is probably already set
........click next
...I am sure you will be able to figure out the rest.
.
.
2) Using sql which will copy the table structure only
In Enterprise manager
...navigate to the table you want to copy
...right click on the table and copy it
...select menu Tools/query analyser
...In query analyser top section...right click and paste
...You will see something like the code posted below
...You will of course need to change the table name (sysusers in my code) if the source and destination databases are the same
...In the toolbar of query analyser you will see a database dropdown
...Change it to the destination database and click the play button
...Goto the tables of the destination database in enterprise manager
...The table is there. (You may need to refresh the table display)

CREATE TABLE [sysusers] (
    [uid] [smallint] NOT NULL ,
    [status] [smallint] NOT NULL ,
    [name] [sysname] NOT NULL ,
    [sid] [varbinary] (85) NULL ,
    [roles] [varbinary] (2048) NOT NULL ,
    [createdate] [datetime] NOT NULL ,
    [updatedate] [datetime] NOT NULL ,
    [altuid] [smallint] NOT NULL ,
    [password] [varbinary] (256) NULL 
) ON [PRIMARY]
GO
 

There are other methods and the SQL method in SQL2007 is very different to how I described.
I have done it but I will let someone else more familiar with 2007 than I am describe it..if indeed you have that version

[ck9663]

Do you need the table structure/metadata or you want to create a table that exactly mimics another table?

~~ CK

[nbiswas]

Apart from the answer given by Mr. Delerna, I would like to add a few more

Assuming 2005+ , you can use SSIS(Export /Import wizard tool)

Assuming 2008+, you can take the advantage of

Generate Scripts

Another option is Select ..Into and Insert into Select. Look into this article

SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE

Hope this helps