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.
Showing posts with label extract. Show all posts
Showing posts with label extract. Show all posts
Friday, March 23, 2012
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
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
Subscribe to:
Posts (Atom)