Jump to content
Sign in to follow this  
Beantreeze

Loading data to multiple tables in a transaction

Recommended Posts

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

Share this post


Link to post

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

Share this post


Link to post

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.

Share this post


Link to post

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?

Share this post


Link to post

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.

Share this post


Link to post

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
);

 

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×