使用SQL Server OpenRowSet函数上载csv文件 [英] Upload csv Files Using SQL Server OpenRowSet Function

问题描述

我需要使用OpenRowset函数将多个文件(file1，file2，file3 ...)上载到sql服务器DB中的表(Table1，table2，table3 ...)中.所有文件都保存在C:\ download 我使用下面的查询工作正常.

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.

谢谢托尼.我将sql放在存储过程中如下，但是它比原始的硬编码文件和表名慢得多，任何建议使其运行得更快.

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);

推荐答案

您将不得不使用动态SQL构建查询，以将参数传递给OPENROWSET函数.然后，可以将用于构建动态SQL的代码放在存储过程中，以使其易于调用.

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.

用于动态创建SQL的代码如下:

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 + ''' )'

注意:您必须检查引号是否正确，我目前无法访问SQL Server来检查语法.

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.

这篇关于使用SQL Server OpenRowSet函数上载csv文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！