1

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 needs to go in as

   ID Param1   5655 DateTime
   ID Param2   5555 DateTime

as such, it needs to insert 2 records into the table for one row from the input file. Wondering what the best way to do this in SQL Server is in terms of importing the file. I can do a BULK INSERT but I the columns need to match exactly. In my case it does not

I am also using .NET C#. Wondering if importing file to datatable, etc. and then using foreach look to further manipulate it may be the best approach.

Nate Pet
  • 38,422
  • 114
  • 251
  • 393
  • Can you quickly massage the file using excel? – Ross Bush Mar 12 '15 at 20:31
  • or massage with any text editor that records keystrokes to massage it. Another way is to import it as it to a staging table, then query it to get it into the table you need. – Tony Hopkinson Mar 12 '15 at 20:36
  • 1
    Mimic the bulk insert by creating temp tables in sql server to pass the data all at once via XML this is very easy to do.. you can also do this by splitting the data and storing it into a class with the 3 fields and from there Insert the data into the database.. can you show us what you have actually tried on your own vs waiting on an answer from one of us – MethodMan Mar 12 '15 at 20:41
  • You can do it with SSIS and an UNPIVOT transformation. – Tab Alleman Mar 12 '15 at 20:47

2 Answers2

1

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.

Community
  • 1
  • 1
Solomon Rutzky
  • 41,664
  • 6
  • 112
  • 149
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:

CREATE TABLE #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) ):

INSERT INTO TABLE_NAME(id,PARAM)
SELECT DISTINCT T.ID,T.PARAM1
FROM #TBL
UNION 
SELECT DISTINCT T.ID,T.PARAM2
FROM #TBL
null
  • 7,377
  • 3
  • 20
  • 41