0

I'm trying to insert values to a table and getting this error message, 

Cannot insert a value NULL into column "RowID".

Do I have to find the last RowID value in the table and manually input every time I do an insert like the insert below? 

insert into table1 (RowID, Name, Gender) values (801, John, Male)

Is there a way to insert like: 

insert into table1 (RowID, Name, Gender) values (AutoInputRowID, John, Male)
J-Alex
  • 6,881
  • 10
  • 46
  • 64
voltaren
  • 21
  • 1
  • 5
  • What is the datatype of RowID column? If you can try the IDENTITY property this can become automatic. Refer: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property – kamleshrao Jul 26 '17 at 08:44

4 Answers4

2

Assuming that the RowID column is an auto increment column, you can simply omit it from the insert in order to get SQL Server automatically assign a value to it:

insert into table1 (Name, Gender)
values ('John', 'Male')

If RowID is not an auto increment column and you need such functionality, then you might have a bit of work to do, q.v. here:

SQL Server add auto increment primary key to existing table

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
1

Just my two cents:

You can actually convert an existing column to an identity. As long as the column being changed is a unique number, it will automatically start incrementing from max number+1. You can also opt to reseed the value so if your max value was for example 100. You could choose to seed from 200 onwards so that your next insert will give you 201.

This can be done in SQL Server Management Studio. Edit the table in question in design mode. Select the column you want to modify. There is a property called "Identity Specification", expand it and change the value "Is Identity" = "Yes" and save the table. If you want to reseed (start from a particular number), set this value in the "Identity Seed" property.

See some of the answers under Adding an identity to an existing column for more details.

KtX2SkD
  • 752
  • 4
  • 12
SQLAndOtherStuffGuy
  • 214
  • 1
  • 5
  • How-to, please. – KtX2SkD Jul 27 '17 at 07:40
  • 1
    This can be done in SQL Server Management Studio. Edit the table in question in design mode. Select the column you want to modify. There is a property called "Identity Specification", expand it and change the value "Is Identity" = "Yes" and save the table. If you want to reseed (start from a particular number), set this value in the "Identity Seed" property. See here for more details. [Adding an identity to an existing column](https://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column) – SQLAndOtherStuffGuy Jul 27 '17 at 07:51
0

Derive it as you're inserting, like so:

INSERT INTO table1 (RowID, Name, Gender)
SELECT MAX(RowID) + 1, 'John', 'Male'
FROM table1

Useful if you don't have, don't want, and/or can't use an identity. Just consider the performance impact (if any).

If inserting more than one record at a time, you'll probably want to use ROW_NUMBER() shifted by the result of MAX from above.

KtX2SkD
  • 752
  • 4
  • 12
0

You have 2 options to make it: 1- Set the column RowID as Identity (but you can do it only if your table is empty) 2- create a function that give you the maximum value for RowID in the table:

 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE FUNCTION dbo.getMaxValue ()
 RETURNS int
 AS
 BEGIN

 DECLARE @valRet int
 SELECT @valRet=MAX(RowID) + 1
 FROM table1 ; 

 RETURN @valRet

 END
 GO

And after you call the function in your insert command:

insert into table1 (RowID, Name, Gender) values (dbo.getMaxValue(), John, Male)
FabioTos
  • 41
  • 5