May 16, 2023 at 11:29 am
I have a table with 3 columns
ColA ColB Currentdatetime(CDT)
A 10 CDT is inserted by default
B 20 CDT is inserted by default
C 30 CDT is inserted by default
This table is populated by a certain number of rows everytime a sqljob runs.Each time this happens I need a (n+1)4th row as below to be inserted below the (nth)3rd row with these values as below.
For the above example 4th row should be
Allrows 60 CDT <- Row 4 inserted automatically after the table is populated with above 3 rows (10+20+30 ->60 is inserted)
is inserted by default.
How do I achieve this
Thanks
May 16, 2023 at 12:30 pm
So, in your example, you'd have
D, 60, CDT
entered automatically.
How is this row going to be used in future? Is there anything special about this row which identifies it as a total?
Are all of the non-total rows inserted as a single batch, or one after the other (which would make a trigger difficult to implement).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 16, 2023 at 4:21 pm
Instead of entering a 4th row why not use a 4th column? Further what makes each row related to another row for if the CDT is automatically generated for each row as it is entered this means there will be no commonality, as currently presented, with any of the rows as follows:
ColA ColB DateTimeAdded(CDT) CommonTotal
A 10 2023-05-16 10:10:10.7654321 NULL
B 20 2023-05-16 10:10:10.7654332 NULL
C 30 2023-05-16 10:10:10.7654343 NULL
Now if instead of doing a Default of GETDATE( ) which is what I am presuming you are doing you instead within the INSERT statement grab the GETDATE( ) time just once and then apply that to all of the INSERTs again as Phil Parkin pointed out that these inserts are all being done at the same time, then you have a commonality item. Other than that I see no way of determining what records should be paired with what records. So let us assume, if we can, that you can make the DateTimeAdded non-unique then immediately after the initial INSERT you would do a SELECT using that DateTimeAdded as you would still have it as a variable to get your total and then do an UPDATE of all the records with that now CommonTotal and it would look as follows:
ColA ColB DateTimeAdded(CDT) CommonTotal
A 10 2023-05-16 10:10:10.7654321 60
B 20 2023-05-16 10:10:10.7654321 60
C 30 2023-05-16 10:10:10.7654321 60
As for the reason for a single numeric column rather than creating a new row, is because the CommonTotal actually does apply to each of these rows. However another Header table containing just the information of DateTimeAdded and CommonTotal might even be better as then nothing gets repeated. I hope that helps. However, this really comes down to better understanding your data when attempting to do a query of any sort.
May 17, 2023 at 2:30 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply