如何在SQL Server中批量插入派生列？(How to bulk insert with derived columns in SQL Server?)

 新手到SQL Server！ 
 
 我尝试在SQL Server中执行批量插入。 我有以下名为input.csv csv文件： 
 
NO,Name,age,Reference,dateTime,Category
1,Stack@mail,23,Kiop,2017-03-02T12:23:00,D
2,OverEnd@Yahoo,22,CSK,2017-030-03T12:23:00,I
 
 在该CSV文件中，我必须使用BulkInsert将其移动到SQL Server中，进入下面的表模式： 
 
create table BulkInsertTemp
(
     no int,
     name nvarchar(50),
     age int,
     Ref nvarchar(30),
     currentDatetime datetime,
     Category nvarchar(40)
)
 
 现在我需要在SQL中存储如下： 
 
no Name    age   Ref    currentDatetime        category
--------------------------------------------------------
1  Stack    23   Kiop   2017-03-02 12:23:00       D
2  OverEnd  22   CSK    2017-03-03 12:23:00       I
 
 我只是尝试下面的查询另一个表进入SQL Server。 
 
create table bulkInsert(no varchar(50),name varchar(50));

BULK INSERT bulkInsert 
FROM 'C:\MyInput\BulkInsert\BulkInsertData.txt' 
WITH 
    (FIRSTROW = 1, 
     ROWTERMINATOR = '\n', 
     FIELDTERMINATOR = ',', 
     ROWS_PER_BATCH = 10000)
 
 如果不需要修改数据，我的查询就有效。 
 
 但是在input.csv我必须更改列值，例如name是否为“Stack @ mail”，以便在SQL中存储为“Stack” 
 
 我是一个新的批量插入选项，所以我不知道如何从现有列派生列。 
 
 有人，请指导我解决我的要求吗？ 

Newbie to SQL Server!
 
I try to perform bulk insert into SQL Server. I have the following csv file named input.csv:
 
NO,Name,age,Reference,dateTime,Category
1,Stack@mail,23,Kiop,2017-03-02T12:23:00,D
2,OverEnd@Yahoo,22,CSK,2017-030-03T12:23:00,I
 
In that CSV file, I have to move that into SQL Server using BulkInsert into below table schema:
 
create table BulkInsertTemp
(
     no int,
     name nvarchar(50),
     age int,
     Ref nvarchar(30),
     currentDatetime datetime,
     Category nvarchar(40)
)
 
Now I need to store in SQL like:
 
no Name    age   Ref    currentDatetime        category
--------------------------------------------------------
1  Stack    23   Kiop   2017-03-02 12:23:00       D
2  OverEnd  22   CSK    2017-03-03 12:23:00       I
 
I just tried below query for another one table to move into SQL Server.
 
create table bulkInsert(no varchar(50),name varchar(50));

BULK INSERT bulkInsert 
FROM 'C:\MyInput\BulkInsert\BulkInsertData.txt' 
WITH 
    (FIRSTROW = 1, 
     ROWTERMINATOR = '\n', 
     FIELDTERMINATOR = ',', 
     ROWS_PER_BATCH = 10000)
 
My query worked if there is no need for modifying data.
 
But in input.csv I have to change column values such as if name is "Stack@mail" to be store as "Stack" in SQL
 
I am a new one to bulk insert option so I don't know how to derive columns from existing ones.
 
Anyone, please guide me to solve my requirement?

原文：https://stackoverflow.com/questions/44062020