Getting new IDs after insert

Use the OUTPUT functionality to grab all the INSERTED Id back into a table.

CREATE TABLE MyTable(    MyPK INT IDENTITY(1,1) NOT NULL,    MyColumn NVARCHAR(1000))DECLARE @myNewPKTable TABLE (myNewPK INT)INSERT INTO     MyTable(    MyColumn)OUTPUT INSERTED.MyPK INTO @myNewPKTableSELECT    sysobjects.nameFROM    sysobjectsSELECT * FROM @myNewPKTable

And if you want the "control" in ADO.Net and get the ids assigned to childs and getting the ids back so that you can update your model:http://daniel.wertheim.se/2010/10/24/c-batch-identity-inserts/

User this stored Procuedure

this will be a dynamic primary key..

SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE PROCEDURE sp_BulkInsertCountry(@FilePath varchar(1000))ASBEGIN--PROCEDURE--variable declarationdeclare @SQL varchar(500)declare @id intdeclare @CountryName varchar(30)--Create temporary table for CountryCREATE TABLE #tmpCountry(CountryName varchar(30),)---executing bulk insert on temporary tableSET @SQL='BULK INSERT #tmpCountry from ''' + @FilePath + ''' WITH (FIELDTERMINATOR ='','',ROWTERMINATOR=''\n'')'EXEC(@sql)DECLARE cursor_Country CURSOR READ_ONLY FORselect [CountryName] from #tmpCountryOPEN cursor_CountryFETCH NEXT FROM cursor_Country INTO @CountryNameWHILE @@FETCH_STATUS=0BEGINSELECT @id=isnull(max(Countryid),0) from tblCountryMasterSET @id=@id+1INSERT INTO tblCountryMaster values(@Id,@CountryName) FETCH NEXT FROM cursor_Country INTO @CountryNameENDCLOSE cursor_CountryDEALLOCATE cursor_CountryEND--PROCEDUREGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO

For More details visit following linkhttp://jalpesh.blogspot.com/search?q=bulk+insert