Copy a table from one db to another including all configurations
-
How to copy a table from one db to another including all configurations, such as index, primary key...
I use
select * into newtable from originaltable
but missing indexes.
-
Is SSMS right-click the table and select Create To Clipboard
Go to the alternate database and open a query
Paste the create statement and run it.
Now that you have the table just do:
set Identity_insert tableb on
go
insert into tableb
select * from db1.dbo.tablea
set Identity_insert tableb off
go
Does that help?
-
you have to script the table out , whether via SSMS, powershell, or custom TSQL.
select into newtable only copies an identity definition(if it exists) and column datatype/size/length/nullability.
it does not copy any constraints, like unique, check or defaults, or calculated column calculations it returns the results of the calcuation as the datatype column..
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Easiest way is to use SSMS.
Right click the database -> Tasks -> Generate Scripts.
This will bring up a wizard. Choose the table(s) you want to script. Then on the "Set Scripting Options" click the advanced button. You can choose all sorts of options in the Table/View Options section.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
One thing to bear in mind, the Generate Script does NOT include any column COLLATION settings. These still have to be set manually.
-
DiverKas (4/15/2013)
One thing to bear in mind, the Generate Script does NOT include any column COLLATION settings. These still have to be set manually.Not true, using advanced options you can script the COLLATION of character based columns.
-
DiverKas (4/15/2013)
One thing to bear in mind, the Generate Script does NOT include any column COLLATION settings. These still have to be set manually.Sure it does. It is all about the options in the script wizard.
--EDIT--
Bitten by the quote bug again.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Lynn Pettis (4/15/2013)
DiverKas (4/15/2013)
One thing to bear in mind, the Generate Script does NOT include any column COLLATION settings. These still have to be set manually.Not true, using advanced options you can script the COLLATION of character based columns.
You right, I was thinking of the Create Script option from right clicking the table.
-
DiverKas (4/15/2013)
Lynn Pettis (4/15/2013)
DiverKas (4/15/2013)
One thing to bear in mind, the Generate Script does NOT include any column COLLATION settings. These still have to be set manually.Not true, using advanced options you can script the COLLATION of character based columns.
You right, I was thinking of the Create Script option from right clicking the table.
You can change how the defaults work for that too. 😀 Open Tools -> Options -> SQL Serve Object Explorer -> Scripting. You have a long list of options for how scripts will work. You can edit this to suit whatever you want.
You are however absolutely correct that by default the collation is not scripted.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply