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 |
|
abhijeetdighe
Starting Member
24 Posts |
 Posted - 2007-10-02 : 09:58:23
|
| I want to upload .dat file to SQL Server , which may contain at least 8 Lakh records.I also want to perform some calculations such as : File will contain quantity and rate then , amount will be calculated as quantity * rate and if amount < 100000 then 'retail' will be stored in sql server.If amount > 100000 then 'HNI' will be stored.I dont have any ideas about bulk copying.I just tried to use bulk insert.I have heard about DTS , but I dont know anything about it.What should I use so that faster performence is achieved? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-02 : 10:02:37
|
| you have 3 options:1. bulk insert the file into a staging table. perform the data transformation there and import data to the proper tables from there.2. modify the data on the client (a .net) and use SqlBulkCopy class to bulk insert data into the correct tables in sql server.3. use SSIS and crate a package that does the transformation and inserting._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
abhijeetdighe
Starting Member
24 Posts |
Posted - 2007-10-02 : 10:08:38
|
| I dont have any idea about staging table.Will it be faster.I want to do it using stored procedure or programmatically. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-02 : 10:13:35
|
| a staging table is a table that you just load your data into, then perform calculations (or whatever else you need) in there and from there you insert data into correct destination tables. when you're finished you drop the staging table.it's just a temporary table that's all._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
abhijeetdighe
Starting Member
24 Posts |
Posted - 2007-10-02 : 10:26:30
|
| I have file on a remote machine not on SQL Server machine.I am getting error like this -File could not be opened. Operating system error code 5(Access is denied.) |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-02 : 10:41:47
|
| how are you opening it?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-02 : 23:11:12
|
| Sql service account needs permission on data file. |
|
|
|
|
|
|
Bulk Copy