insert statement

When I run below statement, I got 3 records insertion.
I only want 1 record added when there was any update on any column on the
source data. I don't want update statement because, I would like to see all
the change from time to time.
Please help,
Culam.
INSERT INTO CUSTOMER_PROFILE_HIST
([CUSTOMER_ID, [RATE], [AGE1], [AGE2])
SELECT src.[CUSTOMER_ID, src.[RATE], src.[AGE1], src.[AGE2]
FROM
CUSTOMER_PROFILE src
LEFT OUTER JOIN CUSTOMER_PROFILE_HIST dst
ON src.[CUSTOMER_ID] = dst.[CUSTOMER_ID]
WHERE
ISNULL(src.[RATE], 0) <> ISNULL(dst.[RATE],0)
OR ISNULL(src.[AGE1], 0) <> ISNULL(dst.[AGE1], 0)
OR ISNULL(src.[AGE2], 0) <> ISNULL(dst.[AGE2], 0)try using inner join.. Just a guess
--
"culam" wrote:

> When I run below statement, I got 3 records insertion.
> I only want 1 record added when there was any update on any column on the
> source data. I don't want update statement because, I would like to see a
ll
> the change from time to time.
> Please help,
> Culam.
> INSERT INTO CUSTOMER_PROFILE_HIST
> ([CUSTOMER_ID, [RATE], [AGE1], [AGE2])
> SELECT src.[CUSTOMER_ID, src.[RATE], src.[AGE1], src.[AGE2]
> FROM
> CUSTOMER_PROFILE src
> LEFT OUTER JOIN CUSTOMER_PROFILE_HIST dst
> ON src.[CUSTOMER_ID] = dst.[CUSTOMER_ID]
> WHERE
> ISNULL(src.[RATE], 0) <> ISNULL(dst.[RATE],0)
> OR ISNULL(src.[AGE1], 0) <> ISNULL(dst.[AGE1], 0)
> OR ISNULL(src.[AGE2], 0) <> ISNULL(dst.[AGE2], 0)|||You will have problems after there are 2 records for the customer in
the history file because one will always be different than the current.
You need to only compare to the latest historical record.|||Thanks Jeff.
Do you know the way to insert 1 record when multiple fields are changed?
My method will insert new records for each changed field.
Lam
"culam" wrote:

> When I run below statement, I got 3 records insertion.
> I only want 1 record added when there was any update on any column on the
> source data. I don't want update statement because, I would like to see a
ll
> the change from time to time.
> Please help,
> Culam.
> INSERT INTO CUSTOMER_PROFILE_HIST
> ([CUSTOMER_ID, [RATE], [AGE1], [AGE2])
> SELECT src.[CUSTOMER_ID, src.[RATE], src.[AGE1], src.[AGE2]
> FROM
> CUSTOMER_PROFILE src
> LEFT OUTER JOIN CUSTOMER_PROFILE_HIST dst
> ON src.[CUSTOMER_ID] = dst.[CUSTOMER_ID]
> WHERE
> ISNULL(src.[RATE], 0) <> ISNULL(dst.[RATE],0)
> OR ISNULL(src.[AGE1], 0) <> ISNULL(dst.[AGE1], 0)
> OR ISNULL(src.[AGE2], 0) <> ISNULL(dst.[AGE2], 0)|||try this.
INSERT INTO CUSTOMER_PROFILE_HIST
([CUSTOMER_ID, [RATE], [AGE1], [AGE2])
SELECT src.[CUSTOMER_ID, src.[RATE], src.[AGE1], src.[AGE2]
FROM
CUSTOMER_PROFILE src
WHERE
not exists( select 1 from CUSTOMER_PROFILE_HIST dst where
src.[CUSTOMER_ID] = dst.[CUSTOMER_ID]
ISNULL(src.[RATE], 0) = ISNULL(dst.[RATE],0)
AND ISNULL(src.[AGE1], 0) = ISNULL(dst.[AGE1], 0)
AND ISNULL(src.[AGE2], 0) = ISNULL(dst.[AGE2], 0)
)|||Thanks, it works.
"culam" wrote:

> When I run below statement, I got 3 records insertion.
> I only want 1 record added when there was any update on any column on the
> source data. I don't want update statement because, I would like to see a
ll
> the change from time to time.
> Please help,
> Culam.
> INSERT INTO CUSTOMER_PROFILE_HIST
> ([CUSTOMER_ID, [RATE], [AGE1], [AGE2])
> SELECT src.[CUSTOMER_ID, src.[RATE], src.[AGE1], src.[AGE2]
> FROM
> CUSTOMER_PROFILE src
> LEFT OUTER JOIN CUSTOMER_PROFILE_HIST dst
> ON src.[CUSTOMER_ID] = dst.[CUSTOMER_ID]
> WHERE
> ISNULL(src.[RATE], 0) <> ISNULL(dst.[RATE],0)
> OR ISNULL(src.[AGE1], 0) <> ISNULL(dst.[AGE1], 0)
> OR ISNULL(src.[AGE2], 0) <> ISNULL(dst.[AGE2], 0)|||On Tue, 9 May 2006 13:32:03 -0700, culam wrote:

>Thanks Jeff.
>Do you know the way to insert 1 record when multiple fields are changed?
>My method will insert new records for each changed field.
Hi Lam,
No, it won't.
It will insert new rows for each existing row in the history table. If
you have three rows in CUSTOMER_PROFILE_HIST, you'll get three
additional rows (or rather: maximum three rows - if any of the existing
history rows happens to match the current rw on all columns, you'll only
get two new rows).
Check JeffB's reply - he hit the nail right on the head.
If yoou need more assitance, then please check out www.aspfaq.com/5006
to find out what additional information yoou need to give to make it
possible for us to help you.
Hugo Kornelis, SQL Server MVP|||All Credit goes to Jeff. I just ex[anded his point of view.
--
"culam" wrote:
> Thanks, it works.
> "culam" wrote:
>|||Unless 3 changed fields actually causes 3 separate inserts. I have seen
this happen (on the application side) where a change to department, salary,
and jobcode actually triggers 3 separate transactions. This is not to
suggest that your post is inaccurate, only that the OP could possibly be
referring to something else here...
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:e23262pmojsjfnt5aph2jdausbrmmv9lcc@.
4ax.com...
> On Tue, 9 May 2006 13:32:03 -0700, culam wrote:
>
> Hi Lam,
> No, it won't.
> It will insert new rows for each existing row in the history table. If
> you have three rows in CUSTOMER_PROFILE_HIST, you'll get three
> additional rows (or rather: maximum three rows - if any of the existing
> history rows happens to match the current rw on all columns, you'll only
> get two new rows).
> Check JeffB's reply - he hit the nail right on the head.
> If yoou need more assitance, then please check out www.aspfaq.com/5006
> to find out what additional information yoou need to give to make it
> possible for us to help you.
> --
> Hugo Kornelis, SQL Server MVP