Insert from flat file into SQL Server Table but with different number of Columns and order ?

Question

Hello, is it possible to Insert data from a flat file that has say 5 columns but target SQL Server Table has only 3 matching Columns (with correct data types) but in different order?

Following example:

Source flat personlist.csv file has 5 Column headers are: [name], [gender], [age], [city], [country]

But SQL Server target Tables has 3 Columns created as:

create table dbo.personlist (
[age] int,
[name] varchar(20),
[gender] varchar(10)
);

Just the 3 Columns in SQL Server target Table is needed and the other 2 sets of data [city], [country] from flat file can be excluded.

Thanks in advance.

Answer 1

Yes, you can do this with a format file. It would look something like this:

   9.0  
   5  
   1 SQLCHAR "" "" "," 1 col1 ""  
   2 SQLCHAR "" "" "," 0 "" ""  
   3 SQLCHAR "" "" "," 2 col1 ""  
   4 SQLCHAR "" "" "," 0 "" ""  
   5 SQLCHAR "" "" "\r\n" 3 col3 ""  

In this example fields 1, 3 and 5 are imported.

For more information on format files, see this article on my web site: https://www.sommarskog.se/bulkload.html

Answer 2

Hi @techresearch7777777 ,

Yes, it is possible. You can achieve this goal through SSIS. During this process, you can choose the columns that you want to import to SQL table , map the source column and target column. Refer to this blog Load flat file into SQL Server table in SSIS to get detail steps.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

is it possible to Insert data from a flat file that has say 5 columns but target SQL Server Table has only 3 matching Columns (with correct data types) but in different order?

Sure it is, I do it all the time.
But you didn't mentioned in any way how you load the data, so it's not really possible possible to give you further suggestion.
So please provide more details about your current solutions.

Answer 4

Thanks for the replies, much appreciated. We won't be using SSIS in this case. The only thing comes to mind so far is something like BULK... INSERT... SELECT... FROM... OPENROWSET... FORMATFILE but not sure the technical logistics or if there was any other way.

Answer 5

Hi @techresearch7777777 ,

Please reading below blog, you can get the detail solution from it.

How to import flat files with a varying number of columns in SQL Server

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".