Upload csv Files Using SQL Server OpenRowSet Function

Question

I need to upload multiple files (file1, file2, file3...) into tables (Table1, table2, table3...) in a sql server DB using OpenRowset function. All the files are kept in C:\download I use the following query which works fine.

INSERT INTO dbo.Table1
SELECT * from OpenRowset('MSDASQL','Driver={Microsoft Text Driver (*.txt;*.csv)};DefaultDir=C:\download;','select * from File1.csv' )

The question is how to pass the file name and table name as parameter.

Thanks Tony for your answer. I have put the sql in a stored procedure as follows.But it is much slower than the original hard coded file, and table names.Any suggestion to make it run faster.

ALTER proc [dbo].[ImportFiles]

  @FilePath varchar(100) ,
  @FileName varchar(100),  
  @TableName varchar(250)
AS
BEGIN
DECLARE @SqlStmt nvarchar(max) 
DECLARE @ErrorCode int

SET @SqlStmt='Truncate table dbo.[' + @TableName +']'
EXEC(@SqlStmt);
-- i COULD PUT TRUNCATE statement in the sate statement as insert just before INSERT INTO. 
set @SqlStmt=N'
INSERT INTO '+@TableName+N'
select *
from openrowset(''MSDASQL''
               ,''Driver={Microsoft Access Text Driver (*.txt, *.csv)}; 
                    DefaultDir='+@FilePath+N'''
               ,''select * from "'+@FileName+N'"'')'

  EXEC(@SqlStmt);

Is there a reason you have to use this method to import the data? If you have SSIS available that might be a better way to perform a multiple file import. — Tony
– Tony, Commented Sep 6, 2011 at 12:33
The reason basically is I do not know much about SSIS I tried to used but I did not succeed. — rao
– rao, Commented Sep 7, 2011 at 21:19
Dynamic SQL is generally slower but by what magnitude are you seeing it slow down? Is it twice as slow, or more, and will that really matter? If the whole process takes minutes then I wouldn't worry, taking hours might be problem but how often do you perform the import? — Tony
– Tony, Commented Sep 9, 2011 at 13:00

Tony · Accepted Answer · 2011-09-06 12:46:47Z

2

You will have to build your query using dynamic SQL to pass a parameter in to the OPENROWSET function. The code to build the dynamic SQL can then be placed in a stored procedure to make it easier to call.

The code to create the SQL dynamically would be something like:

DECLARE @tableName varchar(10);
DECLARE @importFile varchar(10);
SET @tableName = 'Table1'
SET @importQuery= 'File1.csv';
EXEC( 'INSERT INTO ' + @tableName + 'SELECT * from OpenRowset(''MSDASQL'',''Driver={Microsoft Text Driver (*.txt;*.csv)};DefaultDir=C:\download;'',''SELECT * FROM ' + @importFile + ''' )'

NOTE: You'll have to check the quotes are correct, I don't have access to SQL server at the moment to check the syntax.

edited Sep 6, 2011 at 12:46

answered Sep 6, 2011 at 12:40

Tony

10.4k3 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Upload csv Files Using SQL Server OpenRowSet Function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related