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 |
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
 Posted - 2008-06-27 : 11:46:32
|
| We get a csv file every 1 hour into our FTP server and then that file needs to be grabbed and then the data from that file needs to be loaded into multiple SQL tables in a Live database which our users also will be using during that time.I have the following questions:1. Will there be any kind of table locking since it will be done to a LIVE database2. What's the best approach of doing this and how it can be done.Any ideas??Thanks!! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-27 : 16:04:48
|
| Locking shouldn't be an issue.The best approach is to import the data into a staging table. The staging table should match the file layout. Then using T-SQL, move your data from the staging table to the appropriate tables.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2008-07-01 : 17:24:16
|
quote:
Originally posted by tkizer
Locking shouldn't be an issue.The best approach is to import the data into a staging table. The staging table should match the file layout. Then using T-SQL, move your data from the staging table to the appropriate tables.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Thanks for your response, please let me know if the following steps can be followed:1. Using the DTS package will import the data from the file into the staging table2. The staging table will need to be same as file layout3. Using t-sql will load the data from the staging table to the appropriate tablesShall I use t-sql or DTS for step3?Thanks!! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-01 : 17:29:01
|
| T-SQL can be run inside a DTS package via the execute sql task object.I prefer to avoid DTS though as importing data is so easy with bcp.exe or BULK INSERT.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
Load data to multiple SQL tables from a File