By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

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

P: 3
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.
Jan 17 '10 #1
Share this Question
Share on Google+
3 Replies

Expert 100+
P: 1,134
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 the database having the source table and click next
....On the "Choose a destination" window it is probably already set 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 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)
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE [sysusers] (
  2.     [uid] [smallint] NOT NULL ,
  3.     [status] [smallint] NOT NULL ,
  4.     [name] [sysname] NOT NULL ,
  5.     [sid] [varbinary] (85) NULL ,
  6.     [roles] [varbinary] (2048) NOT NULL ,
  7.     [createdate] [datetime] NOT NULL ,
  8.     [updatedate] [datetime] NOT NULL ,
  9.     [altuid] [smallint] NOT NULL ,
  10.     [password] [varbinary] (256) NULL 
  11. ) ON [PRIMARY]
  12. 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
Jan 18 '10 #2

Expert 2.5K+
P: 2,878
Do you need the table structure/metadata or you want to create a table that exactly mimics another table?

~~ CK
Jan 20 '10 #3

P: 149
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
Mar 24 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.