How to generate Insert Statements from Text Files for SQL Server Table in SQL Server - SQL Server / TSQL Tutorial Part 106

Scenario:

You are working as SQL Server developer, you get text files such as comma separate values, Tab delimited or pipe delimited files. It is one time task and you need to generate insert statements from these files so you can load the data into dbo.Customer table in DEV,QA,UAT and Production environments by using those scripts.

Solution:

I wrote a post , how to generate Insert statements from Excel file. Click here.  It means if we can open the text files with excel then we can use excel formulas to build insert statements.

If we have comma separate values file, you can directly open with excel and then use the steps shown in this post.

If you have tab delimited or pipe delimited file, first of all we need to open with excel and then use the formulas.

Step 1: 
Let's say that I have Tab delimited  Customer.txt file as shown below and want to open in excel.
How to generate Insert Statements from Tab delimited file for SQL Server Table

Step 2: 
Open Excel and then go to File and hit Open, Browse to customer.txt file
Generate Insert Statements from Text files for SQL Server Table 


Test Import Wizard will open, Choose Delimited and Click My Data has Headers if it does and  then Click Next
How to open Tab Delimited File in Excel to generate Insert statements for SQL Server Table

Choose the Delimiters, in our case it is Tab and hit Next.
How to Convert Tab Delimited File to Excel file and Generate Insert Statements for SQL Server Table

How to load Tab delimited file into Excel and Create Insert Statements for SQL Server Table


Once you will hit Finish, below excel sheet will be populated with flat file data.
How to generate Insert Statements from Excel File for SQL Server Table 

Now the data is in Excel file. we can use the excel formulas to generate insert statements as shown in this post.


Video Demo : How to generate Insert Statements from Flat File data for SQL Server Table