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 |
|
elegba
Starting Member
1 Post |
 Posted - 2006-09-18 : 14:31:01
|
| Ok say we've got two databases with two tables and we need to transfer data from one to another. However, the data structure in sourceDB is different to targetDB. Better to illustrate: sourceDB tablesource PIN SYSTEM HEADER PROPERTY VALUE STATUS 1000 AF ADDRESS DETAILS LINE 1 The Grange Active 1000 AF ADDRESS DETAILS LINE 2 69 Tintagel Way 1000 AF ADDRESS DETAILS LINE 3 Woking 1001 AF ADDRESS DETAILS LINE 1 50 Active 8888 AF ADDRESS DETAILS LINE 2 Evans Way 8888 AF ADDRESS DETAILS LINE 3 Islington 8888 AF ADDRESS DETAILS 8888 AF ADDRESS DETAILS 8888 AF ADDRESS DETAILS Now each matching PIN on the multiple rows in sourceDB above relates to one customer. In order to extract the relevant info for an address for instance for each customer, we need to know the unique PIN, header, property and value fields. So I created this piece of SQL which gives me the result in targetDB: INSERT into targetDB.dbo.tabletarget (address1) SELECT value FROM sourceDB.dbo.tablesource WHERE pin = 1000 AND header ='address details' AND property = 'line 1' targetDB tabletarget PIN ADDRESS1 ADDRESS2 ADDRESS3 SOURCE The Grange Fine, I've proved I can extract a specific field, but I need to do this for all fields and all customers. Any ideas? |
|
|
shebert
Yak Posting Veteran
85 Posts |
Posted - 2006-09-18 : 17:12:14
|
| Are you trying to put all addresslines on one row?select pin,case when property=line1 then value end as address1,case when property=line2 then value end as address2,case when property=line3 then value end as address3,so onSteve |
 |
|
|
|
|
|
Data transfer between databases different structur