SQL 2008 r2导入文本文件 [英] SQL 2008 r2 Importing Text Files

问题描述

我每天都需要将文件附加到同一个sql表中。我知道如何使用SQL Server导入和导出向导但我需要将文件名和日期/时间添加到我导入的每个每日文件的末尾（...所以我们可以将记录区分为何时以及来自哪个文件）。



问题1：SQL Server导入和导出向导是导入这些文件的最佳方式吗？


问题2：如何添加导入文件的文件名和导入日期/时间？注意：我会将每日文件附加到表格中。



网上通常很少或者就像在问某人什么时间和他们一样讲述如何制作手表。 ：）

I have daily files that need to be appended to the same sql table. I know how to use the "SQL Server Import and Export Wizard" but I need to add the "file name" and "date/time" to the end of each daily file I import(...so we can distinguish the records as to when and which file they came from).

Question 1: Is the "SQL Server Import and Export Wizard" the best way to import these files?

Question 2: How do I add the File Name and the Import Date/Time the imported file? Note: I will be append the daily files to the table.

The is usually very little on the web or so much it's like asking someone what time it is and they tell how to build a watch. :)

推荐答案

尝试使用 OPENROWSET [ ^ ]。

Try to use OPENROWSET[^].

DECLARE @tname VARCHAR(150)
DECLARE @fname VARCHAR(150)

SET @tname = 'c:\test\File1.txt'
SET @fname = 'c:\test\File1.fmt'


INSERT INTO SQLTable (Field1, Field2, ..., FileName, ImportDate)
SELECT a.Field1, a.Field2, ..., @tname AS FileName, GETDATE() AS ImportDate
FROM OPENROWSET( BULK , @tname, FORMATFILE = @fanme) AS a;

请访问以上链接了解更多详情;）

Please, visit above link get more details ;)

这篇关于SQL 2008 r2导入文本文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！