November 15, 2023 at 9:15 pm
I have a situation where I ran into an old school query that I want to change into a more standardized version but I am not exactly sure how to do this as I am not fully understanding how to do this. I was under the understanding that:
FROM Table1, Table2 -- equated to -- FROM Table1 JOIN Table2
However in the following query I am not sure how to implement this JOIN
SELECT 'UPDATE'
,s.login_name
,i.batch_id
FROM INSERTED AS i, sys.dm_exec_sessions AS s
WHERE s.session_id = @@SPID;
Now there are a lot more fields being returned by this SELECT which is part of an INSERT INTO within an Update Trigger but most of that is irrelevant to changing the FROM clause JOIN into a more standardized FROM clause JOIN. Thanks in advance.
November 15, 2023 at 9:44 pm
That's the equivalent of a CROSS JOIN:
SELECT 'UPDATE'
,s.login_name
,i.batch_id
FROM INSERTED AS i
CROSS JOIN sys.dm_exec_sessions AS s
WHERE s.session_id = @@SPID;
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 16, 2023 at 12:03 am
Thanks for ScottPletcher but now I am running into another issue that I am not sure why. I have two tables, first one is the Primary table and the other is PrimaryHist table. I added a new column LineCount to both tables and I am now trying to create the Trigger but I get an error.
CREATE OR ALTER TRIGGER dbo.trSyncPrimaryHist_Update
ON dbo.Primary
FOR UPDATE
AS
BEGIN
INSERT INTO dbo.PrimaryHist
SELECT 'UPDATE'
,s.login_name
,i.batch_id
,s.LineCount
FROM INSERTED AS i
CROSS JOIN sys.dm_exec_sessions AS s
WHERE s.session_id = @@SPID;
END
GO
Error Received:
Msg 207, Level 16, State 1, Procedure trSyncPrimaryHist_Update, Line 9 [Batch Start Line 0]
Invalid column name 'LineCount'.
Again LineCount exists in both tables and when I query sys.dm_exec_sessions none of the columns exist within it. So how do I get this new column to be associated with sys.dm_exec_sessions if I cannot add it into the Trigger.
I can query and update both tables for the column and it shows but I cannot get the creation of the Trigger to not deny it saying it is an Invalid Column?? I cannot even use the old school version as I get the same error??
November 16, 2023 at 3:14 am
LineCount must exist in your table, not the system table; if so, then change the alias before the column name:
...
INSERT INTO dbo.PrimaryHist
SELECT 'UPDATE'
,s.login_name
,i.batch_id
,i.LineCount --<<--
FROM INSERTED AS i
CROSS JOIN sys.dm_exec_sessions AS s
WHERE s.session_id = @@SPID;
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 16, 2023 at 6:55 am
Ah much thanks ScottPletcher I figured it was something I was just overlooking. That did the trick.
November 16, 2023 at 11:11 am
Please use
INSERT INTO dbo.PrimaryHist (columnlist) SELECT
instead of INSERT INTO dbo.PrimaryHist SELECT
in case columns get rearranged
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply