Modify Index

[Sam G]

Hi all,
I have been left to modify an old unique index, which no longer works,
as the two fields, which were used, are no longer unique. I’ve found two
others that are unique. But as I’m new to this I need some advice first.
What happens if one table inserts from another (the second containing
the index). However if there is a duplicate will the records which are
not duplicates be inserted? How could I insure this?

Thanks

Sam


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

"Sam G" <mo**@spamhole.com> wrote in message
news:3f*********************@news.frii.net...

Hi all,
I have been left to modify an old unique index, which no longer works,
as the two fields, which were used, are no longer unique. I've found two
others that are unique. But as I'm new to this I need some advice first.
What happens if one table inserts from another (the second containing
the index). However if there is a duplicate will the records which are
not duplicates be inserted? How could I insure this?

Thanks

Sam


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Your question isn't completely clear, but I guess you're adding a new
unique index, or possibly a primary key constraint, to a table. If you're
asking what will happen if you try to insert multiple rows which include
duplicate values for those columns, then the whole statement will be
cancelled, and no rows will be inserted.

If that doesn't answer your question, perhaps you could give some more
detail on exactly what you need to do?

Simon

[Sam G]

Yes sorry I don’t think I was that clear!
Basically we have two tables: account_stage and account_live. The
contractors set up an index on account_live, which had a unique
constraint batch_number and sequence_number. The company that sends us
information is no longer able to ensure this uniqueness. I have found
another combination in the existing account_live table (ie Rreg_number
and year_code) this is unique. I have therefore changed the account_live
table to the latter index.

We get two files from this outside company, which is poured, into the
account_stage table. The first file being a daily file the second a
monthly. The monthly is a ‘catch-all’ and would include most of the
information the daily files would have (and fill in the holes missed).

I want to ensure that both the daily and monthly load their information.
The monthly may/will have the same reg_number and year_code which match
some daily files already in the account_live table.

I just want to be able to load the monthly with just those missing
items. Is this feasible?
Many thanks
Sam
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

Sam G <mo**@spamhole.com> wrote in message news:<3f*********************@news.frii.net>...

Yes sorry I don?t think I was that clear!
Basically we have two tables: account_stage and account_live. The
contractors set up an index on account_live, which had a unique
constraint batch_number and sequence_number. The company that sends us
information is no longer able to ensure this uniqueness. I have found
another combination in the existing account_live table (ie Rreg_number
and year_code) this is unique. I have therefore changed the account_live
table to the latter index.

We get two files from this outside company, which is poured, into the
account_stage table. The first file being a daily file the second a
monthly. The monthly is a ?catch-all? and would include most of the
information the daily files would have (and fill in the holes missed).

I want to ensure that both the daily and monthly load their information.
The monthly may/will have the same reg_number and year_code which match
some daily files already in the account_live table.

I just want to be able to load the monthly with just those missing
items. Is this feasible?
Many thanks
Sam
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

It's always best to post CREATE TABLE statements, including keys etc,
but it looks like you need to do this:

insert into dbo.account_live
(column1, column2, ...)
select column1, column2, ...
from dbo.account_stage s
where not exists (
select *
from dbo.account_live l
where s.Rreg_number = l.Rreg_number and
s.year_code = l.year_code)

This assumes that both tables have the same structure (or at least
that they both have Rreg_number and year_code). To speed this up, it
would probably be worth creating an index on the same columns on
account_stage, if you haven't done so already.

Simon

[Sam G]

That's great Simon.
many thanks
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!