| Author |
Topic |
|
kirannatt
Yak Posting Veteran
66 Posts |
 Posted - 2006-09-19 : 12:04:42
|
| HI all,I want to copy my table(both stucture and data)with constraint from one db to another db. I tried select into but it doesn't tranfer my contraints and index. DOes any one know better way of doing it.Thanks! |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
kirannatt
Yak Posting Veteran
66 Posts |
Posted - 2006-09-19 : 12:49:01
|
| Yeah, But I am looking for way so that I can copy my table with structure(constarints) and data in one step. I don't want to create table first and do tranfer using DTS or something else. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-19 : 13:21:03
|
| No way I know of to do that unless you use some 3rd part application, or brew-your-own script with something that generates INSERT statements for the data (which isn't really very efficient!)What problem are you trying to solve - knowing that might enable us to think of a different wayKristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-19 : 13:31:00
|
| "Why not do SELECT * INTO t FROM tThen just apply constraints and indexes from a script"Do you reckon that's better than the other way round:Build the database using a script, and then:INSERT INTO TargetDatabase.dbo.TargetTableSELECT *FROM SourceDatabase.dbo.SourceTableif going that route I would apply the FK part of the script (and the indexes too if its a large database) after the import was completed.Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-19 : 15:15:26
|
| "I like to have repeatable processes every time I build something"You are right, that's the most persuasive argument for Build then Import. Some extra possible hassle with IDENTITY columns, and the need to create the FKs after the import - and probably the indexes too - but that's basically just doing the job properly. "Can you import this for me, its only a one off, don't take any special trouble" ... "Sorry, now I see the data I realise I got it wrong, here's some fresh data, I know you will have scripted it!"Kristen |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2006-09-20 : 06:50:51
|
Notice the KEY PHRASE in the answer from the poster: quote:
I don't want to create table first and do tranfer using DTS or something else.
Regardless if this is the best way, or ONLY way or not. Never understood the "I don't want to" stuff. Whats wrong with solving the problem with industry standard practices or "expert" advice?  [Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-20 : 15:28:34
|
| "Never understood the "I don't want to" stuff"I read the nuance differently as: the OP wants to do it in one step, rather than two.I'd quite like a solution that does that too!Kristen |
|
|
|
|
Copy Table with PK Constarint and Index