How to Insert into a Table with Just One Identity Column (Sql Express)

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

This should work:

INSERT INTO GroupTable DEFAULT VALUES 

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

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 specific ID in identity field

You can turn on/off inserting identity values with SET IDENTITY_INSERT On/Off:

To enable your custom values:

SET IDENTITY_INSERT TableName ON

To enable auto-values:

SET IDENTITY_INSERT TableName OFF

Note that you have to list the (non-nullable) columns explicitly if you want to insert identity values, like here:

INSERT INTO TableName (ID, Text, OtherColumns...) Values (99, 'foo', ...)

You can't omit the column-list by using this syntax:

INSERT INTO TableName Values (99,'foo')

Adding auto-incremented values to a table with one column

Try the following:

 INSERT INTO YOUR_TABLE(YOUR_ID) VALUES (NULL);

How do I insert data into a row in SQL?

In SQL Server identity is used for autoincrement. identity(1,1) means the starting value for the column will be 1 and will be incremented by 1. You can change it to desired value for example identity(5,2) starts the value at 5 and increments by 2. You no need to specify an explicit value for setting this column, it will be automatically assigned a unique value.

In mysql you can use AUTO_INCREMENT

Refer w3schools page for details sql autoincrement

Getting the identity value from a table with single identity column in FitNesse

Your problem is not with Fitnesse, but with the SQL engine. If you try the same query in SQL Server Management Studio you will receive the same error. Thus, given the restriction that you cannot use set identity_insert on the question you really need to ask is how to insert a record with no insertable fields in SQL Server independent of Fitnesse? This StackOverflow post provides a simple answer:

INSERT INTO #TempTable DEFAULT VALUES

Having that in hand, now the task is to map this to Fitnesse. Here is one solution:

!|execute|CREATE TABLE #TestTable ( ID int identity(1,1) )|

!|execute|INSERT INTO #TestTable DEFAULT VALUES|

!|query|SELECT TOP 1 @@IDENTITY [ID] FROM #TestTable|
|ID?                                                |
|>>TestID                                           |

Fitnesse's INSERT command does not support the default construct, so you must switch to the more general EXECUTE command to do the insert. That, however, does not return results so you cannot glean the auto-inserted ID value directly. The final query gives you one way to grab the just-inserted identity value.

How to update Identity Column in SQL Server?

You can not update identity column.

SQL Server does not allow to update the identity column unlike what you can do with other columns with an update statement.

Although there are some alternatives to achieve a similar kind of requirement.

• When Identity column value needs to be updated for new records

Use DBCC CHECKIDENT which checks the current identity value for the table and if it's needed, changes the identity value.

DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE)

• When Identity column value needs to be updated for existing records

Use IDENTITY_INSERT which allows explicit values to be inserted into the identity column of a table.

SET IDENTITY_INSERT YourTable {ON|OFF}

Example:

-- Set Identity insert on so that value can be inserted into this column
SET IDENTITY_INSERT YourTable ON
GO
-- Insert the record which you want to update with new value in the identity column
INSERT INTO YourTable(IdentityCol, otherCol) VALUES(13,'myValue')
GO
-- Delete the old row of which you have inserted a copy (above) (make sure about FK's)
DELETE FROM YourTable WHERE ID=3
GO
--Now set the idenetity_insert OFF to back to the previous track
SET IDENTITY_INSERT YourTable OFF

Adding an identity to an existing column

You can't alter the existing columns for identity.

You have 2 options,

1. Create a new table with identity & drop the existing table

2. Create a new column with identity & drop the existing column

Approach 1. (New table) Here you can retain the existing data values on the newly created identity column. Note that you will lose all data if 'if not exists' is not satisfied, so make sure you put the condition on the drop as well!

CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

Approach 2 (New column) You can’t retain the existing data values on the newly created identity column, The identity column will hold the sequence of number.

Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

See the following Microsoft SQL Server Forum post for more details:

How to alter column to identity(1,1)

---

Related Topics