Convert Old School to current ANSI/ISO SQL Standards

  • 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.

    • This topic was modified 3 months, 2 weeks ago by  Dennis Jensen. Reason: bracket issue in code section
  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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??

    • This reply was modified 3 months, 2 weeks ago by  Dennis Jensen.
    • This reply was modified 3 months, 2 weeks ago by  Dennis Jensen.
  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • Ah much thanks ScottPletcher I figured it was something I was just overlooking. That did the trick.

  • 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