Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to insert into a table with just one IDENTITY column?

(Came up with this question in the course of trying to answer this other one)

Consider the following MS-SQL table, called GroupTable:

 GroupID ------- 1   2   3   

where GroupID is the primary key and is an Identity column.

How do you insert a new row into the table (and hence generate a new ID) without using IDENTITY_INSERT ON?

Note that this:

INSERT INTO GroupTable() Values ()    

... won't work.

edit: we're talking SQL 2005 or SQL 2008 here.

like image 620
codeulike Avatar asked May 11 '09 22:05

codeulike


People also ask

Can you insert into an identity column?

An explicit value for the identity column in table 'Students' can only be specified when a column list is used and IDENTITY_INSERT is ON. In simple words, the error says that since the flag IDENTITY_INSERT is off for the Id column, we cannot manually insert any values.

Can you implicitly insert an identity column in a table?

Yes. That's all you need to do. SET ON, write your code, SET OFF at the end.

How do I insert data into a single column?

INSERT INTO table(column1, column2,...) VALUES (value1, value2,...); To insert a row into a table, you need to specify three things: First, the table, which you want to insert a new row, in the INSERT INTO clause. Second, a comma-separated list of columns in the table surrounded by parentheses.


2 Answers

This should work:

INSERT INTO GroupTable DEFAULT VALUES  
like image 83
DJ. Avatar answered Sep 19 '22 06:09

DJ.


Here you go:

INSERT INTO GroupTable DEFAULT VALUES 
like image 44
tofi9 Avatar answered Sep 19 '22 06:09

tofi9