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 |
|
flanz_g
Starting Member
22 Posts |
 Posted - 2007-01-16 : 11:01:01
|
I need to append data to the existing Table1 from a .txt file stored in a link e.g. "http://xx00xx0123.abcdef.net/ABC_REPORTS/DATA/Table1.csv"(Columns in both tables are identical)This .csv contains a rolling 7 days of stats. which means if it is added every Morning 6 of those Days will have been added before and must be Deleted. I would like to schedule an automatic procedure to create a temp table in the server every day and a script removing duplicates.I have a few questions:*) Can I shedule from the Enterprise Console to read/create table from the above link to do this?**) I heard I need an SQL agent. If so why and what is it?***) Is it better to append data and then script removing Duplicates, or is it better to import into a temp table run comparison between the 2, Delete from Temp what is Common and then append whats left of the Temp to the Table1?****) Please could someone paste a sample of CREATE TABLE from a http link like the one above?Thanks for your help,Gezza |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-01-16 : 11:08:19
|
| ok, your easiest solution to this problem would be to create a DTS package that imports the data from CSV and puts in the destination table. Google DTS for more info on what this is. http://www.sqldts.com is a good site for help.To answer your specific questions:1. Yes, you can schedule from the EM2. SQL Agent is one way you can schedule the job (the easiest). SQL Agent is a job scheduler that is bundled with SQL Server. It is like CRON (unix/linux) or the Task Scheduler (Windows).3. I would load a staging table with all data from your CSV, then insert from the staging table to the final table only the rows that need to be inserted. after completion you can truncate the staging table. 4. I don't know what you are asking for.-ec |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-01-16 : 11:19:59
|
| also, DTS supports an FTP transfer task. Can the table1.csv be retrieved via FTP or do you have to use HTTP? Using HTTP could be a little more problematic, atleast from a DTS perspective.Here is an article on the DTS FTP task http://www.sqldts.com/302.aspx-ec |
|
|
|
flanz_g
Starting Member
22 Posts |
Posted - 2007-01-17 : 08:51:30
|
| Great Answers! Thank you. |
|
|
|
flanz_g
Starting Member
22 Posts |
Posted - 2007-01-17 : 10:20:41
|
quote:
Originally posted by eyechart
ok, your easiest solution to this problem would be to create a DTS package that imports the data from CSV and puts in the destination table. Google DTS for more info on what this is. http://www.sqldts.com is a good site for help.To answer your specific questions:1. Yes, you can schedule from the EM2. SQL Agent is one way you can schedule the job (the easiest). SQL Agent is a job scheduler that is bundled with SQL Server. It is like CRON (unix/linux) or the Task Scheduler (Windows).3. I would load a staging table with all data from your CSV, then insert from the staging table to the final table only the rows that need to be inserted. after completion you can truncate the staging table. 4. I don't know what you are asking for.-ec
Add4 Could you paste in here a sample of TRANSACT SQL of creating a staging table from an http..csv link such as the one I quoted above? |
|
|
|
|
|
|
|
|
How to Update a Table from a link