BULK INSERT 2

[RJ5]

The syntax for BULK INSERT IS..

BULK INSERT server.dbo.table FROM 'data_file'
WITH (FIELDTERMINATOR = '"',.....)

Can I replace 'data_file' with a variable (varchar type)?

I want to create a stored procedure for bulk insert but the 'data_file' should be varying depending on user input. Is there another way to import text files to SQL 7 if bulk insert doesnt work?

Thanks.

 

[tlbroadbent]

Yes, you can use a variable name and build a dynamic SQL statment which can be Executed.

declare @sql varchar(1024)
Set @sql="BULK INSERT server.dbo.table FROM '" + @data_file + "' WITH (FIELDTERMINATOR = '"',.....)"


Exec (@sql) OR exec sp_executesql @sql Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[RJ5]

Thank Terry.

 

[krotha]

Hi ,
I have similar situation like this. I created Bulk Insert statement in my procedure and I ran the procedure it says

Could not bulk insert because file 'c:/temp/file.txt' could not be opened.

I am running this procedure from client machine and file is located in client machine only. Is the procedure has to be run from server? What settings I need to be done while running from client. I even have administrative privileges for all files still I am getting this error.

My procedure is like this.
CREATE procedure data_load_bulk_insert
@db_table varchar(255),@data_file varchar(255) as
declare @sql varchar(1024)
set @sql= "BULK INSERT "+@db_table+" FROM'" +@data_file+"'
WITH (FIELDTERMINATOR= ',')"
Execute (@sql)
GO

Thanks For your help.

 

[tlbroadbent]

When you run a query, even if you are sitting at a client PC, the query will run in SQL Server on the server. The C: drive seen by SQL Server is the one on the server. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[krotha]

This works fine if i have olny one file in the directory. Actually I want multiple files load into multiple tables dynamically.
For Example: I have emp.txt, dept.txt, owner.txt 3 files in my directory each file goes to emp,dept,owner tables in sql server. so my procedure(above) parameters has to take 'db_table' as emp and 'data_file' as emp.txt and load into emp table. Like wise dept and owner.Is it possible through Bulk Insert. If so Can please tell me how can i do that.

Thanks a lot.

 

[tlbroadbent]

If your procedure works for one file, just execute for the other two.

exec data_load_bulk_insert 'emp','c:\import\emp.txt'
exec data_load_bulk_insert 'dept','c:\import\dept.txt'
exec data_load_bulk_insert 'owner','c:\import\owner.txt'

Does this answer your question or is there something more that I am missing? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[krotha]

In this case I know there are 3 files, but most of the cases I don't know how many files will be there. so I want to put the procedure in a loop and execute the procedure with that number of files. sometimes I may have two files(emp.txt, emp1.txt) that goes to single table emp.

 

[tlbroadbent]

You can use the undocumented stored procedure, xp_fileexist, to determine if a file exists. If it does, execute the SP for the file.

Declare @rc int

Exec master.dbo.xp_fileexist "c:\import\emp.txt",@rc Output
If @rc=1
Begin
exec data_load_bulk_insert 'emp','c:\import\emp.txt'
End

Repeat for other files. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[krotha]

I think I didn't specify my problem clearly here I am giving more details. You are hardcoding the table name and file names to check file exist or not.What I want is I have to check the existing files in the directory I should be able call the procedure once and pass all the array of table names and file names in a single call. How can I pass an array or table structure into a stored procedure. I want to call this procedure from my visual basic program by passing the 'table_names' array and 'file_names' array. so basically I want to pass the multiple parameters to the stored procedure in a single call.

Thanks for your help. Please let me know If it is not clear.

 

[tlbroadbent]

Let's be clear about this, the calls to the stored procedures were examples only. Although the examples are hard coded, you can modify the execute statements to use parameters. I left it to you to do that.

SQL Server doesn't provide much in the way of access to the file system. You'd be better off checking for the files in a VB program and creating the calls to the stored procedure.

You can't pass an array to a stored procedure. You could pass a delimited string and have SQL parse it. You can find a couple of downloadable scripts for parsing at SWYNK.com.

http://www.swynk.com/sqlscripts/misc7.asp

You might want to consider using DTS for this project. It can do bulk inserts, check for files and you can even write VB code. Dareen Green provided a couple of examples at SWYNK that you might be able to modify for you project.

http://www.swynk.com/friends/green/dtsdirlooppkg.asp

http://www.swynk.com/friends/green/dtsdirloopstep.asp

You can even execute a DTS package from VB.

http://www.sqldts.com/article.asp?nav=1,6,208,7

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.