Inserting Rows into a Table with One Identity Column Only

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

This should work:

INSERT INTO GroupTable DEFAULT VALUES

Inserting rows into a table with one IDENTITY column only

If you have one column that is an IDENTITY, just do this

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

If you don't have identity, then can you set it? This is the best way.. and use the SQL above.

If not, you want to insert a new row

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

Notes:

Under high loads the MAX solution may fail with duplicates
SCOPE_IDENTITY is after the fact, not before
SCOPE_IDENTITY only works with an IDENTITY column. Ditto any idiocy using IDENT_CURRENT
The output clause replaces SCOPE_IDENTITY for the MAX solution

Insert into a table containing only identity column

Simply, enable IDENTITY_INSERT for the table. That looks like this:

SET IDENTITY_INSERT IdentityTable ON

INSERT INTO Seq2(val) VALUES (1)

SET IDENTITY_INSERT IdentityTable OFF

Keep in mind :

It can only be enabled on one table at a time. If you try to enable
it on a second table while it is still enabled on a first table SQL
Server will generate an error.
When it is enabled on a table you must specify a value for the
identity column.
The user issuing the statement must own the object, be a system
administrator (sysadmin role), be the database owner (dbo) or be a
member of the db_ddladmin role in order to run the command.
SELECT SCOPE_IDENTITY() // to get last identity value generated in the same session and scope
SELECT @@IDENTITY // to get the last identity vaue generated in a session irrespective of scope

How to insert into a table with just one IDENTITY column (SQL Server CE)

Using Default Values works for identity columns on the standard version of SQL. I can't see any reason why it wouldn't work on CE.

In your case you would do something like this:

Insert Into Target
Default Values

Edit:

This issue looks like it is specific to SQL CE.

The only other thing I could suggest would be to add another column to your table, such as DateInserted.

Insert Into Target (DateInserted)
Values (getdate())

This should then insert a new row thus generating a new ID.

If you can change your table structure then you could us a UniqueIdentifier instead.

Create Table Target
(
IDColumn uniqueidentifier not null
)

Insert Into Target
Values (newId())

How to insert into a table with just one IDENTITY column (SQL Express)

 INSERT INTO dbo.TableWithOnlyIdentity DEFAULT VALUES

This works just fine in my case. How are you trying to get those rows into the database? SQL Server Mgmt Studio? SQL query from .NET app?

Running inside Visual Studio in the "New Query" window, I get:

The DEFAULT VALUES SQL construct or
statement is not supported.

==> OK, so Visual Studio can't handle it - that's not the fault of SQL Server, but of Visual Studio. Use the real SQL Management Studio instead - it works just fine there!

Using ADO.NET also works like a charm:

using(SqlConnection _con = new SqlConnection("server=(local);
                             database=test;integrated security=SSPI;"))
{
    using(SqlCommand _cmd = new SqlCommand
            ("INSERT INTO dbo.TableWithOnlyIdentity DEFAULT VALUES", _con))
    {
        _con.Open();
        _cmd.ExecuteNonQuery();
        _con.Close();
    }
}

Seems to be a limitation of VS - don't use VS for serious DB work :-)
Marc

Using CakePHP to insert rows into a table with one IDENTITY column only

For demonstration purposes the current model is written as '__THE_CURRENT_MODEL__' which is, of course, not a proper CakePHP model name. Duh!

    public function add() {
        if( $this->request->is('post') ){
            $this->__THE_CURRENT_MODEL__->create();
            if( $this->__THE_CURRENT_MODEL__->save(
                array(
                    '__THE_CURRENT_MODEL__' => array( 'id' => 'NULL' )
                )
            ) ){
                $this->Flash->success(__('The __current_model__ has been saved.'));
                return $this->redirect( array( 'action' => 'index' ) );
            }else{
                $this->Flash->error(__('The __current_model__ could not be saved. Please, try again.'));
            }
        }
    }

As you should note - the value 'NULL' is not NULL .. which would result in no query at all - the id column is numeric.

Insert values into identity column manually and automatically in one query

Some simulation identity insertion:

CREATE TABLE #Test (Id int IDENTITY(1,1) PRIMARY KEY, Value int)

INSERT INTO #Test 
VALUES
(100),
(200)

SELECT *
FROM #Test AS t

DECLARE @Temp TABLE (Id int, Value int, SetId bit)
INSERT INTO @Temp 
VALUES
(-1, 1,1),
(-2, 2,1),
(-1, 33,0),
(-1, 44,0)

SET IDENTITY_INSERT #Test ON

INSERT INTO #Test 
(Id, Value)
SELECT
    CASE(t.SetId)
        WHEN 1 THEN t.Id --just insert new Id
        WHEN 0 THEN 
            IDENT_CURRENT('#Test') --get last identity value
            + ROW_NUMBER() OVER(ORDER BY t.Id) --simulate identity +1
            - (SELECT COUNT(*) FROM @Temp AS t2 WHERE t2.SetId = 1) --skip inserted rows with Id
            + 1
    END,
    t.[Value]
FROM @Temp AS t

SET IDENTITY_INSERT #Test OFF

SELECT *
FROM #Test AS t

DROP TABLE #Test

How to insert 100 rows to a single table with only identity column?

You can try the following query

CREATE TABLE #TEST1 (TEST_ID INT IDENTITY(1,1))

SET IDENTITY_INSERT #TEST1 ON;

DECLARE @numRows int,@i int
SET @numRows = 100
SET @i=1

WHILE @i<@numRows
BEGIN
    INSERT #TEST1(TEST_ID) SELECT @i
    SET @i=@i+1
END
SET IDENTITY_INSERT #TEST1 OFF;

SELECT * FROM #TEST1
DROP TABLE #TEST1

Thanks

SQL server-- Inserting multiple rows into a table with identity column

If you are on SQL Server 2005, use UNION ALL

INSERT STU (NAME,CLASS,SECTION) 
  SELECT 'A' AS NAME,'1' AS CLASS, 'A' AS SECTION
  UNION ALL
  SELECT 'B', '2', 'B'
  UNION ALL
  SELECT 'C', '3', 'C'

Versions later than 2005, you could use

INSERT STU (NAME,CLASS,SECTION) 
VALUES ('A','1','A'), 
       ('B','2','B'), ...

How to insert in a table having a single column which is unique?

The same way you would insert in any other table:

insert into [tableName] default values;