将.csv文件到SQL Server与BULK INSERT和重新排序列 [英] Insert .csv file into SQL Server with BULK INSERT and reorder columns

问题描述

我做一个ASP.NET应用程序，我想将数据插入到CSV文件中我的SQL Server。我这个SQL命令这样做是：

I make an ASP.NET application and I want to insert data into my SQL Server from a CSV file. I did it with this SQL command:

BULK
INSERT Shops
FROM 'C:\..\file.csv'
WITH
(
   FIELDTERMINATOR = ';',
   ROWTERMINATOR = '\n'
);

据pretty的作品，但我有一个自动递增选择一个id列。我想插入的列重新排序到SQL Server增量automatiquely ID列。

It pretty works but I have an id columns with AUTO INCREMENT option. I want to reorder inserted columns to SQL server increment automatiquely Id column.

如何能做到这一点与 BULK 方法？

How can I do that with BULK method?

（当然，我不希望编辑manualy .csv文件：P）

(of course, I don't want to edit .csv file manualy :P )

推荐答案

就像我在我的评论说：你不能，BULK INSERT只是泵，你不能以任何方式转换数据。你可以做的是批量插入到临时表（S）和使用INSERT语句做你需要做的事情。

Like I said in my comment: You can't, bulk insert just pumps data in, you can't transform the data in any way. What you can do is bulk insert to staging table(s) and use an insert statement to do what you need to do.

您可以做到这一点是这样的：

You can do it like this:

-- Create staging table
SELECT   TOP 0 *
INTO     Shops_temp
FROM     Shops;


-- Bulk insert into staging
BULK INSERT Shops_temp
FROM 'C:\..\file.csv'
WITH
(
   FIELDTERMINATOR = ';',
   ROWTERMINATOR = '\n'
);


-- Insert into actual table, use SELECT for transformation, column order etc.
INSERT INTO Shops(name, etc..)
SELECT name
,      etc..
FROM   Shops_temp;


-- Cleanup
DROP TABLE Shops_temp;

这篇关于将.csv文件到SQL Server与BULK INSERT和重新排序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！