[2005] INSTEAD OF INSERT : valeur de Colonne IDENTITY

Version imprimable

Bonjour,

j'ai un soucis avec une syntaxe que je ne résouds pas.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 declare @t table ( x int , y int ) insert @t values (1, 1) insert @t values (2, 2) insert @t values (3, 3) insert @t values (4, 4) insert @t values (5, 5) declare @t2 table ( x int , y int ) insert @t2 (x) output t.x AS x, inserted.x AS y select x from @t as t
Ce qui me génère l'erreur

Citation:

Msg 4104, Level 16, State 1, Line 24
The multi-part identifier "t.x" could not be bound.

Vous pouvez m'aider ?

Merci.

14/09/2009, 13h38
Sergejack

Ok, j'ai trouvé.

INSERT n'a pas de clause FROM comme delete et update.
Le FROM du select n'a aucun rapport avec le INSERTstatment et ne peut donc pas être utilisé dans la clause OUTPUT (seul inserted peut y être référé).

---

J'ai pourtant besoin de pouvoir récupérer d'autres données que celles disponible via inserted, car lors d'une insertion multiple dans une table j'ai besoin de savoir quel identifiant (PK INDENTITY) a été donné à quelle ligne.

Est-ce que les plus calés, savez comment faire ?

Bonjour,

Vous pouvez passer par un trigger pour cela sur l'événement AFTER INSERT

Exemple :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
USE tempdb;
GO 

CREATE TABLE TEMP
(
 x INT IDENTITY(1,1) NOT NULL,
 y INT NOT NULL
);


CREATE TRIGGER tr_test
ON TEMP
AFTER INSERT
AS
 SELECT x FROM INSERTED;
GO


INSERT INTO TEMP (y) VALUES (1);
INSERT INTO TEMP (y) VALUES (2);
INSERT INTO TEMP (y) VALUES (3);
INSERT INTO TEMP (y) VALUES (4);
INSERT INTO TEMP (y) VALUES (5);

14/09/2009, 14h37
Sergejack

Le problème est que je dois faire cette opération dans un trigger INSTEAD OF et cela n'est pas contournable puisque mon trigger porte sur une view.

J'ai trouvé comment faire.
Ma solution est hard-core mais efficace.

1 - rajouter une colonne virtuelle de type uniqueidentifier sur ma view.

Code:

1
2
3
4
5
6
7
8
9
CREATE VIEW vDATA_tHandicaps
AS
SELECT
	*
	, CAST(NULL AS BIT) AS ViaSynchro
	, CAST(NULL AS UNIQUEIDENTIFIER) AS DATA_ID
FROM tHandicaps
GO

2 - insérer des dummies (lignes reiviales) dans la table.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
CREATE TRIGGER vDATA_tHandicaps_IOINSERT
ON vDATA_tHandicaps
INSTEAD OF INSERT
AS
BEGIN
/*
1. Insert dummies : Must occur first to set the ID (IDENTITY COLUMN)
2. Synchro preparation
3. Update
4. Synchro insertion
*/
	SET NOCOUNT ON;

	DECLARE @InsertedID TABLE
	(
		Id BIGINT
	)

	-- INSERT tHandicaps : Dummy entries
	INSERT tHandicaps
	(
		IdMember
		, EntryDate
		, Handicap
		, OriginClubId
		, Formula
		, Remark
		, QualificationStatus
		, TypeOfAdjustement
		, OldHcp
	)
	OUTPUT inserted.Id INTO @InsertedID(Id)
	SELECT
		1
		, '01-01-1900'
		, 0
		, 1000
		, ''
		, ''
		, 1
		, 1
		, 0
	FROM inserted AS H

3 - Établir une table qui pourra faire le lien entre une ligne à insérer* et une ligne de dummies.
Il faut un mécanisme pour faire une association arbitraire 1 à 1 (on associe un id non utilisé à n'importe quelle ligne à insérer*). Pour mettre en place ce mécanisme, je me sert de ROW_NUMBER().

* puisque les insertions sont déjà faite, les lignes ne sont en fait plus que des mises à jour à appliquer aux dummies.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- @HcpSynchro
	DECLARE @HcpSynchro TABLE
	(
		Id BIGINT
		, ViaSynchro BIT
		, DATA_ID UNIQUEIDENTIFIER
	)

	INSERT @HcpSynchro
	(
		ViaSynchro
		, DATA_ID
	)
	SELECT
		I.ViaSynchro
		, I.DATA_ID
	FROM inserted AS I

	-- Random assignement of real id to virtual id
	WITH Assignment
	(
		DATA_ID
		, Id
	)
	AS
	(
		SELECT 
			S.DATA_ID
			, IID.Id
		-- Match Row - Row
		FROM (
			SELECT
				S.DATA_ID
				, ROW_NUMBER() OVER(ORDER BY S.DATA_ID ASC) AS Row
			FROM @HcpSynchro AS S
		) AS S
		INNER JOIN (
			SELECT
				IID.Id
				, ROW_NUMBER() OVER(ORDER BY IID.Id ASC) As Row
			FROM @InsertedID AS IID
		) AS IID ON (
			IID.Row = S.Row
		)
	)
	
	UPDATE @HcpSynchro
	SET Id = A.Id
	FROM Assignment AS A
	WHERE [@HcpSynchro].DATA_ID = A.DATA_ID

4- Finallement on met à jour nos dummies grâce au lien 1 - 1 établi entre l'id de la ligne et l'identifieur unique (DATA_ID).

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
UPDATE @HcpSynchro
	SET Id = A.Id
	FROM Assignment AS A
	WHERE [@HcpSynchro].DATA_ID = A.DATA_ID
 
	-- UPDATE tHandicaps
	UPDATE tHandicaps
	SET
		IdMember = H.IdMember
		, EntryDate = H.EntryDate
		, Handicap = H.Handicap
		, OriginClubId = H.OriginClubId
		, Formula = H.Formula
		, Stableford = H.Stableford
		, Csa = H.Csa
		, CsaStableford = H.CsaStableford
		, Remark = H.Remark
		, IdCompetition = H.IdCompetition
		, QualificationStatus = H.QualificationStatus
		, ForeignKey = H.ForeignKey
		, TypeOfAdjustement = H.TypeOfAdjustement
		, EventDate = H.EventDate
		, NbHoles = H.NbHoles
		, OldHcp = H.OldHcp
	FROM @HcpSynchro AS S
	INNER JOIN inserted AS H ON (
		H.DATA_ID = S.DATA_ID
	)
	WHERE tHandicaps.Id = S.Id
END