How to Insert into a Table with Just One Identity Column

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

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

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())

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

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

In PostgreSQL how do you insert into a table with only one identity column?

If you create the table like above,

You can use default in following way to insert:

INSERT INTO Participant values(default); 

Check out SQLFIDDLE.

Another way to insert is:

INSERT INTO Participant values(NEXTVAL('Participant_id_seq')); 

CREATE TABLE will create implicit sequence "Participant_id_seq" for serial column "Participant.id".

You can get the sequence for the table using pg_get_serial_sequence function in following way:

pg_get_serial_sequence('Participant', 'id')

It will take new value from sequence using NEXTVAL().

Check out SQLFIDDLE

How to insert into table with specific primary key identity (1,1)

set Identity_Insert yourtable on

Then do the insert

insert yourtable (id, field) values(100,'hello')

Then turn it off again

set Identity_Insert yourtable off

Insert row into table with only one column, primary key and identity

Try:

INSERT INTO dbo.TableOnlyPK DEFAULT VALUES

You have created below table:

CREATE TABLE dbo.TableOnlyPK
(
    Id tinyint PRIMARY KEY IDENTITY (1, 1)
)

Each time you fire : INSERT INTO dbo.TableOnlyPK DEFAULT VALUES, you will insert one row in IDENTITY column.

So if you execute:

INSERT INTO dbo.TableOnlyPK DEFAULT VALUES
INSERT INTO dbo.TableOnlyPK DEFAULT VALUES
INSERT INTO dbo.TableOnlyPK DEFAULT VALUES

It will result:

---

Related Topics