0

I am working on the SQL Server 2017. I need to import 20 text file into one table. Every text file has the same data type and column name. I have checked the data and they have in the same order also. I need to import in SQL Table and create a new column, the last column saying that

Row 1 to Row 150 data comes from "textfile-1"

, Row151 to Row300 data comes from "textfile-2"

, Row301 to Row400 data comes from "textfile-3"

We don't have any packages like SSIS.

Can we do it in Advance SQL Query? if so can someone please guide me

0

You should be able to achieve this using BULK INSERT. Check out this Microsoft Docs article and this blog from Pinal Dave for examples.

0

So not sure why @HandyD got so many doenvotes but I voted up for the reference to Penal Dave's blog post on this.

After creating the table you want to import the data to (TextFileImportTable) with a nullable column (FileName) on the end to store the file name, here's an example on how to import to it:

BULK
INSERT TextFileImportTable
FROM 'c:\textfile-1.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

UPDATE TextFileImportTable
SET FileName = 'textfile-1'
WHERE FileName IS NULL;

BULK
INSERT TextFileImportTable
FROM 'c:\textfile-2.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

UPDATE TextFileImportTable
SET FileName = 'textfile-2'
WHERE FileName IS NULL;

Rinse and repeat a new batch for each text file.

If you need help with a query for creating the table too, let me know.

  • Note this answer assumes the columns are comma delimeted and each row is on a new line. You can modify those attributes by changing the characters used in the FIELDTERMINATOR and ROWTERMINATOR properties. – J.D. Mar 17 at 3:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.