Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wshuart
Starting Member
3 Posts |
 Posted - 2009-03-03 : 15:30:09
|
| Hey folks,I have a question/needs some help. I am running Win2k, SQL 2000. I have a csv file that is getting updated with new records every 10 minutes. I'd like to schedule a package/stored procedure to insert the new records to the production table 2-3 times per hour. One of the fields is "Record number" I also have a 'TIME STAMP" so I'd like to run a 'greater than' or newer operation to allow any record numbers greater than the last one in the production table to be inserted.In summary: I have two tables, 1 production 1 temporary, the same fields in both with the same data type. I'd like to insert new records that are greater than/newer the last imported record.Any ideas/code? Thanks for the help!!!!Cheers,WillAny ideas on this? |
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2009-03-03 : 15:47:04
|
| i have once created SSIS Package (SQL 2005) with export to CVS that was run every 15 minutes - from a job - and stored all sql results into file (overriding the old file).this was relatively straightforward.in your case i would try to solve this case with pure SQL and job schedular (Sql server agent) and then create DTS file.best |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-04 : 08:37:58
|
quote:
Originally posted by wshuart
Hey folks,I have a question/needs some help. I am running Win2k, SQL 2000. I have a csv file that is getting updated with new records every 10 minutes. I'd like to schedule a package/stored procedure to insert the new records to the production table 2-3 times per hour. One of the fields is "Record number" I also have a 'TIME STAMP" so I'd like to run a 'greater than' or newer operation to allow any record numbers greater than the last one in the production table to be inserted.In summary: I have two tables, 1 production 1 temporary, the same fields in both with the same data type. I'd like to insert new records that are greater than/newer the last imported record.Any ideas/code? Thanks for the help!!!!Cheers,WillAny ideas on this?
i think what you can do is to use ssis package with SCD task . the Slowly Changing Dimension (SCD) looks for changes in record values based on key field and updates the changed info ,inserts new info & deletes missing info from target table.http://blogs.conchango.com/jamiethomson/archive/2005/06/06/1543.aspx |
|
|
|
wshuart
Starting Member
3 Posts |
Posted - 2009-03-04 : 09:19:51
|
| Any ideas or sample sql that would help me do this? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-04 : 09:22:12
|
| did you check link?that explains it |
|
|
|
wshuart
Starting Member
3 Posts |
Posted - 2009-03-04 : 09:58:31
|
| Visakh16 - Thanks, I just saw the repsonse. I had the window open during a meeting, so I came back and didn't refresh. Yeah, I have SQL 2000... is that jsut part of SQL 2005? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-04 : 10:01:29
|
| Ah...2000 then you cant use ssis. you need to then make use of table which stores each runs max timestamp value from table. for next run, it takes the max date value from this table and extracts those data which comes after this,SELECT columns...FROM TableWHERE timestamp> maxdatevaluethen put it in a staging table and compare this with your final table and do insert/update/delete |
|
|
|
|
|
|
|
|
DTS - File Import check for new records and insert