具有FOREIGN KEY参数的SQL Server批量插入（txt文件中不存在，包括ERD） [英] SQL Server Bulk Insert with FOREIGN KEY parameter (not existant in txt file, ERDs included)

问题描述

好的，所以我有一个表ERD的设计如下……用于常规批量插入

<sub>（来源：

（来源： iforce.co.nz ）</sub>

解决方案

大容量插入将插入NULL或未指定列的默认值（基于KEEPNULLS参数），当然，如果您具有（或将创建一个约束。我认为是这种情况，因为否则您可以在运行插入操作之后直接更新表。

我看到了两种解决方法：

-如果有此能力，则可以在运行批量插入之前对文本文件进行宏编辑。因为我假设这不是问题所在...

-首先，您需要将FK列添加到_stg表中（如果尚未存在的话）。然后，在存储过程中，使用在输入文件中指定的三列创建一个临时表：

 创建表dbo。＃ Temp_STG 
（
列A，
列B，
列C 
）
  

然后，批量插入该表。然后，您可以从temp表插入到_stg主表中，但添加一列：

  INSERT dbo.Customer_STG 
选择
 T.columnA，
 T.columnB，
 T.columnC，
 [您的客户密钥] 
从dbo。＃Temp_STG AS T 
  

确保完成后删除临时表。

作为旁注，您需要为此任务使用动态SQL吗？通常最好避免，除非绝对必要。

我想另一个选择是将列的默认值设置为所需的值，然后关闭KEEPNULLS。但是，当您只能使用上述解决方案时，我绝对不建议您这样做。

查看更多信息： http://msdn.microsoft.com/zh-CN/library/ms188365.aspx

Okay so I have a table ERD designed like so... for regular bulk inserts

_{(source: iforce.co.nz)}

And a tab delimited \t text file with information about each customer (consists of about 100,000+ records).

# columnA columnB columnC
data_pointA data_pointB data_pointC

And a stored procedure that currently does its intended job fine.

CREATE PROCEDURE import_customer_from_txt_para @filelocation varchar(100)
AS BEGIN

    TRUNCATE TABLE dbo.[customer_stg]
    DECLARE @sql nvarchar(4000) = '
    BULK INSERT customer_stg
    FROM ''' + @filelocation + '''
    WITH
    (
        FIRSTROW=14,
        FIELDTERMINATOR=''\t'',
        ROWTERMINATOR=''\n''
    )';
    print @sql;
    exec(@sql);

END

But my question is about the relationship between customer_table and customer_stg is it possible to include a customer_id within the customer_stg bulk insert? with something like so? ( I'm not sure how to apply the foreign key parameter @customer_sk to the bulk insert ).

CREATE PROCEDURE import_customer_from_txt_para @filelocation varchar(100), @customer_sk int
AS BEGIN

    TRUNCATE TABLE dbo.[customer_stg]
    DECLARE @sql nvarchar(4000) = '
    BULK INSERT customer_stg
    FROM ''' + @filelocation + '''
    WITH
    (
        FIRSTROW=14,
        FIELDTERMINATOR=''\t'',
        ROWTERMINATOR=''\n''
    )';
    print @sql;
    exec(@sql);

END

Preferably after each bulk-insert I'd wish to be able to relate the data between the two tables.

_{(source: iforce.co.nz)}

解决方案

Bulk inserts will either insert NULL or the default value for unspecified column (based on the KEEPNULLS argument), which of course will not work for your situation assuming you have (or will create) a constraint. I assume this is the case because otherwise you could just update your table directly after you run the insert.

I see two ways around this:
- If you have the ability, you can just macro-edit the text file before you run the bulk insert. Since I'm assuming that that isn't in the question...
- First of all, you will need to add your FK column to your _stg table if it's not already there. Then, in your stored procedure, create a temp table with the three columns specified in the input file:

CREATE TABLE dbo.#Temp_STG
(
    columnA,
    columnB,
    columnC
)

Then, batch insert into that table. Then you can insert from the temp table to the main _stg table, but add a column:

INSERT dbo.Customer_STG
SELECT
    T.columnA,
    T.columnB,
    T.columnC,
    [your customer key]
FROM dbo.#Temp_STG AS T

Make sure you drop the temp table when you're done.

As a side note, do you need to use dynamic SQL for this task? It's generally best to avoid unless absolutely necessary.

I suppose another option would be setting the default value for the column to whatever you want, and turning KEEPNULLS off. But, I would definitely NOT recommend doing this when you can just use the solution described above.

See more: http://msdn.microsoft.com/en-us/library/ms188365.aspx

这篇关于具有FOREIGN KEY参数的SQL Server批量插入（txt文件中不存在，包括ERD）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！