Duplicate Values Problem

I trying to insert values from a temporary table into a permanent table. Th
e
problem is the temporary table has duplicate UpdateTime values (issues with
the database used to populate the temporary table) and the UpdateTime is a
primary key in the permanent table.
Is there a way I can remove, or exclude, the duplicate values in the
temporary table before inserting the values into the permanent table?
RUN_SER_NO = 635 is the bad actor in this case. Typically the duplicate
value with the larger RUN_SER_NO is the one to keep.
UpdateTime GRADE RUN_SER_NO
11-Aug-04 04:00 AA 634
11-Aug-04 05:00 AA 634
11-Aug-04 06:00 AA 634
11-Aug-04 07:00 VVV 636
11-Aug-04 07:00 VVV 635
11-Aug-04 08:00 VVV 635
11-Aug-04 08:00 VVV 636
11-Aug-04 09:00 VVV 636
11-Aug-04 09:00 VVV 635
11-Aug-04 10:00 VVV 635
11-Aug-04 10:00 VVV 636
11-Aug-04 11:00 VVV 636
11-Aug-04 12:00 VVV 636
11-Aug-04 13:00 VVV 636
11-Aug-04 14:00 VVV 636
11-Aug-04 15:00 VVV 636
Thanks in advance,
Raulselect UpdateTime from table_name group by UpdateTime having count(*) > 1
will give you the duplicates.You can remove them by using this query before
inserting into the permanent table.
"Raul" wrote:

> I trying to insert values from a temporary table into a permanent table.
The
> problem is the temporary table has duplicate UpdateTime values (issues wit
h
> the database used to populate the temporary table) and the UpdateTime is a
> primary key in the permanent table.
> Is there a way I can remove, or exclude, the duplicate values in the
> temporary table before inserting the values into the permanent table?
> RUN_SER_NO = 635 is the bad actor in this case. Typically the duplicate
> value with the larger RUN_SER_NO is the one to keep.
> UpdateTime GRADE RUN_SER_NO
> 11-Aug-04 04:00 AA 634
> 11-Aug-04 05:00 AA 634
> 11-Aug-04 06:00 AA 634
> 11-Aug-04 07:00 VVV 636
> 11-Aug-04 07:00 VVV 635
> 11-Aug-04 08:00 VVV 635
> 11-Aug-04 08:00 VVV 636
> 11-Aug-04 09:00 VVV 636
> 11-Aug-04 09:00 VVV 635
> 11-Aug-04 10:00 VVV 635
> 11-Aug-04 10:00 VVV 636
> 11-Aug-04 11:00 VVV 636
> 11-Aug-04 12:00 VVV 636
> 11-Aug-04 13:00 VVV 636
> 11-Aug-04 14:00 VVV 636
> 11-Aug-04 15:00 VVV 636
> Thanks in advance,
> Raul
>|||How about:
SELECT update_time, grade, run_ser_no
FROM tbl t1
WHERE t1.run_ser_no = ( SELECT MAX( t2.run_ser_no )
FROM tbl t2
WHERE t2.UpdateTime = t1.UpdateTime
AND t1.grade = t2.grade ) ;
Anith|||On Tue, 8 Feb 2005 14:11:04 -0800, Raul wrote:

>I trying to insert values from a temporary table into a permanent table. T
he
>problem is the temporary table has duplicate UpdateTime values (issues with
>the database used to populate the temporary table) and the UpdateTime is a
>primary key in the permanent table.
>Is there a way I can remove, or exclude, the duplicate values in the
>temporary table before inserting the values into the permanent table?
Hi Raul,
SELECT UpdateTime, Grade, Run_ser_no
FROM MyTable AS a
WHERE NOT EXISTS (SELECT *
FROM MyTable AS b
WHERE b.UpdateTime = a.UpdateTime
AND b.Run_ser_no > a.Run_ser_no)
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks to all who replied!
These suggestions are really helpful.
Thanks again,
Raul
"Raul" wrote:

> I trying to insert values from a temporary table into a permanent table.
The
> problem is the temporary table has duplicate UpdateTime values (issues wit
h
> the database used to populate the temporary table) and the UpdateTime is a
> primary key in the permanent table.
> Is there a way I can remove, or exclude, the duplicate values in the
> temporary table before inserting the values into the permanent table?
> RUN_SER_NO = 635 is the bad actor in this case. Typically the duplicate
> value with the larger RUN_SER_NO is the one to keep.
> UpdateTime GRADE RUN_SER_NO
> 11-Aug-04 04:00 AA 634
> 11-Aug-04 05:00 AA 634
> 11-Aug-04 06:00 AA 634
> 11-Aug-04 07:00 VVV 636
> 11-Aug-04 07:00 VVV 635
> 11-Aug-04 08:00 VVV 635
> 11-Aug-04 08:00 VVV 636
> 11-Aug-04 09:00 VVV 636
> 11-Aug-04 09:00 VVV 635
> 11-Aug-04 10:00 VVV 635
> 11-Aug-04 10:00 VVV 636
> 11-Aug-04 11:00 VVV 636
> 11-Aug-04 12:00 VVV 636
> 11-Aug-04 13:00 VVV 636
> 11-Aug-04 14:00 VVV 636
> 11-Aug-04 15:00 VVV 636
> Thanks in advance,
> Raul
>

DTS with temporary tables

Hello,

I tried to make a DTS to transform data in a text file, I used a Store Procedure that use a temp table (#Resultados) but the DTS give me an error.

I read that in this case I can′t use local temp tables but I can use global temp tables, then I changed in my Store, #Resultados by ##Resultados, bu the result was the same.

My Store is likely to his. Please help me.

INSERT ##Resultados (Planta, Etapa,GrupoEquipo,Equipo,Concepto,Fecha,Guardia,

Valor,idConcepto)
EXEC CalculosDiarios @.Area,@.Reporte,@.FechaIni,@.FechaFin,0

SELECT LEFT(RP.Grupo,3) + LEFT(RP.Equipo,12) + LEFT(RP.SubGrupo,2)
+ LEFT(D.Fecha,8) + D.Valor as Dato
FROM
ReportesPlantilla RP
LEFT JOIN
##Resultados D
ON
RP.Planta = D.Planta
AND RP.Etapa = D.Etapa
AND RP.GrupoEquipo = D.GrupoEquipo
AND RP.Equipo = D.Equipo
AND RP.Concepto = D.Concepto
AND D.Fecha BETWEEN @.FechaIni AND @.FechaFin

What is the error message that you are getting?|||

Hi,

The error is:

Error Source : Microsoft OLE DB Provider for SQL Server

Error Description : Invalid Object Name '##Resultados'

When I run the Store in Query Analizer everything run OK.

|||

That is what I thought was going on. Sometimes when working with the ##tempTables in DTS you will need to work around it. What I often do is something like this:

Create the ##temp table in query analyzer Create the DTS steps Then drop the ##temp table in query analyzer|||Great, It worked, thanks a lot....