CODEWITHSUNDEEP
sql-serverdatabasestored-proceduresidentity
Rosario
Rosario

Reputation: 67

Error while inserting data with stored procedure in table with shared identity primary key

I've got a few tables linked together where data should be inserted to using a stored procedure. The tables are:

create table contactpersoon
(
    contactpersoonnr    integer identity(1,1),
    klantnr             integer,
    naam                varchar(50) not null,
    telefoonnr          varchar(10) not null,
    emailadres          varchar(50) not null,

    constraint pk_contactpersoon 
        primary key(contactpersoonnr, klantnr),
    constraint fk_contactpersoon_klantnr 
        foreign key(klantnr) references klant(klantnr)
)

create table klant 
(
    klantnr        integer identity(1,1) primary key,
    bedrijfsnaam   varchar(50) not null
)

create table Logins
(
    GebruikersNaam  varchar(30),
    Wachtwoord      varchar(30),
    Klantnr         int,
    MdwNr           int,

    constraint pk_logID primary key(GebruikersNaam),
    constraint fk_klantnr foreign key(klantnr) references klant(klantnr),
    constraint fk_mdwnr foreign key(mdwnr) references medewerker(mdwnr)
)

Stored procedure for adding data to these tables:

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'spKlantAanmaken') 
   DROP PROCEDURE spKlantAanmaken
GO 

Create Procedure spKlantAanmaken
(
    @bedrijfsnaam as varchar(255),
    @contactnaam as varchar(255),
    @telnr as integer,
    @email as varchar(255),
    @gebruikersnaam as varchar(255),
    @wachtwoord as varchar(255)
)
AS
Begin transaction 
Declare @klantnr integer
Declare @contactpersoonnr integer

Insert into Klant Values (@klantnr, @bedrijfsnaam);
Insert into contactpersoon values(@contactpersoonnr, @klantnr, @contactnaam, @telnr, @email);
Insert into Logins values (@gebruikersnaam, @wachtwoord ,@klantnr, NULL);
Select * from contactpersoon
    IF @@ERROR <> 0 
    BEGIN 
        ROLLBACK 
        RAISERROR ('Error tijdens uitvoeren van stap 2.', 16, 1) 
        RETURN 
    END 
    COMMIT 
GO

I don't know if it is necessary to use these identity values in the inserts. If I try this stored procedure I get the following error:

Msg 8101, Level 16, State 1, Procedure spKlantAanmaken, Line 923
An explicit value for the identity column in table 'Klant' can only be specified when a column list is used and IDENTITY_INSERT is ON.

If I remove the identity values from the insert I get this error:

Msg 213, Level 16, State 1, Procedure spKlantAanmaken, Line 923
Column name or number of supplied values does not match table definition.

What am I doing wrong?

Upvotes: 0

Views: 812

Answers (5)

jamiedanq
jamiedanq

Reputation: 967

When you use Identity, the columns on which the identity is applied need not be in your INSERT statement VALUES. So edit your code like below

EDIT

It also seems you are missing out the columns you are trying to insert into

Insert into Klant (bedrijfsnaam) Values (@bedrijfsnaam)
Insert into contactpersoon (klantnr, contactnaam, telnr, email) Values (@klantnr, @contactnaam, @telnr, @email)

It seems all the answers saying the same thing so hope your issued is solved

Upvotes: 1

Raj
Raj

Reputation: 10853

Edit these two lines in your SP

Insert into Klant (bedrijfsnaam) 
Values (@bedrijfsnaam);
Insert into contactpersoon(klantnr,naam,telefoonnr,emailadres)
values(@klantnr, @contactnaam, @telnr, @email);

Provide a column list, excluding the identity columns in the insert statements

Upvotes: 0

Chris Tophski
Chris Tophski

Reputation: 960

Just specify the column names AND the contents in the INSERT statement like:

INSERT INTO klant (bedrijfsnaam) VALUES ('XYZ');

If you don't specify the column name list, the SQL interpreter implies, you want the identity column, too. In this case you would want to set data for 2 columns, but only provide one content element, which explains the latter error message.

Upvotes: 0

Arulkumar
Arulkumar

Reputation: 13237

Avoid the identity columns klantnr, contactpersoonnr in the INSERT query and explicitly define your column names:

So the below code will work in your case:

Insert into Klant(bedrijfsnaam) Values (@bedrijfsnaam);

Insert into contactpersoon(klantnr, naam, telefoonnr, emailadres) values(@klantnr, @contactnaam, @telnr, @email);

Upvotes: 0

marc_s
marc_s

Reputation: 754598

Since you have identity columns, you must specify the list of columns to insert into, in your INSERT statement, and not supply a value for the identity column - like this:

Instead of 

Insert into Klant Values (@klantnr, @bedrijfsnaam);

use

Insert into Klant(bedrijfsnaam) Values (@bedrijfsnaam);

and do this for all your INSERT operations.

This is a generally accepted "Best Practice" for any time you insert something into a table - it is recommend to always explicitly specify the list of columns in your table that you're inserting into (to avoid annoying errors and surprises).

Upvotes: 1

Related Questions