将数据从txt文件导入SQL-Server表的功能

I am quite new with SQL Server. I have got a .txt file with values separated like this:

我是SQL Server的新手。我有一个.txt文件，其值分隔如下：

20120101;001;2;0;0;0;0;0;8
20120102;002;3;0;0;0;0;0;8
20120103;003;4;0;0;0;0;0;8
...

This file is in a server and I need to import this file everyday at the same hour autommaticaly, and update the changes (the file is changing every day). I was thinking in making a function to do it but I don't know if it is even possible.

此文件位于服务器中，我需要在autommaticaly的同一时间每天导入此文件，并更新更改（文件每天都在更改）。我正在考虑制作一个功能，但我不知道它是否可能。

Any help would be appreciated. Thanks in advance.

任何帮助，将不胜感激。提前致谢。

2 个解决方案

#1

insert into your_table
  SELECT a.*  
  FROM OPENROWSET( BULK 'c:\input.csv', FORMATFILE = 'c:\Format.fmt') AS a;

Exaample Format.fmt

示例Format.fmt

9.0
6
1       SQLCHAR     0     15   ";"      1     col1      SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR     0     25   ";"      2     col2      SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR     0     6    ";"      3     col3      SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR     0     5    ";"      4     col4      SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR     0     15   ";"      5     col5      SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR     0     100  "\r\n"   6     col6      SQL_Latin1_General_CP1_CI_AS

#2

You could use SQL Server Integration Services (SSIS) to import flat files on a scheduled basis.

您可以使用SQL Server Integration Services（SSIS）按计划导入平面文件。

Here is an example that loops through all CSV files of same format in a given a folder and loads them into database.

下面是一个示例，它循环遍历给定文件夹中相同格式的所有CSV文件，并将它们加载到数据库中。

How do I move files to an archive folder after the files have been processed?

处理完文件后，如何将文件移动到存档文件夹？

Here is another example:

这是另一个例子：

Validate CSV file Data before import into SQL Server table in SSIS?

在导入SSIS中的SQL Server表之前验证CSV文件数据？

Following answer shows how to schedule an SSIS package to run from inside a SQL Server Agent job.

以下答案显示如何安排从SQL Server代理作业内部运行SSIS包。

How do I create a step in my SQL Server Agent Job which will run my SSIS package?

如何在我的SQL Server代理作业中创建一个将运行我的SSIS包的步骤？

Hope that gives you an idea.

希望能给你一个想法。

#1

insert into your_table
  SELECT a.*  
  FROM OPENROWSET( BULK 'c:\input.csv', FORMATFILE = 'c:\Format.fmt') AS a;