Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
RichardGG
Starting Member
10 Posts |
 Posted - 2007-10-30 : 18:18:00
|
| Hello All,I'm not an expert in SQL and here's the stiuation:- Database1 was created to run in version1 of TheBestAppInTheWorld- Database2 began as an identical COPY of Database1 at one point in time but nearly all table structures have been modified and it has grown through application testing and development of TheBestAppInTheWorld version 2- Database1 has also grown through production use- I now need to delete all data in Database2 and copy all data from Database1 into it to make Database2 the production database working with TheBestAppInTheWorld version 2 for switchover.Both databases sit on the same MS SQL 2000 serverMy Questions:1. Assuming correct syntax, would Select * Into <DestinationTableName> From <SourceTableName) copy data from source SourceTableName into DestinationTableName? Would it copy fields of the same name and NOT choke on the fact that DestinationTableName has more fields?2. If so, what WOULD the correct syntax be for copying from one database to another?3. If not, how can I accomplish this as easily as possible? Many thanks in advance for any suggestions!Richard |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-10-30 : 18:30:40
|
| 1. Yes it would crash because the definition of * would be "everything" and the column alignment wouldn't match, and it would not be able to guess which columns are correct for the insert. It is always better to specify the column names for the insert and specify the column names for the select statement. Better safe than super-sorry.2. DTS can be used very effectively to import from one to the other, and in this case,when you specify destination tables, the column names which match will auto-map properly. Use DTS and set up a connection to your source and a connection to your destination. Highlight both and choose the transform data task. You can specify what you want to do from there..it is pretty easy. Make sure in the options that you append the data, rather than delete existing data in the destination and transform options.3. See # 2. Make sure you have a full backup which can be restored from the end of business before you do this just in case. |
 |
|
|
RichardGG
Starting Member
10 Posts |
Posted - 2007-10-31 : 16:59:36
|
| dataguru1971,Thanks for the reply!These databases are hosted on a remote server at hostmysite.com (a good outfit). When I bring up Entrprise Manager and go to Tools > Data Transformation Services all I see is Import & Export Data. Is that what you mean?I've seen DTS packages before but never created one.Am I looking in the wrong place? Is it because of the hosting situation? Will using the import/export services function the same way?Thanks for your patience,Richard |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-31 : 17:03:55
|
| 1. DestinationTable can not exist when running your query. The query will create the object for you. As a result of this, the tables will have the exact same columns.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
RichardGG
Starting Member
10 Posts |
Posted - 2007-10-31 : 17:29:39
|
| Tara,Can you recommend a way to accomplish what I need to in Enterprise manager without creating separate queries to copy data for each set of tables? I work in Coldfusion.Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-31 : 17:33:16
|
| BACKUP/RESTORE is the best method to use.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
copy table between DB's with modified structure