Any way to insert records when the table has default and identity in SQL Server?

Navigation
0
CREATE PROCEDURE USP_ADD_CLIENTE
@PNIDTIPDOCUMENTO INT,
@PSNUMDOCUMENTO VARCHAR(35),
@PSNOMBRES VARCHAR(40),
@PSAPEPAT VARCHAR(25),
@PSAPEMAT VARCHAR(25),
@PSCORREO VARCHAR(40),
@PSTELEF VARCHAR(25),
@PNESTADO INT,
@PSUSUREG VARCHAR(40)
AS
    INSERT CLIENTE VALUES (NULL,@PNIDTIPDOCUMENTO,@PSNUMDOCUMENTO,@PSNOMBRES,@PSAPEPAT,@PSAPEMAT,@PSCORREO,@PSTELEF,@PNESTADO,@PSUSUREG)
GO


EXEC USP_ADD_CLIENTE 1,'21245312','JUAN','CASTRO','ASDASDAS','[email protected]','124124',1,'CPEREZ'

My table has the PK Identity and the registration date set with today by DEFAULT, but I get the following error when I run my SP.

Mens 8101, Level 16, State 1, USP_ADD_CLIENT procedure, Line 21 Only one explicit value can be specified for the identity column of the 'CUSTOMER' table when a list of columns is used and IDENTITY_INSERT is ON.

PS: I have even tried to define my SP by specifying the date as default

INSERT CLIENTE VALUES (NULL,@PNIDTIPDOCUMENTO,@PSNUMDOCUMENTO,@PSNOMBRES,@PSAPEPAT,@PSAPEMAT,@PSCORREO,@PSTELEF,@PNESTADO,DEFAULT,@PSUSUREG) and it tells me that there can not be two null with identity. Thank you very much in advance.

    
asked by J. Carlos 06.11.2017 в 19:16
source

1 answer

2

So that the unanswered question is not open ...

In your insert, you should not assign value to the IDENTITY column. In your case, you must remove the NULL at the beginning. But this can only be achieved if you use the INSERT notation where you explicitly list the columns, which is the best practice anyway. 

INSERT CLIENTE (col1, col2, col3, ...) VALUES (value1, value2, value3, ...)
    
answered by 06.11.2017 в 20:06
How can I pass this exercise to for or while loops how to take data from a mysql table?