2

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
Avinash Kumar
  • 41
  • 1
  • 11
  • 1
    If the files are on SQL Server, you can do this fairly easily with `INSERT table(columns, extracolumn) SELECT columns, 'textfile-1' FROM OPENROWSET(BULK, ...)`. You can even make this one query with `UNION ALL`. [See also](https://learn.microsoft.com/sql/t-sql/functions/openrowset-transact-sql). If the files are not on SQL Server, you could still do this with a local instance, then use the data import/export wizard to copy the final result. All this is assuming you only import the data once; for repeated imports investing time in learning SSIS is more appropriate. – Jeroen Mostert Mar 13 '19 at 19:10
  • You could use `BULK INSERT`, yes, but you'll need to write a script for each file. Alternatively, you could use SSIS and a For Each Loop Container and loo through each file, reusing the same Data Flow Task (provided all the files do indeed have the same definition). – Thom A Mar 13 '19 at 19:11
  • @avinash In my view SSIS meets the requirements perfectly. You can easily load all the files in a loop. Mind using an interim table. The source file name can be very easily captured along the data ingestion by applying a simple property change as per http://sqlblog.com/blogs/jamie_thomson/archive/2010/03/31/filenamecolumnname-property-flat-file-source-adapter-ssis-nugget.aspx – Arthur Mar 13 '19 at 20:22
  • If you are familiar with c# check out this project: https://github.com/munchy-bytes/SchemaMapper/wiki/Import-data-from-multiple-files-into-one-SQL-table-step-by-step-guide – Hadi Mar 13 '19 at 21:50

1 Answers1

1

SQL BULK INSERT

First of all you have to make sure that the table structure is identical with the file structure.

You can store the text files path inside a table, loop over these values using a cursor, build the command dynamically then execute the command:

DECLARE @strQuery VARCHAR(4000)
DECLARE @FileName VARCHAR(4000)

DECLARE file_cursor CURSOR  
    FOR SELECT FilePath FROM FilesTable
OPEN file_cursor 
FETCH NEXT FROM file_cursor INTO @FileName;  

WHILE @@FETCH_STATUS = 0  
BEGIN 

    SET @strQuery = 'BULK INSERT SchoolsTemp
        FROM ''' + @FileName + '''
        WITH
        (
        FIELDTERMINATOR = '','',  --Columns delimiter
        ROWTERMINATOR = ''\n'',   --Rows delimiter
        TABLOCK
        )

    EXEC(@strQuery)

    FETCH NEXT FROM file_cursor INTO @FileName;

END

CLOSE file_cursor 
DEALLOCATE file_cursor

More information

C# approach: SchemaMapper class library

If you are familiar with C#, recently i started a new project on Github, which is a class library developed using C#. You can use it to import tabular data from excel, word , powerpoint, text, csv, html, json and xml into a unified SQL server table. check it out at:

You can follow this Wiki page for a step-by-step guide:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Thank you for the suggestion and code. I'm new in this and I need a little bit more clarification. Where shall I put the folder name? Where shall I put the filename? – Avinash Kumar Mar 14 '19 at 03:07
  • Create a table. Called `filestable` that have 2 columns `ID` and `Filename` to store files pathes. – Hadi Mar 14 '19 at 05:19