Downgrade INSERT with OUTPUT to Sql Server 2000

• TBIG

  SSC-Addicted

  Points: 479

  More actions

  July 14, 2013 at 4:46 pm

  #144429

  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!

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 14, 2013 at 5:00 pm

  #1632267

  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

• Andrew G

  SSChampion

  Points: 12809

  More actions

  July 14, 2013 at 9:55 pm

  #1632280

  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