duplicate in text file

I have my SQL server reads a text file into a temp table, then it copies
that data over to a main table, but it looks to see if a particular filed
has a certain value, if it does, it updates that record instead of creating
a duplicate.
But, what I am finding is that if the text file has duplicate records in it,
it will duplicate into the main table.
Any ideas on how I can weed out duplicates from the text file that I am
importing?
You can add an intermediate process to remove the duplicates after they are
loaded into the temp table and before perorming the insert or the update to
the main table.
"Johnfli" wrote:

> I have my SQL server reads a text file into a temp table, then it copies
> that data over to a main table, but it looks to see if a particular filed
> has a certain value, if it does, it updates that record instead of creating
> a duplicate.
> But, what I am finding is that if the text file has duplicate records in it,
> it will duplicate into the main table.
> Any ideas on how I can weed out duplicates from the text file that I am
> importing?
>
>
|||Can't you insert everything into the temp table -- the do the SELECT
DISTINCT at the temp table? prior to entering into the main table
Jack Vamvas
__________________________________________________ ________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Johnfli" <john@.ivhs.us> wrote in message
news:%23SPhtYdLGHA.2216@.TK2MSFTNGP09.phx.gbl...
> I have my SQL server reads a text file into a temp table, then it copies
> that data over to a main table, but it looks to see if a particular filed
> has a certain value, if it does, it updates that record instead of
creating
> a duplicate.
> But, what I am finding is that if the text file has duplicate records in
it,
> it will duplicate into the main table.
> Any ideas on how I can weed out duplicates from the text file that I am
> importing?
>

duplicate in text file

I have my SQL server reads a text file into a temp table, then it copies
that data over to a main table, but it looks to see if a particular filed
has a certain value, if it does, it updates that record instead of creating
a duplicate.
But, what I am finding is that if the text file has duplicate records in it,
it will duplicate into the main table.
Any ideas on how I can weed out duplicates from the text file that I am
importing?You can add an intermediate process to remove the duplicates after they are
loaded into the temp table and before perorming the insert or the update to
the main table.
"Johnfli" wrote:
> I have my SQL server reads a text file into a temp table, then it copies
> that data over to a main table, but it looks to see if a particular filed
> has a certain value, if it does, it updates that record instead of creating
> a duplicate.
> But, what I am finding is that if the text file has duplicate records in it,
> it will duplicate into the main table.
> Any ideas on how I can weed out duplicates from the text file that I am
> importing?
>
>|||Can't you insert everything into the temp table -- the do the SELECT
DISTINCT at the temp table? prior to entering into the main table
--
Jack Vamvas
__________________________________________________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Johnfli" <john@.ivhs.us> wrote in message
news:%23SPhtYdLGHA.2216@.TK2MSFTNGP09.phx.gbl...
> I have my SQL server reads a text file into a temp table, then it copies
> that data over to a main table, but it looks to see if a particular filed
> has a certain value, if it does, it updates that record instead of
creating
> a duplicate.
> But, what I am finding is that if the text file has duplicate records in
it,
> it will duplicate into the main table.
> Any ideas on how I can weed out duplicates from the text file that I am
> importing?
>

duplicate in text file

I have my SQL server reads a text file into a temp table, then it copies
that data over to a main table, but it looks to see if a particular filed
has a certain value, if it does, it updates that record instead of creating
a duplicate.
But, what I am finding is that if the text file has duplicate records in it,
it will duplicate into the main table.
Any ideas on how I can weed out duplicates from the text file that I am
importing?You can add an intermediate process to remove the duplicates after they are
loaded into the temp table and before perorming the insert or the update to
the main table.
"Johnfli" wrote:

> I have my SQL server reads a text file into a temp table, then it copies
> that data over to a main table, but it looks to see if a particular filed
> has a certain value, if it does, it updates that record instead of creatin
g
> a duplicate.
> But, what I am finding is that if the text file has duplicate records in i
t,
> it will duplicate into the main table.
> Any ideas on how I can weed out duplicates from the text file that I am
> importing?
>
>|||Can't you insert everything into the temp table -- the do the SELECT
DISTINCT at the temp table? prior to entering into the main table
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Johnfli" <john@.ivhs.us> wrote in message
news:%23SPhtYdLGHA.2216@.TK2MSFTNGP09.phx.gbl...
> I have my SQL server reads a text file into a temp table, then it copies
> that data over to a main table, but it looks to see if a particular filed
> has a certain value, if it does, it updates that record instead of
creating
> a duplicate.
> But, what I am finding is that if the text file has duplicate records in
it,
> it will duplicate into the main table.
> Any ideas on how I can weed out duplicates from the text file that I am
> importing?
>

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....

DTS using #temp tables

I have a rather complex SP that creates a #temp table and then populates tha
t
table using several select/inserts/update statements. I need the results of
this #table to be exported to external text file nightly so that our
mainframe FTPs can grab it.
DTS apparently wont let me use #temp tables. I get invalid object errors.
Ive heard of global ##temp, but even changing the table all the references
to be ##table isn’t solving the problem.
Is there commands I can place in the SP to create the external file without
using DTS, or can I define the #temp table in a way that DTS can see it?
Help appreciated
--
JP
.NET Software DeveloperHi
This seems similar to http://tinyurl.com/82fqd
Temporary tables have a limited scope see the section in:
http://msdn.microsoft.com/library/d...r />
_4hk5.asp
Depending on what you are trying to do, you may not require the temporary
table or you could use a permanent table that you clear down before each run
.
Alternatively using a derived table or use of the CASE statement may be
possible options.
John
"JP" wrote:

> I have a rather complex SP that creates a #temp table and then populates t
hat
> table using several select/inserts/update statements. I need the results o
f
> this #table to be exported to external text file nightly so that our
> mainframe FTPs can grab it.
> DTS apparently wont let me use #temp tables. I get invalid object errors.
> Ive heard of global ##temp, but even changing the table all the reference
s
> to be ##table isn’t solving the problem.
> Is there commands I can place in the SP to create the external file withou
t
> using DTS, or can I define the #temp table in a way that DTS can see it?
> Help appreciated
> --
> JP
> .NET Software Developer
>