BULK INSERT 到带有外键的表中 [英] BULK INSERT into table with Foreign Key

问题描述

我有一个表 Customer，其中包含客户的详细信息.以下是字段

I have a table Customer which has the details of the customer. The following are the fields

CustId (PrimaryKey), Name, Date of Birth

我有另一个表，资产信息.有以下字段 -

I have another table, the Asset information. The following fields are there -

AssetId (PrimaryKey), AssetValue, CustId (Foreign Key Reference) 

我的 CSV 文件就是这样

My CSV file is as such

Name, Date of Birth, AssetValue 

我必须将它插入到两个表中.我拆分了 CSV 文件，一个带有出生日期名称，另一个带有 AssetValue.

and I have to insert it into the two tables. I split the CSV file, one with the Name of Date of Birth and the other with just the AssetValue.

这是我所做的 -

/*Creation of Table*/
CREATE TABLE Customer
(
    custid int identity(1,1) not null, 
    name nvarchar(50) not null, 
    dateofbirth datetime not null, 
    primary key (custid) 
)
CREATE TABLE Asset
(
    AssetId int identity(1,1) not null, 
    AssetDollars money not null, 
    primary key (AssetId),
    CustId int foreign key references Customer(custid)
)

对于批量插入，我所做的是这样.我使用两个字段 Name 和 Date of Birth 为 Customer 创建了一个视图，然后插入了记录.

For the bulk insert what I did was this. I created a view for the Customer with the two fields Name and Date of Birth and then inserted the records.

这是我所做的 -

CREATE view vw_bulk_insert_customer
AS
    SELECT name, dateofbirth FROM customer

BULK  INSERT vw_bulk_insert_customer
FROM 'C:\Customer.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

这工作得很好.

现在，如何将它插入到带有 CustId 的资产表中(因为它在 CSV 文件中不可用).

Now, how do I insert it into the Asset table with the CustId (as it is not available in the CSV file).

我不能更改 CSV 文件.我可以拆分 CSV 文件，这是允许的.

I am not allowed to change the CSV file. I can split the CSV file, that is allowed.

我不知道该怎么做...有什么想法吗?

I did not know how to do this...Any thoughts?

推荐答案

一种选择是将数据按原样导入临时表，然后将数据从该表复制到最终表.这本质上是规范化数据库的后半部分(跳过规范化表的设计和定义).

One option is to import the data as-is into an interim table, then copy data from this table to your final tables. This is essentially the latter part of normalizing a database (skipping the design & definition of the normalized tables).

/* interim table */
CREATE TABLE CustomerAssets (
    name NVARCHAR(50) NOT NULL,
    dateOfBirth DATETIME NOT NULL,
    assetDollars MONEY NOT NULL
)

BULK INSERT CustomerAssets
  FROM 'C:\Customer.csv'
  WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
  )

INSERT INTO Customer (name, dateOfBirth)
  SELECT name, dateOfBirth FROM CustomerAssets;

INSERT INTO Asset (assetDollars, custId)
  SELECT CA.assetDollars, Customer.custId
    FROM CustomerAssets AS CA
      INNER JOIN Customer
        ON CA.name = Customer.name AND CA.dateOfBirth = Customer.dateOfBirth;

这篇关于BULK INSERT 到带有外键的表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！