使用查詢或存盤程序將文本檔案匯入SQL服務器表

我想每天將檔案夾中的文本檔案匯入到 sql server 表中。我可以使用批量插入來完成，但每日文本檔案名不同，然后我必須更改腳本（僅檔案名）。一旦我們在檔案夾中收到檔案，有沒有辦法匯出檔案，sp 可以動態讀取名稱。

uj5u.com熱心網友回復：

最好使用 SSIS 執行此類任務。但有時 SSIS 需要開發人員額外的依賴項。所以他們嘗試使用 SQL Server 來實作。

因此，您可以使用以下 SETPS 執行此操作。它會從特定位置動態讀取您的文本檔案，并在閱讀后移動到另一個位置。

假設您有以下資料庫和表。

CREATE DATABASE [Codefirst]
GO

USE [Codefirst]
GO

CREATE TABLE [dbo].[tbl_NewVendorData](
    [AccountNumber] [nvarchar](15) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [ModificationDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [AccountNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

并且您在需要插入表中的文本檔案中有以下文本資料。閱讀后您還需要將檔案移動到另一個位置。只需將以下資料復制并粘貼到您的文本檔案中即可。

SETP:1創建以下 SP。并假設您的@oldDir='C:\Data' 和@newDir='C:\Data2'。@oldDir 表示將從這里讀取您的文本檔案，@newDir 表示讀取后檔案將移動到的位置。

CREATE PROC uspReadTextFileAndMove
(
    @oldDir VARCHAR(1000)='C:\Data',
    @newDir VARCHAR(1000)='C:\Data2'
)
AS
BEGIN
DECLARE @command VARCHAR(1000);
DECLARE @insertCommand VARCHAR(1000);
DECLARE @cmdFileMove VARCHAR(1000);
DECLARE @files TABLE ([Id] INT IDENTITY, [FileName] VARCHAR(1000));
DECLARE @FileToRead VARCHAR(1000)

SET @command = 'dir /b '   @oldDir;
INSERT INTO @files EXECUTE xp_cmdshell @command;

DECLARE fileReadCursor CURSOR FOR

     SELECT [FileName] FROM @files

OPEN fileReadCursor;
FETCH fileReadCursor INTO @FileToRead;
WHILE (@@FETCH_STATUS = 0) 
BEGIN
    
        SET @insertCommand= 'BULK INSERT [dbo].[tbl_NewVendorData] FROM '''  @oldDir '\'  @FileToRead   ''' WITH  ( FIELDTERMINATOR='','', ROWTERMINATOR='';\n'');' 
        EXECUTE(@insertCommand);
        
        SET @cmdFileMove= 'MOVE '   @oldDir   '\'   @FileToRead   ' '  @newDir  '\' @FileToRead
        EXEC master..xp_cmdshell @cmdFileMove
    
    --PRINT @insertCommand;
    --PRINT @FileToRead;
    --PRINT @cmdFileMove;
    
    FETCH fileReadCursor INTO @FileToRead;
END;
CLOSE fileReadCursor;
DEALLOCATE fileReadCursor;

END

步驟：2制作一個 SQL 服務器作業計劃來執行步驟：1 每天或每小時或任何你想要的。

根據以下腳本中的注釋行的指導，根據您的需要進行更改并運行該腳本以創建作業調度程式，該程式將在每 1 分鐘后運行一次（您可以每小時、每天等更改一次）并且它將執行我們的 SP .

USE tempdb
GO
IF OBJECT_ID('##tempJobTableConfig', 'U') IS NOT NULL 
BEGIN
    DROP TABLE ##tempJobTableConfig; 
        
END

DECLARE @DatabaseName VARCHAR(200)='Codefirst'  --Change Here: Change Your Database name.
DECLARE @TrialDays INT = 0 --Change Here: Change your trial days number from @TrialDays variable.
DECLARE @freq_subday_type_Variable INT= 4 --if you want it as minute change it value as 4,If value is 8 then it will be hour, 
DECLARE @freq_subday_interval_Variable INT=1 --Change here as: If your @freq_subday_type=8 then 12 will be hour, @freq_subday_type=4 then 12 will be minutes, 

SELECT  @DatabaseName AS DatabaseName 
       ,DATEADD(DAY,@TrialDays,GETDATE()) AS ExpairationDate
       ,ISNULL(@freq_subday_type_Variable,4) AS freq_subday_type_Variable
       ,ISNULL(@freq_subday_interval_Variable,15) AS freq_subday_interval_Variable
INTO ##tempJobTableConfig



USE [msdb]
GO

DECLARE @jobId2 BINARY(16)
SELECT @jobId2 = job_id FROM msdb.dbo.sysjobs WHERE (name = N'TextFileReadAndMoveJob')
IF (@jobId2 IS NOT NULL)
BEGIN
    EXEC msdb.dbo.sp_delete_job @jobId2
END

BEGIN TRANSACTION

DECLARE @DateInIntData INT
DECLARE @jobId BINARY(16)
DECLARE @ReturnCode INT
DECLARE @User NVARCHAR(500)=(SELECT SUSER_NAME())

/*===========================================================================================================================================================================
        Value of @DateInIntData will be like this =20210224, where 2021=year,02=month & 24=day & it'll dynamically add current date as your schedular start date.
=============================================================================================================================================================================*/

SELECT @ReturnCode = 0 
SET @DateInIntData=CAST(
                    (CAST((SELECT DATEPART(YEAR,GETDATE())) AS VARCHAR(4))
                    RIGHT('00' CAST((SELECT DATEPART(MONTH,GETDATE())) AS VARCHAR(4)),2)
                    RIGHT('00' CAST((SELECT DATEPART(DAY,GETDATE())) AS VARCHAR(4)),2))
                   AS INT)

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'TextFileReadAndMoveJob', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=@User, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

DECLARE @databaseValue VARCHAR(400)=(SELECT DatabaseName FROM tempdb..##tempJobTableConfig)
DECLARE @commandValue NVARCHAR(500)=N'USE ' @databaseValue ' 
GO
EXEC uspReadTextFileAndMove;
'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Execution', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=@commandValue, 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
DECLARE @schedule_uid2 NVARCHAR(500)=(SELECT NEWID())

DECLARE @freq_subday_type_Value INT=(SELECT ISNULL(freq_subday_type_Variable,4) FROM tempdb..##tempJobTableConfig)
DECLARE @freq_subday_interval_Value INT=(SELECT ISNULL(freq_subday_interval_Variable,4) FROM tempdb..##tempJobTableConfig)
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'TextFileReadAndMoveJobSchedule', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=@freq_subday_type_Value,
        @freq_subday_interval=@freq_subday_interval_Value,
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=@DateInIntData, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959, 
        @schedule_uid=@schedule_uid2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Note: You may need to enable 'xp_cmdshell's permission. To do that execute the following code.

-- this turns on advanced options and is needed to configure xp_cmdshell
EXEC sp_configure 'show advanced options', '1'
RECONFIGURE
-- this disables xp_cmdshell
EXEC sp_configure 'xp_cmdshell', '0' 
RECONFIGURE

標籤：sql-server 批量插入

上一篇：c#-如何為datagridview實作更新按鈕并使用dataHandler類（資料訪問類）將值保存到資料庫

下一篇：EWS發送郵件在C#中失敗，但在VB.Net中沒有