I have a very simple table - Four columns:
CREATE TABLE [dbo].[SampleTable]
(
[item] VARCHAR(32) NOT NULL,
[symbol] VARCHAR(64) NOT NULL,
[date] DATE NOT NULL,
[value] NVARCHAR(255) NOT NULL,
CONSTRAINT [PK_SampleTable]
PRIMARY KEY CLUSTERED ([symbol] ASC, [item] ASC, [date] DESC)
)
To update the table, I use a view and trigger:
CREATE VIEW [dbo].[SampleTable_in]
AS
SELECT [item], [symbol], [date], [value]
FROM [dbo].[SampleTable]
GO
CREATE TRIGGER [dbo].[SampleTable_in_trig]
ON [dbo].[SampleTable_in]
/*
Trigger on the insert view to facilitate:
1. Insertions into the table overwrite (update) any records that already exist
2. Sparse value updates (if the last value in the series is the same, value is not inserted)
3. Insertions where value is null are considered DELETES
*/
INSTEAD OF INSERT, UPDATE
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION
/* When inserted symbol, item, date exists and values don't match, update */
UPDATE u
SET [value] = i.[value]
FROM [dbo].[SampleTable] u
INNER JOIN INSERTED i ON u.[symbol] = i.[symbol]
AND u.[item] = i.[item]
AND u.[date] = i.[date]
WHERE i.[value] IS NOT NULL
AND i.[value] != u.[value]
/* When inserted symbol, item, date exists and inserted value is null, delete */
DELETE [dbo].[SampleTable]
FROM [dbo].[SampleTable] u
INNER JOIN INSERTED i ON u.[symbol] = i.[symbol]
AND u.[item] = i.[item]
AND u.[date] = i.[date]
WHERE i.[value] IS NULL
/* When inserted symbol, item does not exist and inserted value is not null, insert */
INSERT INTO [dbo].[SampleTable] ([item], [symbol], [date], [value])
SELECT
i.[item], i.[symbol], i.[date], i.[value]
FROM
INSERTED i
LEFT OUTER JOIN
[dbo].[SampleTable] u ON u.[symbol] = i.[symbol]
AND u.[item] = i.[item]
WHERE
i.[value] IS NOT NULL
AND u.[symbol] IS NULL
/* When inserted symbol, item, date does not exist and value from prior date does not match inserted value, insert */
INSERT INTO [dbo].[SampleTable] ([item], [symbol], [date], [value])
SELECT
i.[item], i.[symbol], i.[date], i.[value]
FROM
INSERTED i
LEFT OUTER JOIN
[dbo].[SampleTable] u ON u.[symbol] = i.[symbol]
AND u.[item] = i.[item]
AND u.[date] = i.[date]
WHERE
i.[value] IS NOT NULL
AND u.[symbol] IS NULL
AND i.[value] != (SELECT TOP 1 [value]
FROM [dbo].[SampleTable] uu
WHERE uu.[symbol] = i.[symbol]
AND uu.[item] = i.[item]
AND uu.[date] < i.[date]
ORDER BY [date] DESC)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
EXEC proc_error_handler
END CATCH
GO
My input data is supplied in a TSV format file, provided in the format <item>\t<symbol>\t<date>\t<value> with the date in YYYYMMDD format. I cannot change the input format and SQL is raising the Invalid character value for cast specification when I attempt to bcp directly into the view (with FIRE_TRIGGERS enabled).
If I change the date format in the underlying table from DATE to SMALLDATETIME, the bcp in works exactly as expected, however my understanding is that this older format should not be used for new work (I want to avoid making this change for various reasons).
Likewise, if I change the input date format to YYYY-MM-DD, this works as expected, however as explained, I can't change the source data format and would have to build an extra processing step here to do this
I've tried altering the view, using either CAST or CONVERT to format the date field as SMALLDATETIME and a regular INSERT works just fine, but bcp is still not happy, with the trigger now throwing a Cannot insert the value NULL into column 'date'
CREATE VIEW [dbo].[SampleTable_in]
AS
SELECT [item], [symbol], CAST([date] AS SMALLDATETIME) AS [date], [value]
FROM [dbo].[SampleTable]
GO
Digging into this further, when I create that view and use the trigger to redirect the input to a table with all the columns set to NULL, the date is being replaced with NULL on the input (I assume by the bcp process before it ever gets to SQL)
Does anyone have any ideas on how to resolve this issue, beyond altering the target table or input data?
EDIT: The bcp command I am using is bcp TestDB.dbo.SampleTable_in in test_file.tsv -c -b 50000 -T -h FIRE_TRIGGERS -k
The data matches this small sample (but many more rows than this):
SOURCE M01 20210813 FOO
SOURCE M02 20210813 FOO
SYMBOL M01 20210813 M01
SYMBOL M02 20210813 M02
DESC M01 20210813 A short description
DESC M02 20210813 Some other desc
When I change the view to alter the format of the date field (CAST, CONVERT, FORMAT), the date field appears to be sent through as NULL values only
with the input source and your BCP commands I do not have a way to test this, however here is what I would try:
First, change your View definition to this:
CREATE VIEW [dbo].[SampleTable_in]
AS
SELECT [item],
[symbol],
CONVERT(NVARCHAR(8), [date], 112) AS [date],
[value]
FROM [dbo].[SampleTable]
GO
Then change the trigger definition to this:
CREATE TRIGGER [dbo].[SampleTable_in_trig]
ON [dbo].[SampleTable_in]
/*
Trigger on the insert view to facilitate:
1. Insertions into the table overwrite (update) any records that already exist
2. Sparse value updates (if the last value in the series is the same, value is not inserted)
3. Insertions where value is null are considered DELETES
*/
INSTEAD OF INSERT, UPDATE
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION
/* When inserted symbol, item, date exists and values don't match, update */
UPDATE u
SET [value] = i.[value]
FROM [dbo].[SampleTable] u
INNER JOIN INSERTED i ON u.[symbol] = i.[symbol]
AND u.[item] = i.[item]
AND u.[date] = CONVERT(DATE, i.[date], 112)
WHERE i.[value] IS NOT NULL
AND i.[value] != u.[value]
/* When inserted symbol, item, date exists and inserted value is null, delete */
DELETE [dbo].[SampleTable]
FROM [dbo].[SampleTable] u
INNER JOIN INSERTED i ON u.[symbol] = i.[symbol]
AND u.[item] = i.[item]
AND u.[date] = CONVERT(DATE, i.[date], 112)
WHERE i.[value] IS NULL
/* When inserted symbol, item does not exist and inserted value is not null, insert */
INSERT INTO [dbo].[SampleTable] ([item], [symbol], [date], [value])
SELECT
i.[item], i.[symbol], CONVERT(DATE, i.[date], 112), i.[value]
FROM
INSERTED i
LEFT OUTER JOIN
[dbo].[SampleTable] u ON u.[symbol] = i.[symbol]
AND u.[item] = i.[item]
WHERE
i.[value] IS NOT NULL
AND u.[symbol] IS NULL
/* When inserted symbol, item, date does not exist and value from prior date does not match inserted value, insert */
INSERT INTO [dbo].[SampleTable] ([item], [symbol], [date], [value])
SELECT
i.[item], i.[symbol], CONVERT(DATE, i.[date], 112), i.[value]
FROM
INSERTED i
LEFT OUTER JOIN
[dbo].[SampleTable] u ON u.[symbol] = i.[symbol]
AND u.[item] = i.[item]
AND u.[date] = CONVERT(DATE, i.[date], 112)
WHERE
i.[value] IS NOT NULL
AND u.[symbol] IS NULL
AND i.[value] != (SELECT TOP 1 [value]
FROM [dbo].[SampleTable] uu
WHERE uu.[symbol] = i.[symbol]
AND uu.[item] = i.[item]
AND uu.[date] < CONVERT(DATE, i.[date], 112)
ORDER BY [date] DESC)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
--EXEC proc_error_handler
END CATCH
GO
To summarize, I do not think that this is a problem with your trigger, rather it is a problem with either your BCP command and/or the View definition. In short, BCP cannot figure out how to turn your YYYYMMDD input strings into Date datatypes. The simple answer is to take that problem away from BCP and let the trigger figure it out.
From the OP's feedback this too put's NULL in the date column.
Therefore, I have to conclude that the problem is that BCP will not write to the [date] column when we modify it because then it appears to be a derived and thus non-writeable column in the view (see the rules for Writeable Views) with no backing store. On the one hand, making a write-through view with Instead Of triggers triggers like this is cutting-edge T-SQL and it's behavior for cases/conditions like this is not always well-documented. On the other hand, BCP is one of the oldest tools in SQL Server, that uses an (no longer documented) special interface to SQL server that doesn't always follow INSERT-like rules.
Taken together, I just have to say that I don't think that there's likely a way to get this approach to work the way that you want. So at this point, I think that your only reliable course of action is to abandon the write-through View with triggers approach and instead use a staging table.
I'd define the staging table like this:
CREATE TABLE [dbo].[SampleTable_in]
(
[item] VARCHAR(32) NOT NULL,
[symbol] VARCHAR(64) NOT NULL,
[date] NVARCHAR(8) NOT NULL,
[value] NVARCHAR(255) NOT NULL
)
This would replace your View, and you could either apply your (modified) triggers to it, or incorporate their logic into a stored procedure that runs after BCP finishes.