Showing posts with label extract. Show all posts
Showing posts with label extract. Show all posts

Friday, March 23, 2012

Need DTS to overwrite data

Newbie, so please bear with me.

I'm trying to automate a daily extract from our Personnel System to SQL server using DTS.

The Personnel system spits out a TXT file (containing approx 35 columns, 5000 records), which, is then picked up by DTS. I need this to overwrite the existing data, not duplicate it. However, after 1 successful load, if I try again I get a PK constraint error message.

The PK is the employee's PIN, but data in the other columns can change (e.g. Address_1) which is why I need to overwrite the data.

I have thought about importing the TXT file to a Temp Table, dropping the original table, then importing the data that way, but using DTS I cannot drop the table because of the constraints.

Any help greatly appreciated.i usually push data into a staging table, and then write the 'good records' into the fact table, and the 'bad records' get written to a exceptions table.

its pretty straight forward

you may need to fiddle with 'IDENTITY INSERT' in order to not get PK violations--

it just depends on what kindof PK you use.

i write a few views between the staging table and the fact table in order to filter out records that are already in the table, etc

it would be pretty straightforward (if you have a simple PK) in order to do this

basically i just issue a 'TRUNCATE TABLE' statement, and then i run most of my code in ActiveX script

and then when it is done, you fire a stored proc that writes the records from staging to fact.

cheers and good luck|||thanks for the info Aaron - i will give it a try.

Wednesday, March 21, 2012

Need count and subselects

Hello,
i use mysql 4.0.20 and have the following problem:
I have a column with names and want to extract all names which are lets say 3 times in this column. See

Names
foo
bar
bla
foo
bla
bla
foo

In this case mysql shoud output foo and bla, but how to do this??
I tried some combinations of count and something like subselects, but no success.

Could anybody help me please? It drives me crazy.
Thanks, b52hmm, does mysql support having?

group by names
having count(*) = 3|||yea it does,
i will give it a try, but not sure to get it working.

Thanks, bye, b52|||YES it works! :D :D
SELECT KNAME FROM AUFTRAG LEFT JOIN KUNDE ON KNR=AKNR GROUP by KNAME HAVING COUNT(*) =3

Thank you :) , bye, b52sql