How to programmatically import large numbers of records from a separated text file from a folder and insert into SQL Table

Aypn CNN 446 Reputation points
2022-11-01T09:43:11.007+00:00

Hi,

Can anyone provide some samples programmatically to import from a separate text file same is insert into a SQL Table with created timestamp.

Please ref my sample Bulk separate text file, it stored in D:\BulkImpFile\EM_20221031.txt (attached) 255948-em-20221031.txt
256041-ss-bulktextfile.png

ref my SQL Table

If(OBJECT_ID('tempdb..#empMaster') Is Not Null)  
Begin  
    Drop Table #empMaster  
End  
  
Create Table #empMaster  
(  
    EmpCode Varchar(10),   
    EmpName Varchar(50),   
    DOB DateTime,   
 DPImage_Path Varchar(MAX),   
 Salary Decimal(15,2),  
 CreatedOnTimeStamp DateTime  
)  
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,268 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,508 questions
0 comments No comments
{count} votes

Accepted answer
  1. ZoeHui-MSFT 34,996 Reputation points
    2022-11-02T03:16:05.73+00:00

    Hi @Aypn CNN ,

    You may use SSIS to import the data to table.

    And then you may use sql agent job to run the package so that it could be an automated operation.

    ssis-how-to-get-most-recent-file-from.html

    sql-server-agent-jobs-for-packages

    Regards,

    Zoe Hui


    If the answer is helpful, please click "Accept Answer" and upvote it.


1 additional answer

Sort by: Most helpful
  1. Olaf Helper 42,926 Reputation points
    2022-11-01T10:13:42.663+00:00

    You can use the bcp Utility to import data into SQL Server.