Insertion de lignes dans une table avec une seule colonne IDENTITY

Question

J'ai une table Administrator avec une seule colonne, adminId, qui est la clé primaire. En raison de règles commerciales, il doit en être ainsi.

J'aimerais comprendre une fois pour toutes comment je peux écrire des procédures stockées qui insèrent des valeurs dans des tables comme celle-ci. J'utilise SQL Server et T-SQL et j'ai essayé d'utiliser SCOPE_IDENTITY() mais cela ne fonctionne pas puisque la table a INSERT_IDENTITY à false ou off.

J'aimerais vraiment ne pas avoir à insérer une valeur fictive juste pour pouvoir insérer une nouvelle ligne. Merci !

Answer 1

Si vous avez une colonne qui est une IDENTITÉ, il suffit de faire ceci

INSERT MyTable DEFAULT VALUES;  --allows no column list. The default will be the IDENTITY
SELECT SCOPE_IDENTITY();

Si vous n'avez pas d'identité, alors pouvez-vous la définir ? C'est la meilleure façon et utiliser le SQL ci-dessus.

Si ce n'est pas le cas, vous voulez insérer une nouvelle ligne.

INSERT MyTable (admidid)
OUTPUT INSERTED.admidid --returns result to caller
SELECT ISNULL(MAX(admidid), 0) + 1 FROM MyTable

Notes :

• Sous des charges élevées, la solution MAX peut échouer avec des doublons.
• SCOPE_IDENTITY est après le fait, pas avant
• SCOPE_IDENTITY ne fonctionne qu'avec une colonne IDENTITY. Idem pour toute idiotie utilisant IDENT_CURRENT
• La clause de sortie remplace SCOPE_IDENTITY pour la solution MAX.

Answer 2

Vous devez ajouter l'instruction IDENTITY_INSERT à votre instruction select :

SET IDENTITY_INSERT MyTable ON

INSERT INTO MyTable
(AdminCol)

SELECT AdminColValue

 FROM Tableb

Quand vous aurez fini, n'oubliez pas de vous rappeler de

SET IDENTITY_INSERT MyTable OFF

Voici une bonne description du fonctionnement de BOL : http://msdn.microsoft.com/en-us/library/aa259221(SQL.80).aspx

Answer 3

@Phil : Ne voulez-vous pas dire que votre table a deux (2) colonnes, la colonne PK auto-incrémentée et une colonne AdminName ? S'il n'y a qu'une seule colonne où le nom d'administrateur va, le nom d'administrateur est le PK et vous ne pouvez pas auto-incrémenter une chaîne, bien sûr. Les règles de gestion prévoient-elles que vous fassiez d'un nom d'utilisateur Windows entièrement qualifié la clé primaire ? Cela serait viable et logique, car vous n'auriez alors pas besoin d'un index unique alternatif sur la colonne AdminName.

Mais si votre tableau a deux colonnes, pas une :

Dans SQLServer, l'auto-incrément fait partie de la définition de la table/colonne. Vous définissez la colonne comme un nombre entier et vous en faites également une colonne d'identité. en spécifiant l'incrément, généralement 1, mais cela peut être 2, 5, 10 ou autre. Pour insérer une ligne, il suffit d'insérer la ou les valeurs des autres colonnes et de ne rien faire avec la colonne PK :

insert into T
(foo)   -- column(s) list
values('bar') -- values list

Votre procédure stockée qui effectue l'insertion peut faire de SCOPE_IDENTITY une valeur RETURN ou SCOPE_IDENTITY peut être renvoyé au client comme paramètre OUT.

P.S. SCOPE_IDENTITY() renvoie la valeur d'identité autoincrémentée la plus récemment générée dans l'étendue actuelle ; elle ne génère pas la valeur d'identité suivante.

EDITAR:

Vraisemblablement, votre table Administrateurs contient un ensemble d'administrateurs. Mais si elle n'a aucune colonne autre que la colonne de clé primaire entière, il n'y a aucun moyen d'identifier les administrateurs ; la seule chose que vous pouvez faire est de les distinguer les uns des autres. Cela ne vous mène pas très loin. Mais si votre table Administrateur avait l'une des structures suivantes :

ID   INTEGER PRIMARY KEY AUTOINCREMENT
windowsusername   varchar(50)  (unique index)

OU

windowsusername varchar(50) primary key

vous seriez en mesure de faire référence à la table de l'administrateur à partir d'autres tables, et les clés étrangères auraient un sens. Et c'est précisément ce qui manque à une table constituée d'une seule colonne d'entiers : du sens.

En ayant deux colonnes, vous pourriez alors avoir une procédure stockée pour faire cela :

insert into Administrators
(windowsusername)
values('mydomain\someusername');
return SCOPE_IDENTITY();

et votre programme-client obtiendrait comme valeur de retour l'identifiant autoincrémenté qui a été autogénéré et attribué à la ligne nouvellement insérée. Cette approche est la pratique habituelle, et j'irais même jusqu'à dire qu'elle est considérée comme une "meilleure pratique".

P.S. Vous mentionnez que vous ne saviez pas comment "insérer une valeur" si vous "n'aviez rien à insérer". Il y a là une contradiction. Si vous n'avez rien à insérer, pourquoi insérer ? Pourquoi créer, par exemple, un nouvel enregistrement CLIENT si vous ne savez absolument rien du client ? Pas son nom, sa ville, son numéro de téléphone, rien ?

Answer 4

Lorsque nous définissons la propriété d'identité d'une colonne "On", nous n'avons pas besoin d'insérer explicitement des valeurs dans cette colonne. Le serveur SQL insère implicitement des valeurs dans la colonne d'identité en fonction de la valeur incrémentale. Nous allons voir ici comment insérer explicitement des valeurs dans la colonne d'identité. Tout d'abord, nous allons créer une table avec une colonne d'identité.

Voici le script pour créer une table avec une colonne d'identité :-.

create table Identitytable(id int identity,Name varchar(50))

Dans la table "Identitytable", j'ai défini la colonne "Id" comme une colonne d'identité. Nous allons insérer quelques lignes dans cette table sans insérer explicitement des valeurs dans la colonne "Id".

insert into Identitytable(Name) values('Neeraj')
insert into Identitytable(Name) values('Raj')
insert into Identitytable(Name) values('Akshay')
insert into Identitytable(Name) values('Ankit')
insert into Identitytable(Name) values('Vikas')

Nous allons exécuter le script ci-dessus pour insérer 5 lignes dans la table "Identitytable" et vérifions ce qui est inséré dans la table.

Dans l'image ci-dessus, nous pouvons voir les valeurs de la colonne id (1,2,3,4,5), alors que nous n'avons pas explicitement inséré les valeurs dans la colonne "Id". Essayons maintenant d'insérer explicitement des valeurs dans la colonne "Id".

Dans le script ci-dessous, nous essayons d'insérer la valeur "9" dans la colonne "Id" explicitement, voyons quel est le résultat de la requête d'insertion ci-dessous.

insert into Identitytable(Id,Name) values(9,'Mamta')