Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Downgrade INSERT with OUTPUT to Sql Server 2000 Expand / Collapse
Author
Message
Posted Sunday, July 14, 2013 4:46 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 11, 2014 10:37 AM
Points: 16, Visits: 80
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!
Post #1473412
Posted Sunday, July 14, 2013 5:00 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:24 PM
Points: 3,784, Visits: 8,489
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1473413
Posted Sunday, July 14, 2013 9:55 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 23, 2014 2:02 AM
Points: 1,252, Visits: 1,737
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


Post #1473431
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse