Insert Record to SQL Table with Identity Column

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

This should work:

INSERT INTO GroupTable DEFAULT VALUES

INSERT record to SQL table with IDENTITY column

Change the OFF and ON around

SET IDENTITY_INSERT Notes ON

INSERT INTO Notes (NoteID, NoteTitle,NoteDescription)
SELECT NoteID, NoteTitle,NoteDescription from Notes_Temp

SET IDENTITY_INSERT Notes OFF

Inserting into multiple tables with identity column

You could use @@ROWCOUNT here to determine if an update occurred within the last statement.

  INSERT INTO [dbo].[Remittances]
                           ([TransactionID],[RemittanceTransactionId],[InsertedBy],[InsertedDateTime],[SavedBy],[SavedDateTime])
               SELECT 
                            @TransactionID,[RemittanceTransactionId],[InsertedBy],[InsertedDateTime],[SavedBy],[SavedDateTime]
               FROM         @parRemittance;

               SET         @RemittanceID =  @@IDENTITY ;

     IF(@@ROWCOUNT=0) SET @RemittanceID = NULL

Insert from temp table to a table with identity column

If you don't want to keep the id of inserted records, then you need to specify all your columns but the id column in the select. As general good practice, dont select *, always specify the columns you want to retrieve-insert.

INSERT INTO OriginalTable (col1, col2, col3...)
OUTPUT MyIdentityColumn INTO @InsertedRows(NewSeqNum)
SELECT (col1, col2, col3...) FROM #TestTable

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

Handling identity columns in an Insert Into TABLE Values() statement?

By default, if you have an identity column, you do not need to specify it in the VALUES section. If your table is:

ID    NAME    ADDRESS

Then you can do:

INSERT INTO MyTbl VALUES ('Joe', '123 State Street, Boston, MA')

This will auto-generate the ID for you, and you don't have to think about it at all. If you SET IDENTITY_INSERT MyTbl ON, you can assign a value to the ID column.

Insert identity column value into table from another table?

You can use the output clause. From the documentation (emphasis mine):

The OUTPUT clause returns information from, or expressions based on, each row affected
by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be
returned to the processing application for use in such things as
confirmation messages, archiving, and other such application
requirements. The results can also be inserted into a table or table
variable. Additionally, you can capture the results of an OUTPUT
clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and
insert those results into a target table or view.

like so:

create table #tempids (a int) -- a temp table for holding our identity values

insert into #test 
(b,c) 
output inserted.a into #tempids -- put the inserted identity value into #tempids
values 
('bvju','hjab')

You then asked...

What if the insert is from a select instead?

It works the same way...

insert into #test 
(b,c) 
output inserted.a into #tempids -- put the inserted identity value into #tempids
select -- except you use a select here
 Column1
,Column2
from SomeSource

It works the same way whether you insert from values, a derived table, an execute statement, a dml table source, or default values. If you insert 1000 records, you'll get 1000 ids in #tempids.

SQL statement to insert record into table that has identity column?

Usually, omitting the id column would generate a new automatic ID if the column is set so. In your case, you can use

SET IDENTITY_INSERT MyTable ON;

INSERT INTO MyTable (id, col1, col2, col3, col4)
VALUES (4567, 'Value1', 'Value2', 'Value3', 'Value4');

SET IDENTITY_INSERT MyTable OFF;