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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-10-13 : 08:21:43
|
| Allen writes "Hi,At your earliest convenience can you tell me if it is possible to do a Bulk Insert into a database table from files that exist in a folder on another server. These files do not have a .dbf extension yet they are a .dbf format and can be opened in Visual FoxPro without a problem. We want the ability to insert each file into a Master table that we created in SQL Server in order to house this data. I thought creating a DTS Package might do the trick but I can't seem to point SQL Server to this folder where all the files reside. Do I need to write a VB Script and loop through the entire folder inserting each table file in SQL Server or is there a more efficient way of doing this? Thank you in advance for your attention to this matter." |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-13 : 12:35:12
|
| To use BULK INSERT command, the file MUST exist on the database server. If you want to do this using DTS, just copy the files over to the database server, then run the import. You can do the copy using xp_cmdshell in an Execute SQL Task.Tara |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-10-13 : 17:52:00
|
| The files don't have to be on the server but they must be available to the sql server account via a share.If you give full control to everyone to your folder then you should be able to bulk insert. After testing that reduce the permissions to see if it still works.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-13 : 17:59:02
|
| I mistyped in my post. It should say the file must exist where the database server can get to it. The point that I was trying to make is that when you run the command that it is from the server's perspective. Whereas when you use DTS, it is from the client's perspective.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-10-14 : 11:48:52
|
| Well I would imagine it would be better on the box...just to eliminate any network traffic...plus it would probably be faster that way...Any downside to that?Brett8-) |
|
|
|
|
|
|
Bulk Insert