I'm not too experienced with database problems, so please excuse the wall of text, I believe the context is important to understand the problem and goal.
I receive a CSV file from another system every day that I want to retain in a database running on Microsoft SQL Server. To accomplish this, I have a PowerShell-script scheduled daily that uses the bcp utility to "load" the CSV (accounts.csv) into a table (
accounts_stage) that has the "exact" layout of the CSV file. In addition, I "write" the timestamp into another table (
accounts_stage table has a trigger set, and bcp is configured to fire that trigger.
After the import is finished, I want to "normalize" the data. For a basic example, assume this concerns only the contract number (in the CSV file a field called "Vnr"), which is a string. I have another table (
contracts), which gets filled by the trigger:
INSERT INTO dzp.contractid(vnr) SELECT DISTINCT a.Vnr FROM dzp.accounts_stage a WHERE NOT EXISTS( SELECT id.vnr FROM dzp.contractid id )
This should, and seems to accomplish, add all contract numbers that are ever imported to the table
contractids.contractId is a BIGINT PRIMARY KEY, IDENTITY, and auto increment.
I add the import-metadata to my table like this:
CREATE TABLE #IMPORTMETADATA ([importid] bigint ,[generated_timestamp] datetime ,[imported_timestamp] datetime ) INSERT INTO #IMPORTMETADATA SELECT TOP 1 [importid], [timestamp_import], [generated_timestamp] FROM [QGTAA].[dzp].[import] ORDER BY [timestamp_import] DESC
Next, the trigger should INSERT the data FROM
accounts, such that the
contractId is replaced (from the original string representation to the PRIMARY KEY of the
INSERT INTO [dzp].[accounts] ([Udnr] ,[ContractId] -- snip ,[generated_timestamp] ,[importid] ) SELECT a.[Udnr], cid.contractId, -- snip meta.[generated_timestamp], meta.[importid] FROM [QGTAA].[dzp].[accounts_stage] a LEFT JOIN dzp.contractid cid ON cid.vnr = a.Vnr CROSS JOIN #IMPORTMETADATA meta
However, this last step fails, because the SELECT operation in the last block yields rows where
contractId IS NULL, which I did not expect. Now I believe the issue is somewhere with the LEFT JOIN, but I'm not sure why.
Can someone please point me in the right direction on this specific problem?
I am not too experienced with databases, is this even a sane way of accomplishing what I intend, or is this a completely insane approach? Is there a better way?