<p>I am currently working in C#, and I need to insert a new record into one table, get the new primary key value, and then use that as a foreign key reference in inserting several more records. The Database is MS SQL Server 2003. All help is appreciated!</p>

The best way of doing this is the use SCOPE_IDENTITY() function in TSQL.  This should be executed as part of the insert i.e.
```
```
SqlCommand cmd = new SqlCommand(@"
    INSERT INTO T (Name) VALUES(@Name)
    SELECT SCOPE_IDENTITY() As TheId", conn);
cmd.AddParameter("@Name", SqlDbType.VarChar, 50).Value = "Test";
int tId = (int)cmd.ExecuteScalar();
```
```
Alternatively you can assign SCOPE_IDENTITY() to a variable to be used in successive statements. e.g.
```
```
DECLARE @T1 int

INSERT INTO T (Name) VALUES('Test')

SELECT @T1 = SCOPE_IDENTITY() 

INSERT INTO T2 (Name, TId) VALUES('Test', @T1)
```
```