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 |
|
shudd
Starting Member
5 Posts |
 Posted - 2007-04-18 : 16:25:43
|
| Firstly I am not a programmer; and this is the first time I have used this forumI am working with some friends who have been assisting.I am developing a product that is importing data into a sequel data base, and the data is being recorded in real time but posted at minute intervals. The data is then being exported to a local flat file again at minute intervals each row containing about 50 columns.Currently the whole file is being imported across the internet and it all works fine. I am using analysis services to look at the data every two minutes. As the file grows this time becomes longer. What I want to do is only import the latter portion of the flat file, say the last 144 rows there are 1440 rows per day.What is the most efficient way to do this? Duplicated records would be a disaster.Ideally the number of rows to be collected would be variable not fixed at 1440, these rows would be added to the main table that will probably contain up to 1 years worth of data i.e. 1440 rows * 365 days with approx 50 columns.Any comments to point me in the right direction would be appreciated.Steve |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2007-04-18 : 16:32:11
|
| 1440 rows should import very quickly so I have 2 suggestions for you. BCP is a great way to do this type of thing, it's fast and you can call it from a command line easy enough or from a stored procedure. Typically it's a good idea to pull your data into a staging area from the file, process it (ie remove dupes) then do your insert into your production tables. 1440 or even a million rows should go quite quickly this way. now if you insist that you need to pull the last x rows there are 2 things I would look at. First, you need a way to figure out what row of the last file you imported. So if you pulled in your file last time and you had 10 rows, you need to start next time on row 11. Easy enough to get the count. Once you have that you can recall bcp and use the -F (first row) parameter to tell BCP where to start importing next time. By default it's one but you can accomplish what you're looking for this way.Mike"oh, that monkey is going to pay" |
 |
|
|
shudd
Starting Member
5 Posts |
Posted - 2007-04-18 : 16:45:21
|
| Thankscurrently two days worth of data is taking about 42 seconds to pull in to the sequal database via a VPN connection, would you recomend the sorting to be done on the remote site or pull the data into the server and then carry out your recomendationsSteve |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2007-04-18 : 16:56:23
|
| I would say that depends on where your time constraint is. There's lots of "what ifs" around that in my opinion. But I would say if you can get the data into the file already sorted you're ahead of the game.Mike"oh, that monkey is going to pay" |
|
|
|
|
|
|
realtime flat file transfer