If you are using SQL Server 2005 or higher, you can do this in a single statement by using the OUTPUT clause:
Create Table Foo ( Id int not null identity(1,1) , Name varchar(50) null , .... ) Insert Foo(Name) Output inserted.Id, inserted.Name Select 'Foo' Union All Select 'Bar' ....
If you are using SQL Server 2000, then you should use SCOPE_IDENTITY like so:
Insert Foo(Name) Select 'Foo' Select SCOPE_IDENTITY()
Notice that I can only do one Insert at a time using this method because we want to call SCOPE_IDENTITY immediately after the Insert statement.
If you are using a version prior to SQL Server 2000, then you want to use @@IDENTITY
Insert Foo(Name) Select 'Foo' Select @@Identity
The problem is that @@Identity will do funky things if you have triggers on the table.
EDIT You asked how to use this information in C#. You would use it just as if you called a SELECT query:
var connString = ConfigurationManager.ConnectionStrings["MyConnectionStringName"].ConnectionString; DataTable newData; using ( var conn = new SqlConnection( connString ) ) { conn.Open(); const string sql = "Insert Foo(Name) Output inserted.Id, inserted.Name Values(@Name)"; using ( var cmd = new SqlCommand( sql, conn ) ) { cmd.Parameters.AddWithValue("@Name", "bar"); using ( var da = new SqlDataAdapter( cmd ) ) { da.Fill( newData ); } } }
Here I am assuming you have in your configuration file a connectionStrings entry for MyConnectionStringName. In addition, you will need to add a reference to System.Configuration in order to use the ConfigurationMananager class. I did not check this code but it should be pretty close to what you need. In this case, I'm writing the query directly. There are other solutions such as using a DataSource control and setting the SelectCommand.
