使用 SQL Server OpenRowSet 函数上传 csv 文件

发布于 2024-12-02 22:39:58 字数 1059 浏览 5 评论 0原文

我需要使用 OpenRowset 函数将多个文件（file1、file2、file3...）上传到 sql server DB 中的表（Table1、table2、table3...）。所有文件都保存在C:\download 我使用以下查询效果很好。

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

问题是如何将文件名和表名作为参数传递。

感谢托尼的回答。我已将 sql 放入存储过程中，如下所示。但它比原始硬编码文件和表名慢得多。有什么建议可以使其运行得更快。

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

红尘作伴 2024-12-09 22:39:58

您必须使用动态 SQL 构建查询，以将参数传递给 OPENROWSET 函数。然后可以将构建动态 SQL 的代码放置在存储过程中，以便于调用。

动态创建 SQL 的代码类似于：

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 来检查语法。

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.

回复收藏 0 原文

~没有更多了~