Loading data to multiple tables in a transaction

[Beantreeze 1]

Hello -

 

I think I must just be missing something, but I wrote some code that creates a list of strings setting up a transaction that writes data to a parent table and several child tables, linked by the key generated from the record in the parent table. (We have text files containing disparate data in a single record, all keyed by location and date/time. It had worked but now is not. I have done some editing of the code and tweaking of the table structures, but none I can think of that would cause the issue.

 

The app loads the data from 2 text files into 2 temporary tables that are then queried in the sequence created in the transaction. I get all the records in the parent table, but none in any of the child tables. I'm at my wits end trying to figure out why it's not working. If someone can point me to something, even if it embarrasses me to no end, I'd be ever grateful.

 

I have attached the generated transaction. Thanks.

 

Teri

SQLStr_Append.txt

[Lars Fosdal 1792]

Which database do you use?

 

Attached the reformatted SQL for readability.

 

Personally I would never use a timestamp for a join, but that is probably just me.

test.sql

[Beantreeze 1]

Thank you for your reply. I am using MS SQL Server 2019.

 

Normally I would not use a timestamp for joining, either, but that is the only unique number in each record of the text files. Also, though the column name is TIMESTAMP, it's actually a date/time value to specified to the minute. Would it be better to somehow add an integer identity column to the temporary tables to join? The only issue I might have with that is, while in theory the records in the values file match the records in the quality file, I should not assume that, I believe.

[Lars Fosdal 1792]

Have you looked into which joins that fail? 

Is it possible to see any differences in the stored data / parameters that can cause joins that fail?

Is the type strictly identical - ie both parameters and tables columns are of the TIMESTAMP type?

[Beantreeze 1]

That was my next move - to check the data types to make sure they're compatible. Also, I'm going to put some code in to output the results of the individual queries to a text file, to make sure they have data. 

 

I appreciate your replies.

[Lars Fosdal 1792]

It seems that the @Inserted_Values temp table does not use TIMESTAMP.

declare @Inserted_Values table (
    WX_5Min_Key int NOT NULL,
    [DateTime] DateTime NOT NULL
);