Do I need a loop? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Do I need a loop?

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2006-10-08 : 20:55:56

Hi,

I have a view which is a pivot of another table.

Each column in the pivot is a sign of the zodiac and I want to add a row from the pivot table into a table where its one row per zodiac sign.

The following trigger works if there's only one row inserted at a time...


CREATE TRIGGER [IO_Trig_INS_vw_StarSign_Prediction] ON [dbo].[vw_StarSign_Prediction]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON

DECLARE @date datetime
SELECT @date = Date FROM inserted

--DATE
INSERT tbl_StarSign_PredictionSet ( DATE ) VALUES ( @date )

DECLARE @ID int
SET @ID = @@IDENTITY

INSERT INTO tbl_StarSign_Prediction SELECT @ID, 1, i.Aries FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT @ID, 2, i.Taurus FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT @ID, 3, i.Gemini FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT @ID, 4, i.Cancer FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT @ID, 5, i.Leo FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT @ID, 6, i.Virgo FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT @ID, 7, i.Libra FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT @ID, 8, i.Scorpio FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT @ID, 9, i.Sagittarius FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT @ID, 10, i.Capricorn FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT @ID, 11, i.Aquarius FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT @ID, 12, i.Pisces FROM inserted i

END

The question is, is it possible to handle multiple row inserts using a single set of statements or must I have a loop which iterates over inserted?

Cheers, XF.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-10-08 : 21:40:32

You can do it using a single set of statements because the 'inserted' virtual table can have many rows and you can treat it like any other table (with a few limitations which probably shouldn't affect you)

HTH,

Tim

TSQLMan
Posting Yak Master

160 Posts

Posted - 2006-10-08 : 22:22:04

[code]
CREATE TRIGGER [IO_Trig_INS_vw_StarSign_Prediction] ON [dbo].[vw_StarSign_Prediction]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON

DECLARE @date datetime
SELECT @date = Date FROM inserted --Get Rid of this

--DATE
INSERT tbl_StarSign_PredictionSet ( DATE )
Select Date from inserted

DECLARE @ID int
SET @ID = @@IDENTITY --This will only return the @@identity of
the last row of the inserted records so if you want them unique you
may need to use a unique row from the inserted records, and do the
insert all at once.

INSERT INTO tbl_StarSign_Prediction SELECT SomeOtherUniqueColumn, 1, i.Aries FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT SomeOtherUniqueColumn, 2, i.Taurus FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT SomeOtherUniqueColumn, 3, i.Gemini FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT SomeOtherUniqueColumn, 4, i.Cancer FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT SomeOtherUniqueColumn, 5, i.Leo FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT SomeOtherUniqueColumn, 6, i.Virgo FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT SomeOtherUniqueColumn, 7, i.Libra FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT SomeOtherUniqueColumn, 8, i.Scorpio FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT SomeOtherUniqueColumn, 9, i.Sagittarius FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT SomeOtherUniqueColumn, 10, i.Capricorn FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT SomeOtherUniqueColumn, 11, i.Aquarius FROM inserted i
INSERT INTO tbl_StarSign_Prediction SELECT SomeOtherUniqueColumn, 12, i.Pisces FROM inserted i

END
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources