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 |
|
stephenbirdsall
Starting Member
2 Posts |
 Posted - 2007-02-07 : 10:47:23
|
| I need to copy data from 3 tables in one database into another db. The destination db already contains some data and it is expected that there will be duplicates which we do not want to have copied across (I think there is a constraint that prevents duplicate email addresses which is our main search field)The three tables are effectively a user table, an address table, and a [phone] numbers table, each of which has an auto generated id field. The user table also maintains a reference to the address and numbers tables.We are using SQL Server 8 (SP3) and it has been suggested that I use the data transformation service (DTS) tool which I have used numerous times to copy entire databases, but I can't figure this bit out.I am still learning t-sql using SQL Query Analyzer, but have been doing so for a while and think that I'm fairly competent in it. My main question is this: Is it possible to connect to two DBs at the same time in SQL QA? If so, I'm pretty sure that I could work out how to pass the data across, I'd just need to know how to connect to them both.Any help would be much appreciated. If you need any more information to help, please let me know.Thanks,Stephen |
|
|
rcurrey
Starting Member
30 Posts |
Posted - 2007-02-07 : 15:06:08
|
| Either set up a linked server in Enterprise Manager, or use OPENROWSET in Query Analyzer to return your rows from the 2nd server. OPENROWSET is documented in BOL.If you are using the linked server then you can reference the remote table with the syntax instancename.dbname.owner.tablenameIf you are using OPENROWSET, then the OPENROWSET will take the place of the tablename on the remote server in your from clause.Hope this helps.Thanks,Rich |
 |
|
|
stephenbirdsall
Starting Member
2 Posts |
Posted - 2007-02-08 : 04:52:09
|
| Thanks for your help - I'll go and look it up and let you know how it goes.Stephen |
|
|
|
|
|
|
|
|
Data transfer with possible duplicates in target