MS SQL Server - Database transfer between databases with differing structures

Author

Database transfer between databases with differing structures

elegba21@yahoo.co.uk

2006-10-24, 6:29 pm

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 PR
OPERTY 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 ADDRESS
2 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?

Roy Harvey

2006-10-24, 6:29 pm

If all three lines will always exist for every PIN, then one approach
that will probably serve is:

INSERT targetDB.dbo.tabletarget
(PIN, address1, address2, address3)
SELECT A1.PIN,
A1.Value as Address1,
A2.Value as Address2,
A3.Value as Address3
FROM tablesource as A1
JOIN tablesource as A2
ON A1.PIN = A2.PIN
JOIN tablesource as A3
ON A1.PIN = A3.PIN
WHERE A1.header = 'ADDRESS DETAILS'
AND A1.property = 'LINE 1'
AND A2.header = 'ADDRESS DETAILS'
AND A2.property = 'LINE 3'
AND A3.header = 'ADDRESS DETAILS'
AND A4.property = 'LINE 3'

However, if you ever have the possibility of only one or two lines out
of the tree the query needs to allow for that. Which is more
complicated, along the lines of:

INSERT targetDB.dbo.tabletarget
(PIN, address1, address2, address3)
SELECT K.PIN,
A1.Value as Address1,
A2.Value as Address2,
A3.Value as Address3
FROM (select PIN from tablesource
where header = 'ADDRESS DETAILS'
and property IN
('LINE 1', 'LINE 2', 'LINE 3')) as K
LEFT OUTER
JOIN tablesource as A1
ON K.PIN = A1.PIN
AND A1.header = 'ADDRESS DETAILS'
AND A1.property = 'LINE 1'
LEFT OUTER
JOIN tablesource as A2
ON K.PIN = A2.PIN
AND A2.header = 'ADDRESS DETAILS'
AND A2.property = 'LINE 2'
LEFT OUTER
JOIN tablesource as A3
ON K.PIN = A3.PIN
AND A3.header = 'ADDRESS DETAILS'
AND A3.property = 'LINE 3'

Roy Harvey
Beacon Falls, CT

On 18 Sep 2006 10:59:38 -0700, elegba21@yahoo.co.uk wrote:

>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 PR
OPERTY 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 ADDRESS
2 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?

Arnie Rowland

2006-10-24, 6:29 pm

Something like this is more what you want, assuming that PIN, SYSTEM, and HEADER are columns in both tables.

But you weren't clear about if your were going to INSERT new rows with just this single value, OR if you were going to UPDATE existing rows. I've assumed UPDATE.

UPDATE t
SET Address1 = s.[Value]
FROM sourceDB.dbo.tablesource s
JOIN targetDB.dbo.tabletarget t
ON ( s.[PIN] = t.[PIN]
AND s.[System] = t.[System]
AND s.[Header] = t.[Header]
)

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

<elegba21@yahoo.co.uk> wrote in message news:1158602378.410334.295010@i42g2000cwa.googlegroups.com...
> 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?
>

Sponsored Links