SQL Server Bulk Insert with FOREIGN KEY parameter (does not exist in txt file including ERD)
So I have an ERD table constructed like this ... for normal bulk inserts
(source: iforce.co.nz )
And a tab delimited text file \t
containing information about each customer (contains about 100 000+ records).
# columnA columnB columnC
data_pointA data_pointB data_pointC
And a stored procedure that is currently doing its intended job well.
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 the customer_id in the bulk insert customer_stg
? with something like this? (I'm not sure how to apply the foreign key parameter @customer_sk
to 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 every bulk insert, I would like to be able to link data between the two tables.
(source: iforce.co.nz )
Bulk inserts will either insert a NULL or default value for an unspecified column (based on the KEEPNULLS argument), which of course won't work for your situation if you have (or create) a constraint. I assume this is the case, because otherwise you could just update the table directly after starting the insert.
I see two ways to get around this:
- If you have the option, you can just macro edit the text file before starting bulk paste. As I assume this is not in the question ...
- First of all, you will need to add your FK column to your _stg table if it doesn't already exist. Then, in your stored procedure, create a temporary table with the three columns listed in the input file:
CREATE TABLE dbo.#Temp_STG
(
columnA,
columnB,
columnC
)
Then batch insert into that table. Then you can insert from temp table into 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 is usually best avoided if absolutely necessary.
I suppose the other option would be to set the default for the column to whatever you want and turn off KEEPNULLS. But I would definitely NOT recommend doing this when you can just use the solution above.
More details: http://msdn.microsoft.com/en-us/library/ms188365.aspx