I want to import the text file from folder on daily basis into the sql server table. I can do it using the bulk insert but the daily text file name different then i have to change the script(only the file name). is there any way to export the file once we received it in folder and sp can read the name dynamically.

CodePudding user response：

It is better to do this type of task with SSIS. But sometimes SSIS needs extra dependencies for the developers. So they try to do it using SQL Server.

So you can do this with the following SETPS. It'll dynamically read your text file from a specific location and also move to another after reading.

Suppose you have the following DB and table.

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

And you have the following text data in a text file that needs to insert into the table. After reading you also need to move the file to another location. Just copy & paste the following data to your text file.

SETP:1 Create the following SP. And suppose your @oldDir='C:\Data' and @newDir='C:\Data2'. @oldDir means from here your text file will be read & @newDir means where the file will be moved after reading.

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

STEP:2 Make a SQL server Job Schedular to execute the STEP: 1 on your daily basis or hourly basis or whatever you want.

Change as per your need as guided by the comment line in the following script and run the script to create a Job scheduler, which will run after every 1 minute (you may change it hourly, daily, etc basis) & it will execute our 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