How to Return a New Identity Column Value from an SQLserver Select Statement

How do I return a new IDENTITY column value from an SQLServer SELECT statement?

In general, it can't be done in a single statement.

But the SELECT SCOPE_IDENTITY() can (and should) be placed directly after the INSERT statement, so it's all done in the same database call.

Example:

mydb.ExecuteSql("INSERT INTO table(foreign_key1, value) VALUES(9, 'text'); SELECT SCOPE_IDENTITY();");

You can use OUTPUT, but it has some limitations you should be aware of:

http://msdn.microsoft.com/en-us/library/ms177564.aspx

Sql Server return the value of identity column after insert statement

Insert into TBL (Name, UserName, Password) Output Inserted.IdentityColumnName
 Values ('example', 'example', 'example')

SQL Server - Return value after INSERT

No need for a separate SELECT...

INSERT INTO table (name)
OUTPUT Inserted.ID
VALUES('bob');

This works for non-IDENTITY columns (such as GUIDs) too

How to get the next identity value from SQL Server

I think you'll want to look for an alternative way to calculate the next available value (such as setting the column to auto-increment).

From the IDENT_CURRENT documentation, regarding empty tables:

When the IDENT_CURRENT value is NULL (because the table has never contained rows or has been truncated), the IDENT_CURRENT function returns the seed value.

It doesn't even seem all that reliable, especially if you end up designing an app that has more than one person writing to the table at the same time.

Be cautious about using IDENT_CURRENT to predict the next generated identity value. The actual generated value may be different from IDENT_CURRENT plus IDENT_INCR because of insertions performed by other sessions.

How can return identity column value from table and insert other table in SQL Server?

Try this:

-- declare a variable to hold your newly created IDENTITY value
DECLARE @Identity BIGINT

-- insert your values into the first table
INSERT INTO dbo.Table1(list-of-columns)
VALUES(list-of-values);

-- get the newly generated IDENTITY value into your variable
SET @Identity = SCOPE_IDENTITY();

-- use that variable in your second INSERT
INSERT INTO dbo.Table2(table1Id)
VALUES(@Identity)

Update:

With your updated question with the INSERT statement, use this code to include the @Identity value:

INSERT INTO dbo.Interconnect_Traffic_Analysis_MAIN(Code_Op, Name_Op, Shomare_Tel, Duration, table1ID) 
   SELECT 
       t7, t8, t9, t10, @Identity 
   FROM
       Interconnect_Traffic_Analysis_Temp;

How to get the identity of an inserted row?

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.
SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.
IDENT_CURRENT('tableName') returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). Also, as @Guy Starbuck mentioned, "You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into."
The OUTPUT clause of the INSERT statement will let you access every row that was inserted via that statement. Since it's scoped to the specific statement, it's more straightforward than the other functions above. However, it's a little more verbose (you'll need to insert into a table variable/temp table and then query that) and it gives results even in an error scenario where the statement is rolled back. That said, if your query uses a parallel execution plan, this is the only guaranteed method for getting the identity (short of turning off parallelism). However, it is executed before triggers and cannot be used to return trigger-generated values.

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

Get IDENTITY value in the same T-SQL statement it is created in?

You cannot really do this - because the actual value that will be used for the IDENTITY column really only is fixed and set when the INSERT has completed.

You could however use e.g. a trigger

CREATE TRIGGER trg_YourTableInsertID ON dbo.YourTable 
AFTER INSERT
AS 
UPDATE dbo.YourTable
SET dbo.YourTable.OtherID = i.ID
FROM dbo.YourTable t2
INNER JOIN INSERTED i ON i.ID = t2.ID

This would fire right after any rows have been inserted, and would set the OtherID column to the values of the IDENTITY columns for the inserted rows. But it's strictly speaking not within the same statement - it's just after your original statement.