Single complex INSERT or INSERT plus UPDATE

[Thomas R. Hummel]

Hello,

I am writing a stored procedure that will take data from several
different tables and will combine the data into a single table for our
data warehouse. It is mostly pretty straightforward stuff, but there is
one issue that I am not sure how to handle.

The resulting table has a column that is an ugly concatenation from
several columns in the source. I didn't design this and I can't hunt
down and kill the person who did, so that option is out. Here is a
simplified version of what I'm trying to do:

CREATE TABLE Source (
grp_id INT NOT NULL,
mbr_id DECIMAL(18, 0) NOT NULL,
birth_date DATETIME NULL,
gender_code CHAR(1) NOT NULL,
ssn CHAR(9) NOT NULL )
GO
ALTER TABLE Source
ADD CONSTRAINT PK_Source
PRIMARY KEY CLUSTERED (grp_id, mbr_id)
GO
CREATE TABLE Destination (
grp_id INT NOT NULL,
mbr_id DECIMAL(18, 0) NOT NULL,
birth_date DATETIME NULL,
gender_code CHAR(1) NOT NULL,
member_ssn CHAR(9) NOT NULL,
subscriber_ssn CHAR(9) NOT NULL )
GO
ALTER TABLE Destination
ADD CONSTRAINT PK_Destination
PRIMARY KEY CLUSTERED (grp_id, mbr_id)
GO

The member_ssn is the ssn for the row being imported. Each member also
has a subscriber (think of it as a parent-child kind of relationship)
where the first 9 characters of the mbr_id (as a zero-padded string)
match and the last two are "00". For example, given the following
mbr_id values:

12345678900
12345678901
12345678902
11111111100
22222222200

They would have the following subscribers:

mbr_id subscriber mbr_id
12345678900 12345678900
12345678901 12345678900
12345678902 12345678900
11111111100 11111111100
22222222200 22222222200

So, for the subscriber_ssn I need to find the subscriber using the
above rule and fill in that ssn.

I have a couple of ideas on how I might do this, but I'm wondering if
anyone has tackled a similar situation and how you solved it.

The current system does an insert with an additional column for the
subscriber mbr_id then it updates the table using that column to join
back to the source. I could also join the source to itself in the first
place to fill it in without the extra update, but I'm not sure if the
extra complexity of the insert statement would offset any gains from
putting it all into one statement. I plan to test that on Monday.

Thanks for any ideas that you might have.

-Tom.

[Erland Sommarskog]

Re: Single complex INSERT or INSERT plus UPDATE

Thomas R. Hummel (tom_hummel@hot mail.com) writes:[color=blue]
> The member_ssn is the ssn for the row being imported. Each member also
> has a subscriber (think of it as a parent-child kind of relationship)
> where the first 9 characters of the mbr_id (as a zero-padded string)
> match and the last two are "00". For example, given the following
> mbr_id values:[/color]

I am afraid that I will have to repeat the standard recommendations
(which you as a regular participant of the group should know by now :-)

o CREATE TABLE statements for the tables (OK, those we got)
o INSERT statements for sample data.
o The desired result given the sample data.

It might be that it's a late Friday night, and I am tired after a working
week, but I could not make out where this subscriber mbr_id comes from;
there was no such column in the table. Nor did I understand how the
SSNs came into the picture.

And it should come us no surprise that I don't like guessing.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Thomas R. Hummel]

Re: Single complex INSERT or INSERT plus UPDATE

Sorry, it was a late Friday for all of us :-)

Using the above CREATE TABLE statements:

INSERT INTO Source VALUES (1, 12345678900, '1968-02-02', 'M',
'111111111')
INSERT INTO Source VALUES (1, 12345678901, '1971-04-11', 'M',
'222222222')
INSERT INTO Source VALUES (1, 12345678902, '2001-10-03', 'F',
'333333333')
INSERT INTO Source VALUES (1, 11111111100, '1974-08-15', 'F',
'444444444')
INSERT INTO Source VALUES (1, 22222222200, '1974-09-22', 'F',
'555555555')
GO

I would like the destination table filled like this:

grp_id mbr_id birth_date gender_code member_ssn
subscriber_ssn
1 12345678900 1968-02-02 M 111111111 111111111
1 12345678901 1971-04-11 M 222222222 111111111
1 12345678902 2001-10-03 F 333333333 111111111
1 11111111100 1974-08-15 F 444444444 444444444
1 22222222200 1974-09-22 F 555555555 555555555

Since the first 9 characters of the mbr_id for the first three rows are
the same, they all of the same subscriber, which is the one that has
"00" as the last two characters. In other words, the first 9 characters
of the mbr_id are really more of a family ID, while the last two
differentiate the members of the family, with the primary family member
having "00". They need that primary family member's SSN included in the
destination table.

Right now I am planning to have an intermediate table anyway, so I will
either split up the mbr_id into those two parts so that I can easily
join the table to itself to fill the destination table, or I will just
fill in a new column (subscriber_mbr _id) in the intermediate table with
the first 9 characters plus a hard-coded "00" and use that to join the
table to itself. BTW, there will ALWAYS be a record that ends in "00"
for each SUBSTRING(mbr_i d, 1, 9). In other words, if there is a mbr_id
of 12345678901 then there is guaranteed to be a mbr_id of 12345678900
somewhere in the table.

Thanks,
-Tom.

[Erland Sommarskog]

Re: Single complex INSERT or INSERT plus UPDATE

Thomas R. Hummel (tom_hummel@hot mail.com) writes:[color=blue]
> I would like the destination table filled like this:
>
> grp_id mbr_id birth_date gender_code member_ssn
> subscriber_ssn
> 1 12345678900 1968-02-02 M 111111111 111111111
> 1 12345678901 1971-04-11 M 222222222 111111111
> 1 12345678902 2001-10-03 F 333333333 111111111
> 1 11111111100 1974-08-15 F 444444444 444444444
> 1 22222222200 1974-09-22 F 555555555 555555555[/color]

Here's way to skin the cat:

INSERT Destination (grp_id, mbr_id, birth_date, gender_code, member_ssn,
subscriber_ssn)
SELECT a.grp_id, a.mbr_id, a.birth_date, a.gender_code, a.ssn,
b.ssn
FROM Source a
JOIN Source b ON b.grp_id = a.grp_id
AND b.mbr_id = floor(a.mbr_id / 100) * 100


Since mdr_id was declared as numeric, I used mathematical functrions
rather than string functions.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Thomas R. Hummel]

Re: Single complex INSERT or INSERT plus UPDATE

Erland,

Thanks for the SQL. I used something similar to that and it worked
well.

Thanks,
-Tom.