SQL Server: Tips and Tricks - 2 (2017)

How to generate Insert Statements from Text Files for SQL Server Table in SQL Server

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:
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.

https://3.bp.blogspot.com/-gJ7hw8iajBI/VzDxnv42fwI/AAAAAAAAdx8/VQtS992mTFERfWITd5bWa-L5E7tiMOOVACLcB/s640/Capture.PNG


Step 2:
Open Excel and then go to File and hit Open, Browse to customer.txt file

https://2.bp.blogspot.com/-eGvSGyVdGik/VzDyI5oB4PI/AAAAAAAAdyA/yuijbY73NuQVRXk2foSd1VoTN8VdCjc0ACLcB/s640/Capture.PNG


Test Import Wizard will open, Choose Delimited and Click My Data has Headers if it does and then Click Next

https://4.bp.blogspot.com/-OTlzHwE43do/VzDykglTwiI/AAAAAAAAdyI/qbY_LedAJHodLVh5hFQbQo78APkgloPlACLcB/s640/Capture.PNG


Choose the Delimiters, in our case it is Tab and hit Next.

https://4.bp.blogspot.com/-xF7NL7z2EgA/VzDzKquKuEI/AAAAAAAAdyU/O-nlzm-0NcUr3vvvQvGlsX_6b3x6jDt4QCLcB/s640/Capture.PNG

https://1.bp.blogspot.com/-rtTx9BzIP34/VzDz0X9hUfI/AAAAAAAAdyc/WD2Tjh85dfA_Ajwmyca1TUGq4SwnZtGxACLcB/s640/Capture.PNG



Once you will hit Finish, below excel sheet will be populated with flat file data.

https://4.bp.blogspot.com/-Q6GCxG2ynq4/VzD0Mft_JKI/AAAAAAAAdyk/ks2w7W2IgewyUs7R2HZwoOnQLNWL_nIRgCLcB/s640/Capture.PNG

More to Come

Write a short review at the site you purchased Email me at sales@meetcoogle.com with a link to your review and I’ll reply with series next book for free!