sql-server - 如何批量插入显示文件名的附加列？

Question

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

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

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

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 表中的一个文件。

在新表中，我想要这样的内容:

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，如果我能够一次迈出这一步那就太好了，这就是为什么我从这样一个简单的问题开始。

提前致谢!

Answer 1

您可以将列 FileName varchar(max) 添加到 ResultsDump 表，使用新列创建表的 View ，批量插入到 View 中，并在每次插入后设置文件名对于仍具有默认值 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