我需要批量插入一个文本文件,它总是存储在同一个文件夹中。文件名为'employee_date
',其中的日期部分并不总是实际日期。它是用户每天更改的动态日期(格式为YYYYMMDDHHMM)。
我需要的是一个查询,它在该文本文件(已经格式化)中批量插入数据。我的问题是,批量插入不能与变量、*.txt
或employes*.txt
一起使用。
我需要一个查询,它只批量插入名称类似EMPLOYEE_YYYYMMDDHHMM.txt
的文件,可以每天执行,并且总是插入该文件夹中的文件,而不管文件名中的日期是什么。
发布于 2016-10-31 23:17:33
这里有一些你可以修改以满足你的需求的东西。我有一个类似的任务,我们从Linux系统得到文件SFTP
给我们,我需要把这些数据上传到SQL Server。这是基本的布局。了解您的文件位置、文件夹权限、SQL权限等都需要考虑在内。包括您是否可以在您的环境中运行命令shell代码。
CREATE procedure [dbo].[file_upload]
as
DECLARE @dt VARCHAR(10) --date variable but stored as VARCHAR for formatting of file name
DECLARE @fileLocation VARCHAR(128) = 'E:\SomeFolder\' --production location which is
DECLARE @sql NVARCHAR(4000) --dynamic sql variable
DECLARE @fileName VARCHAR(128) --full file name variable
--This stores the file names into a temp table to be used in a cursor.
--The bottom part is handleing some date formatting i needed. You can change to what your files look like
IF OBJECT_ID('tempdb..#FileNames') IS NOT NULL DROP TABLE #FileNames
CREATE TABLE #FileNames (
id int IDENTITY(1,1)
,subdirectory nvarchar(512)
,depth int
,isfile bit
,fileDate date null
,fileTime time null)
INSERT #FileNames (subdirectory,depth,isfile)
EXEC xp_dirtree @fileLocation, 1, 1
UPDATE #FileNames SET
fileDate = CAST(SUBSTRING(subdirectory,LEN(subdirectory) - 19,10) AS DATE)
,fileTime = CAST(REPLACE(SUBSTRING(subdirectory,LEN(subdirectory) - 8,5),'-',':') AS TIME)
--here's the cursor to loop through all the files
DECLARE c CURSOR FOR
select subdirectory from #FileNames
OPEN c
FETCH NEXT FROM c INTO @fileName
--For each file, bulk insert or what ever you want...
WHILE @@FETCH_STATUS = 0
BEGIN
--set the dynamic with the appropriate delimiters, if you want to keep headers, etc.
SET @sql = 'BULK INSERT Server.dbo.someTable FROM '''+ @fileLocation + @fileName +''' WITH (FIELDTERMINATOR = ''|'',FIRSTROW=2,KEEPNULLS,ROWTERMINATOR = ''0x0a'')'
EXEC(@sql)
--do some other stuff like logging, updating, etc...
END
CLOSE c
DEALLOCATE c
发布于 2016-12-21 12:53:41
这对你来说应该足够了。只需根据需要进行调整。
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=48)
BEGIN
PRINT @intFlag
declare @fullpath1 varchar(1000)
select @fullpath1 = '''your_path_here\employee_' + convert(varchar, getdate()- @intFlag , 112) + '.txt'''
declare @cmd1 nvarchar(1000)
select @cmd1 = 'bulk insert [dbo].[your_table_name] from ' + @fullpath1 + ' with (FIELDTERMINATOR = ''\t'', FIRSTROW = 2, ROWTERMINATOR=''0x0a'')'
exec (@cmd1)
SET @intFlag = @intFlag + 1
END
GO
https://stackoverflow.com/questions/40344629
复制相似问题