Downgrade INSERT with OUTPUT to Sql Server 2000

  • How do I convert the following Sql script so that it will run on Sql Server 2000 (which does not support the OUTPUT feature?

    --Create new locations and capture Location Name and new LocationIDs, AreaIDs in temp table _NewLocationis

    INSERT INTO Locations ( [LocationName], [LocationOrder], [LocationNotes], [AreaID], [NumberInArea] )

    --Add new inserted Locations into temp table _NewLocations

    OUTPUT INSERTED.LocationID, INSERTED.LocationName, INSERTED.AreaID INTO _NewLocations (LocationID, LocationName,AreaID)

    SELECT LocationName, LocationOrder, LocationNotes, @NewAreaID, NumberInArea

    FROM Locations

    WHERE AreaID=@AreaID

    ORDER BY LocationID

    Thanks!

  • You could create a trigger for insert

    CREATE TRIGGER TI_Locations ON Locations

    AFTER INSERT

    AS

    INSERT INTO _NewLocations (LocationID, LocationName,AreaID)

    SELECT LocationID, LocationName, AreaID

    FROM Inserted

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Trigger is probably better, or put into a temp table and roll it all into a single transaction in case of failure.

    BEGIN TRAN

    --Create new locations and capture Location Name and new LocationIDs, AreaIDs in temp table _NewLocationis

    SELECT LocationName, LocationOrder, LocationNotes, @NewAreaID, NumberInArea

    INTO #LOCATIONS

    FROM Locations

    WHERE AreaID=@AreaID

    --ORDER BY LocationID

    INSERT INTO Locations ( [LocationName], [LocationOrder], [LocationNotes], [AreaID], [NumberInArea] )

    --Add new inserted Locations into temp table _NewLocations

    --OUTPUT INSERTED.LocationID, INSERTED.LocationName, INSERTED.AreaID INTO _NewLocations (LocationID, LocationName,AreaID)

    SELECT LocationName, LocationOrder, LocationNotes, @NewAreaID, NumberInArea

    FROM #LOCATIONS

    IF @@ERROR<> 0

    ROLLBACK

    INSERT INTO _NewLocations (LocationID, LocationName,AreaID)

    SELECT LocationID, LocationName, AreaID

    FROM #LOCATIONS

    IF @@ERROR<> 0

    ROLLBACK

    ELSE

    COMMIT

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply