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: Monday, July 15, 2013 7:14 AM
Points: 12, Visits: 70
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 @ 9:24 PM
Points: 3,342, Visits: 7,216
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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: Yesterday @ 12:16 AM
Points: 1,151, Visits: 1,588
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