Insert avec select sur table avec Trigger d'insertion

Version imprimable

Hello,

j'ai un schéma de base qui utilise une table propriétaire pour générer les clés primaires que j'ai reprise de l'article sur les meta données dans la partie SQL de ce site.
Pour celà j'utilise la table Tmeta_RefTables_TBL :

Code:

1
2
3
4
5
6
7
8
9
CREATE TABLE [Tmeta_RefTables_TBL] (
	[TBL_ID] [bigint] NOT NULL ,
	[TBL_NOM] [varchar] (128) NOT NULL ,
	[TBL_PKREF] [bigint] NOT NULL ,
	CONSTRAINT [PK_Tmeta_RefTables_TBL] PRIMARY KEY  CLUSTERED 
	(
		[TBL_ID]
	)  ON [PRIMARY] 
) ON [PRIMARY]

et un trigger d'insert sur chaque table qui incrémente automatiquement la clé ..

Un pb se posait néanmoins pour insérer un jeu de données à partir d'un select j'ai donc fait la ps que je vous livre ici car j'aimerais savoir si il y a une meilleure méthode plus simple , plus optimisée pour faire un insert into... select ?
Merci à tous et toutes ;)

voici ma ps :

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
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
 
 
CREATE PROCEDURE PS_SYS_DB_INSERT_WITH_SELECT
                @SELECT_QUERY VARCHAR(2048),
		@NomTableCible as varchar(128),
		@NomTrigger as varchar(128)
                 AS
 
 
 
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
 
Declare @errMsg as varchar(256)
DECLARE @SQL_Query1 as varchar(4096)
DECLARE @SQL_Query2 as varchar(4096)
DECLARE @BorneMin as varchar(8)
DECLARE @TBL_ID as bigint
DECLARE @MaxIDGen as bigint
 
BEGIN TRANSACTION 
 
-- 1/ on chope la borne minimale de l'id
 
SELECT @BorneMin = CAST ( TBL_PKREF+1 as varchar(8) ) , @TBL_ID=TBL_ID from Tmeta_RefTables_TBL Where TBL_NOM=@NomTableCible
 
		IF @@ERROR<> 0 
		BEGIN 
			Set @errMsg = 'PS_SYS_DB_INSERT_WITH_SELECT - erreur lors du select @BorneMin'
			GOTO LBL_ERROR
		END
 
-- 2/ on Disable le trigger
--TRIGGER_INSERT_PK_Tmeta_TEST
EXECUTE('ALTER TABLE ' + @NomTableCible + ' DISABLE TRIGGER ' + @NomTrigger)
 
		IF @@ERROR<> 0 
		BEGIN 
			Set @errMsg = 'PS_SYS_DB_INSERT_WITH_SELECT - erreur lors du DISABLE TRIGGER'
			GOTO LBL_ERROR
		END
 
-- 3/ On créé la table temporaire qui va bien
 
SET @SQL_Query1 = ' SELECT IDENTITY(int,' + @BorneMin + ',1) as MyID,TSel.* INTO tmp_table FROM (' +  @SELECT_QUERY +') as Tsel'
 
EXECute(@SQL_Query1)
 
		IF @@ERROR<> 0 
		BEGIN 
			Set @errMsg = 'PS_SYS_DB_INSERT_WITH_SELECT - erreur lors de l''insertion des données ds la table temporaire : ' + @SQL_query1
			GOTO LBL_ERROR
		END
 
 
-- 3-1/ On récupère l'ID le plus grand pour MAJ Temat_RefTables_TBL
SELECT @MaxIDGen = IDENT_CURRENT('tmp_table')
		IF @@ERROR<> 0 
		BEGIN 
			Set @errMsg = 'PS_SYS_DB_INSERT_WITH_SELECT - erreur lors du select IDENT_CURRENT  '
			GOTO LBL_ERROR
		END
 
-- 4/ On insere dans la table
SET @SQL_Query2 ='INSERT INTO ' + @NomTableCible + ' SELECT * FROM tmp_table'
 
 
EXECUTE(@SQL_Query2)
 
