如何使用显示文件名的附加列批量插入? [英] How do I BULK INSERT with additional column showing filename?

问题描述

我还在习惯SQL，所以在开始使用存储过程之前，我想先了解如何有效地使用BULK INSERT.

I'm still getting used to SQL, so before I get to using stored procedure, I would like to understand how to use BULK INSERT effectively first.

我需要组合 50 多个 csv 文件并将它们转储到 SQL 表中.问题是，我希望能够区分每条记录(例如，每条记录都属于某个 csv 文件，我将通过文件名进行识别).

I need to combine 50+ csv files and dump them into an SQL table. The problem is, I'd like to be able to tell each record apart (as in, each record belongs to a certain csv file, which I will identify by the file name).

这是我想要的一个小例子:

Here's a small example of what I want:

CREATE TABLE ResultsDump
(
    PC FLOAT,
    Amp VARCHAR(50),
    RCS VARCHAR(50),
    CW VARCHAR(50),
    State0 VARCHAR(50),
    State1 VARCHAR(50),
)
BULK INSERT ResultsDump
    FROM 'c:\distance1000_7_13_2010_1_13PM_Avery DennisonAD_2300008_10S_Lock.csv' 
    WITH 
    ( 
        FIRSTROW = 2, 
        MAXERRORS = 0, 
        FIELDTERMINATOR = ',', 
        ROWTERMINATOR = '\n'
    )
BULK INSERT ResultsDump
    FROM 'c:\distance1000_7_13_2010_2_27PM_Avery DennisonAD_2300009_10S_Lock.csv' 
    WITH 
    ( 
        FIRSTROW = 2, 
        MAXERRORS = 0, 
        FIELDTERMINATOR = ',', 
        ROWTERMINATOR = '\n'
    )
BULK INSERT ResultsDump
    FROM 'C:\distance1000_7_13_2010_2_58PM_Avery DennisonAD_230000A_10S_Lock.csv' 
    WITH 
    ( 
        FIRSTROW = 2, 
        MAXERRORS = 0, 
        FIELDTERMINATOR = ',', 
        ROWTERMINATOR = '\n'
    )
BULK INSERT ResultsDump
    FROM 'c:\distance1000_7_13_2010_3_21PM_Avery DennisonAD_230000B_10S_Lock.csv' 
    WITH 
    ( 
        FIRSTROW = 2, 
        MAXERRORS = 0, 
        FIELDTERMINATOR = ',', 
        ROWTERMINATOR = '\n'
    )
BULK INSERT ResultsDump
    FROM 'c:\distance1000_7_13_2010_3_41PM_Avery DennisonAD_230000C_10S_Lock.csv' 
    WITH 
    ( 
        FIRSTROW = 2, 
        MAXERRORS = 0, 
        FIELDTERMINATOR = ',', 
        ROWTERMINATOR = '\n'
    )

我知道这是一种低效的做事方式，但我绝对喜欢在开始创建存储过程之前弄清楚如何以我想要的格式手动转储 SQL 表中的一个文件.

I know this is an inefficient way of doing things, but I definitely like to figure out how to manually dump one file in the SQL table in the format I want before I start to create a stored procedure.

在新表中，我想要这样的东西:

In the new table, I want something like this:

FileName,PC,Amp,RCS,CW,State0,State1
c:\distance1000_7_13_2010_1_13PM_Avery DennisonAD_2300008_10S_Lock.csv, ...
...
...
c:\distance1000_7_13_2010_2_27PM_Avery DennisonAD_2300009_10S_Lock.csv, ...
...
...
c:\distance1000_7_13_2010_2_58PM_Avery DennisonAD_230000A_10S_Lock.csv, ...
...
...

任何对特定功能的简单建议或推荐都会很棒！请记住，我已经习惯了 SQL，如果我能一次迈出这一步就太好了，这就是我从这么简单的问题开始的原因.

Any simple suggestions or referrals to specific functions would be great! Remember, I'm getting used to SQL and it'd be great if I could take this one step at a time, that's why I'm starting with such a simple question.

提前致谢！

推荐答案

您可以将列 FileName varchar(max) 添加到 ResultsDump 表中，使用新列创建表的视图，批量插入到视图中，并在每次插入后，为仍然具有默认值的列设置文件名 null:

You can add a column FileName varchar(max) to the ResultsDump table, create a view of the table with the new column, bulk insert into the view, and after every insert, set the filename for columns where it still has its default value null:

CREATE TABLE dbo.ResultsDump
(
    PC FLOAT,
    Amp VARCHAR(50),
    RCS VARCHAR(50),
    CW VARCHAR(50),
    State0 VARCHAR(50),
    State1 VARCHAR(50),
)
GO

ALTER TABLE dbo.ResultsDump ADD [FileName] VARCHAR(300) NULL 
GO 

CREATE VIEW dbo.vw_ResultsDump AS
SELECT
    PC,
    Amp,
    RCS,
    CW,
    State0,
    State1
FROM
    ResultsDump
GO

BULK INSERT vw_ResultsDump
    FROM 'c:\distance1000_7_13_2010_1_13PM_Avery DennisonAD_2300008_10S_Lock.csv' 
    WITH 
    ( 
        FIRSTROW = 2, 
        MAXERRORS = 0, 
        FIELDTERMINATOR = ',', 
        ROWTERMINATOR = '\n'
    )

UPDATE dbo.ResultsDump 
SET [FileName] = 'c:\distance1000_7_13_2010_1_13PM_Avery DennisonAD_2300008_10S_Lock.csv'
WHERE [FileName] IS NULL 

BULK INSERT vw_ResultsDump
    FROM 'c:\distance1000_7_13_2010_2_27PM_Avery DennisonAD_2300009_10S_Lock.csv' 
    WITH 
    ( 
        FIRSTROW = 2, 
        MAXERRORS = 0, 
        FIELDTERMINATOR = ',', 
        ROWTERMINATOR = '\n'
    )

UPDATE dbo.ResultsDump 
SET [FileName] = 'distance1000_7_13_2010_2_27PM_Avery DennisonAD_2300009_10S_Lock.csv'
WHERE [FileName] IS NULL 

这篇关于如何使用显示文件名的附加列批量插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！