Sql Server Insert From Flat File

I have some data that needs to be imported into SQL Server. I have the following fields: ID Param1 Param2 The way it needs to go into the table is not that straighforward. It

Solution 1:

As the question was a little bit unclear for me but if I'm getting you well then there is many ways for doing it one simple way is using a temp table:

create a temp table:

CREATETABLE #TBL (ID int, param1 datetime, param2 datetime);

bulk insert from file into temp table

BULK INSERT #TBL FROM'D:\data.txt'WITH (FIELDTERMINATOR =' ');

now you can insert into permanent table using a specific query on the temp table (assuming your table structure is: (ID,param) ):

INSERTINTO TABLE_NAME(id,PARAM)
SELECTDISTINCT T.ID,T.PARAM1
FROM #TBL
UNIONSELECTDISTINCT T.ID,T.PARAM2
FROM #TBL

Solution 2:

Since you are using C#, you can make use of Table-Valued Parameters to stream in the data in any way you like. You can read a row from a file, split it apart, and pass in 2 rows instead of mapping columns 1 to 1. I detailed a similar approach in this answer:

How can I insert 10 million records in the shortest time possible?

The main difference here is that, in the while loop inside of the GetFileContents() method, you would need to call yield return twice, once for each piece.