October 28, 2006 at 9:39 am
Hi,
Originally had 2 tables, fullsource and ssotarget. I did the following extract on fullsource due to its irregular schema and inserted into target:
INSERT SSOTARGET (pin, address1, address2, address3, MemberNo, Tel1, Tel2, Tel3, Tel4, DOB, Email, IDNumber, Title, Initials, Firstname, Surname, STATUS)
SELECT
PIN,
MAX(CASE WHEN HEADER = 'ADDRESS DETAILS' AND PROPERTY = 'LINE 1' THEN VALUE ELSE NULL END) AS address1,
MAX(CASE WHEN HEADER = 'ADDRESS DETAILS' AND PROPERTY = 'LINE 2' THEN VALUE ELSE NULL END) AS address2,
MAX(CASE WHEN HEADER = 'ADDRESS DETAILS' AND PROPERTY = 'LINE 3' THEN VALUE ELSE NULL END) AS address3,
MAX(CASE WHEN HEADER = 'ACOMPANY' AND PROPERTY = 'Membership Number' THEN VALUE ELSE NULL END) AS MemberNo,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Preferred method of contact*' THEN VALUE ELSE NULL END) AS Tel1,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Tel number (o/h) e.g. 011 2690000' THEN VALUE ELSE NULL END) AS Tel2,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Mobile number e.g. 0821234567' THEN VALUE ELSE NULL END) AS Tel3,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Fax number e.g. 011 2691000' THEN VALUE ELSE NULL END) AS Tel4,
MAX(CASE WHEN HEADER = 'Date Of Birth' AND PROPERTY = 'eg. 04 Jan 1965' THEN VALUE ELSE NULL END) AS DOB,
MAX(CASE WHEN HEADER = 'Email address' AND PROPERTY = 'Email Address' THEN VALUE ELSE NULL END) AS Email,
MAX(CASE WHEN HEADER = 'ID Number' AND PROPERTY = 'ID Number' THEN VALUE ELSE NULL END) AS IDNumber,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Title' THEN VALUE ELSE NULL END) AS Title,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Initials' THEN VALUE ELSE NULL END) AS Initials,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Firstname' THEN VALUE ELSE NULL END) AS Firstname,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Surname' THEN VALUE ELSE NULL END) AS Surname,
MAX(CASE WHEN HEADER = 'ACOMPANY' AND PROPERTY = 'Membership Number' THEN STATUS ELSE NULL END) AS STATUS
FROM
FULLSOURCE
GROUP BY PIN
Now how I can I reverse this to insert all the values back to tbl FULLSOURCE from maybe an updated SSOTARGET? Tbl FULLSOURCE looks like this:
CREATE TABLE [dbo].[fullsource](
[PIN] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SYSTEM] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Header] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Property] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Value] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ID_Header] [int] NULL,
[ID_Property] [int] NULL
) ON [PRIMARY]
October 30, 2006 at 3:28 am
Something like
INSERT Target
SELECT PIN, <someothercolumns> FROM Source
UNION ALL
SELECT PIN, <someothercolumns> FROM Source
UNION ALL
SELECT PIN, <someothercolumns> FROM Source
N 56°04'39.16"
E 12°55'05.25"
October 30, 2006 at 6:27 am
Hi Peter
Thx for responding. I am not sure from your response that I made myself clear.
Basically, I've already succeeded in extracting from fullsource to target. With the script above. However, now I want to extract back to tbl fullsource. Since tbl fullsource is heavily normalised, I wasn't sure how to go about this. Tbl fullsource looks like this:
tbl fullsource
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 tbl fullsource above relates to
one customer. In order to extract the relevant info for an address for
instance for each customer, my original script above used the unique PIN, header,
property and value fields.
How do I get it back into tbl fullsource maintaining this structure?
October 30, 2006 at 9:25 am
thanks it worked!
🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply