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

1 Answer 1

2

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.

Note this answer assumes the columns are comma delimited and each row is new line delimited. You can modify those attributes by changing the characters used in the FIELDTERMINATOR and ROWTERMINATOR properties, per this Microsoft Docs page.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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