IF @@ERROR<> 0 
BEGIN 
	Set @errMsg = 'PS_SYS_DB_INSERT_WITH_SELECT - erreur lors de l''insertion des données ds la table finale : ' + @SQL_query2
	GOTO LBL_ERROR
END
 
-- 5/ On remet le trigger en place
EXECUTE('ALTER TABLE ' + @NomTableCible + ' ENABLE TRIGGER ' + @NomTrigger)
 
		IF @@ERROR<> 0 
		BEGIN 
			Set @errMsg = 'PS_SYS_DB_INSERT_WITH_SELECT - erreur lors du ENABLE TRIGGER  '
			GOTO LBL_ERROR
		END
 
 
-- 6/ On drop la table temporaire avant de valider la transaction
DROP TABLE tmp_table
 
		IF @@ERROR<> 0 
		BEGIN 
			Set @errMsg = 'PS_SYS_DB_INSERT_WITH_SELECT - erreur lors du DROP TABLE tmp_table'
			GOTO LBL_ERROR
		END
 
-- 7/ On MAJ la table Tmeta_PK_ref avec la nouvelle clé de fin
UPDATE Tmeta_RefTables_TBL SET TBL_PKREF=@MaxIDGen WHERE TBL_ID=@TBL_ID
		IF @@ERROR<> 0 
		BEGIN 
			Set @errMsg = 'PS_SYS_DB_INSERT_WITH_SELECT - erreur lors du UPDATE Tmeta_RefTables_TBL SET TBL_PKREF=@MaxIDGen WHERE TBL_ID=@TBL_ID'
			GOTO LBL_ERROR
		END
 
 
COMMIT TRANSACTION 
 
GOTO LBL_END
 
LBL_ERROR:
ROLLBACK TRANSACTION 
RAISERROR (@ErrMsg,16,1)
 
LBL_END:
 
 
 
 
 
 
 
 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

27/10/2005, 18h04
SQLpro

je comprend rien ! pourrais tu être explicite et commencer par nous dire ce que tu veut faire ?

A +

je vais tenter d'etre plus clair, je m'excuse de la confusion causée.

Présentation du système

Soit les tables suivantes :

Tmeta_TEST : table contenant un id numérique et une chaine de caractère associée

TDWH_Source : table contenant des valeurs de type chaine de cxaratères "en vrac" à copier dans Tmeta_TEST

Tmeta_RefTables_TBL : table contenant la référence de toutes les tables utilisateur du système. Chaque tuple se compose d'un id unique (colonne TBL_ID) , associé au nom usuel de la table (colonne TBL_NOM), ainsi que la dernière valeur de clé primaire numérique générée dans chaque table (colonne TBL_PKREF).

