Question of merging multiple records back into one record.

[XavierExtreme]

I have a table that has become corrupt and I need to merge the records back into one record. I have multiple records that should be recombined and I have hit a brick wall on this.

In my table the data looks like this
part_id Unique c_date m_date Qty ht_num
1 SWA123 6/1/2009 null null 28683
2 SWA123 null null 115 null
3 SWA123 null 6/2/2009 null 28683

This data used to be one single record. I have thousands of these that need merged again. Unfortunately, I only have SQL 2000 at my disposal. The value in the Unique field can only exist one time in the table, which is reall causing some problems.

This is how my table is put together.

CREATE TABLE [dbo].[MyTable] (
   [part_id] [int] IDENTITY (1, 1) NOT NULL ,
   [Unique] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [c_date] [datetime] NULL ,
   [m_date] [datetime] NULL ,
   [Qty] [int] NULL ,
   [ht_num] [int] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MyTable] WITH NOCHECK ADD 
   CONSTRAINT [PK_MyTable] PRIMARY KEY  CLUSTERED 
   ([part_id])  ON [PRIMARY] 
GO

I have tried many different ways to merge these values into one single record and am failing. The values are for traceable parts, so I can not simply delete the duplicate records. Since the parts are recorded in the table every 10 to 15 minutes, the option of restoring a backup can not be done.

Does any one have a good idea that could accomplish this task?
I have spent three days trying to figure this out with no success.

Thank you,
Xavier

[sknake]

I'm very interested in *how* that happened. Do all of your records have only 3 entries ... and each has one field?
Anyway here is one way of going about it.

My test data:

Insert Into MyTable ([Unique], c_date, m_date, qty, ht_num) Values ('SWA123', '6/1/2009', null, null, '28683')
Insert Into MyTable ([Unique], c_date, m_date, qty, ht_num) Values ('SWA123', null, null, '115', null)
Insert Into MyTable ([Unique], c_date, m_date, qty, ht_num) Values ('SWA123', null, '6/2/2009', null, '28683')

Yields:

1	SWA123	2009-06-01 00:00:00.000	NULL	NULL	28683
2	SWA123	NULL	NULL	115	NULL
3	SWA123	NULL	2009-06-02 00:00:00.000	NULL	28683

To combine them:

Select [Unique], Max(c_date) As c_Date, Max(m_date) As m_date, Max(Qty) as Qty, Max(ht_num) As ht_Num
From MyTable
Group By [Unique]

Yields:

SWA123	2009-06-01 00:00:00.000	2009-06-02 00:00:00.000	115	28683

To fix the whole table:

Select [Unique], Max(c_date) As c_Date, Max(m_date) As m_date, Max(Qty) as Qty, Max(ht_num) As ht_Num
Into MyTable2
From MyTable
Group By [Unique]

[XavierExtreme]

Thank you for your reply Scott.

Actually, the rows in this table have 25 columns and the duplicates vary between 2 and 13 for each record.

Excellent idea!

I came up with this just before your reply.

Select
[Unique], Max(cdate) as c_date, max(mdate)as mdate, sum(qty) as qty, max(htnum) as htnum, count([Unique])as repeats
from
(

Select
  MyTable.[Unique], isnull(c_date, '')as cdate 
, isnull(m_date, '') as mdate
, isnull(MyTable.Qty, 0) as qty
, isnull(ht_num, 0) as htnum
from MyTable

) as test
Group by [Unique]

I smacked myself afterwards. I was making this extremely harder than it was.

Actually what happened was an accident.
We were scheduled for a plant power shutdown, which we were prepared for.
Unfortunately, the city shut the power off two days later for two hours. Our battery backup died while the server was performing a scheduled DTS. Of course it was writing to this table and a few others.

Strange that no one called or emailed me when it happened. Found out from the server logs on Monday.

My senior manager has approved getting a UPS monitoring system to shutdown the server automatically.

Thanks for your reply Scott, I really appreciate the quick response.
It is also great to interact with other developers. Where I currently work, I am the Developer, DBA, and Programmer. The only person I have for IT assistance is a Server Admin, he is into hardware and networks only.

I will use your idea from the post, since it is more streamlined than mine.
Thanks for helping to end three days of torment.

Xavier Extreme