如何将值添加到本地文本文件中的单个空列中

Question

我的表格为客户，customerid，companyname，address，phone

现在，我们插入了一个称为备注的新列，该列为空或null

我有文本文件使用以下代码

将视图Indioninsert插入列中

bulk insert  HRRegion.dbo.Remarksinsert
 From 'C:UsersSMSTECHLNG50Documentsremarks..txt'
 with 
 (
 FIELDTERMINATOR = ',',
ROWTERMINATOR = 'n'
)
GO

但是它得到错误

msg 515，第16级，状态2，第9行无法将值插入到 列" customerId"，table'hrregion.dbo.customers';列没有 允许空。插入失败。该声明已终止。

Answer 1

我认为，在这里，您只能插入整个行或任何一行。

如果您的客户表看起来像这样：

customer
custid|co_name        |addr                     |phone            |remarks
    42|Laverda        |Breganze, Italy          |[phone]  |(NULL)
    43|Benelli        |Pesaro, Italy            |[phone]  |(NULL)
    44|Ural           |Irbit, Russia            |[phone]|(NULL)
    45|Dnepr          |Kiew, Ukraine            |[phone] |(NULL)
    46|Harley Davidson|Milwaukee, US            |[phone]  |(NULL)
    47|Honda          |Tokyo, Japan             |[phone]  |(NULL)
    48|Moto Guzzi     |Mandello del Lario, Italy|[phone]  |(NULL)
    49|Ducati         |Bologna, Italy           |[phone]  |(NULL)
    50|Norton         |Birmingham, UK           |[phone]|(NULL)
    51|Matchless      |Plumstead, London, UK    |[phone]|(NULL)
    52|Brough         |Nottingham, UK           |[phone]|(NULL)

（好吧，您使用ALTER TABLE ...添加remarks列...），然后，我希望您在备注中提到的文件看起来有些类似：

remarks
custid|remarks
    42|built also tractors, closed now
    43|first series 6-cylinder motorbike
    44|old style sidecar rigs with modern engine
    45|old style sidecar rigs, permanent two-wheel drive
    46|the american classic
    47|builders of the CB 750 four and the gold wing
    48|famous for horizontal singles and 90° V twins
    49|90° V twin bikes with lateral crankshaft
    50|english classic, still alive
    51|english classic, closed now
    52|probably the finest motorcycles ever built

所以，您将构建一个remarks_stg表：

CREATE TABLE remarks_stg (
  custid  SMALLINT    NOT NULL
, remarks VARCHAR(50) NOT NULL
);

然后，您只需加载该登台表带有上述数据文件 - 至少如果具有SQL Server 2008及以上，则使用MERGE语句更新customer表：

MERGE customer t
USING stg_customer s
  ON t.custid = s. custid
WHEN MATCHED THEN UPDATE SET
  remarks = s.remarks
;