crées de la manière suivante :
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 CREATE TABLE [Tmeta_TEST] ( [MyID] [bigint] NOT NULL , [DataChar] [varchar] (50) AS NULL , CONSTRAINT [PK_Tmeta_TEST] PRIMARY KEY CLUSTERED ( [MyID] ) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [TDWH_Source] ( [Valeurs] [varchar] (50) NOT NULL , [MaCondition] [varchar] (2) NOT NULL ) ON [PRIMARY] GO CREATE TABLE [Tmeta_RefTables_TBL] ( [TBL_ID] [bigint] NOT NULL , [TBL_NOM] [varchar] (128) NOT NULL , [TBL_PKREF] [bigint] NOT NULL , CONSTRAINT [PK_Tmeta_RefTables_TBL] PRIMARY KEY CLUSTERED ( [TBL_ID] ) ON [PRIMARY] ) ON [PRIMARY]
Considérant que le trigger suivant est crée sur la table Tmeta_Test :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 CREATE TRIGGER TRIGGER_INSERT_PK_Tmeta_TEST ON [dbo].[Tmeta_TEST] INSTEAD OF INSERT NOT FOR REPLICATION AS BEGIN --Build an INSERT statement ignoring inserted.PrimaryKey and --inserted.ComputedCol. DECLARE @RC bigint DECLARE @NOM_TABLE varchar(128) SET @NOM_TABLE='Tmeta_TEST' -- Set parameter values EXEC [dbo].[PS_SYS_DB_CALC_NEW_KEY] @NOM_TABLE,@RC OUTPUT INSERT INTO Tmeta_TEST SELECT @RC,[DataChar] FROM inserted END
avec la ps PS_SYS_DB_CALC_NEW_KEY qui renvoie la clé "indentity" à partir de la référence stockée dans Tmeta_RefTables_TBL pour la ligne relative à Tmeta_Test.. Je peut vous fournir le détail de la ps PS_SYS_DB_CALC_NEW_KEY au besoin ..

Ce que je voudrais faire

Je voudrais faire quelque chose comme :

Code:

INSERT INTO Tmeta_test(MyID,DataChar) SELECT IDENTITY(bigint,IDENT_CURRENT('Tmeta_Test'),1), VALEURS FROM TDWH_SOURCE WHERE MaCondition='CA'

Or je sais pertinement que celà ne fonctionnera pas, j'ai donc pour palier à celà créé la procédure stockée citée dans le message ci dessus.

17/11/2005, 16h10
bran_noz

reBonjour ,

je me permets de faire remonter ce sujet , pour savoir si mes explications complémentaires ont étés plus claires ? si vous auriez un conseil concernant ce pb à me donner?

Merci d'avance.

Bonjour ,
bien j'ai continué à chercher de mon coté et la solution finalemnt la plus élégante que j'ai pu trouver fut d'insérer avec un curseur directement dans le trigger :

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
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
 
ALTER  TRIGGER TRIGGER_INSERT_PK_Tmeta_TEST ON [dbo].[Tmeta_TEST] 
INSTEAD OF INSERT 
NOT FOR REPLICATION
AS
 
 
BEGIN
 
		DECLARE @RC bigint
		DECLARE @NOM_TABLE varchar(128)
		SET @NOM_TABLE='Tmeta_TEST'
 
	-- On detecte si l'insert est multirow ou pas 
	IF (SELECT COUNT(*) FROM Inserted) = 1
	BEGIN
		-- génération de l'iD
		EXEC [dbo].[PS_SYS_DB_CALC_NEW_KEY] @NOM_TABLE,@RC OUTPUT
 
		-- insertion de la ligne
		INSERT INTO Tmeta_TEST
		SELECT @RC,[DataChar]
		FROM inserted
	END
 
	ELSE
 
	BEGIN
	-- On cree un curseur sinon
	-- qui va inserer ligne après ligne 
		DECLARE IDCursor CURSOR LOCAL
		READ_ONLY	FOR 
			SELECT [MyID], [DataChar] FROM Inserted --[Ciel].[dbo].[Tmeta_TEST]
 
		DECLARE @myID int, 
			@DataChar varchar(50)
 
		OPEN IDcursor
 
		FETCH NEXT FROM IDCursor INTO @myID, @DataChar
 
		WHILE (@@fetch_status <> -1)
		BEGIN
 
		IF (@@fetch_status <> -2)
			BEGIN
 
				-- Si la valeur de clé existe deja alors on en génére une nouvelle
				EXEC [dbo].[PS_SYS_DB_CALC_NEW_KEY] @NOM_TABLE,@RC OUTPUT
 
				-- et puis on insere
				INSERT INTO Tmeta_TEST
				SELECT @RC, @DataChar 
 
			END
 
			-- next row 
 
			FETCH NEXT FROM IDCursor INTO @myID, @DataChar
 
		END
 
 
	END
 
END
 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Si vous avez des remarques optimisation elles seront toujours les bienvenues..

Merci.

23/12/2005, 14h38
Fabien Celaia

ce ne serait pas plus performant d'éviter le curseur et de faire le tout en 2 passes.

Update des champs qui ont une clé existante
Insert des autres.