>Let's take this as example, my application has tables to store Country, State, City, Province. And each table has a fk to the other table.
>
>Example
>
>1. CountryPK will be a FK to State.
>2. State PK will be a FK to City.
>
>Assumed those data will be added from time to time. Thus, I have a default script to store all the latest data.
>
>Default Data Script
>

>-- * Step 1 - Create a table variable with identical structure of the actual table --
>DECLARE @Country TABLE (pk INT IDENTITY(1,1) PRIMARY KEY, newPK INT, CountryCode VARCHAR(60), 
>	CountryName VARCHAR(60), isNew BIT DEFAULT(0))
>	
>DECLARE @recIndex INT
>DECLARE @recCount INT
>DECLARE @newRecPK INT
>DECLARE @CountryCode CHAR(3)
>
>-- * Step 2 - Create INSERT for each default data --
>INSERT INTO @Country(CountryCode, CountryName) VALUES ('UK', 'United Kingdom')
>INSERT INTO @Country(CountryCode, CountryName) VALUES ('JP', 'Japan')
>INSERT INTO @Country(CountryCode, CountryName) VALUES ('CN', 'China')
>INSERT INTO @Country(CountryCode, CountryName) VALUES ('HK', 'Hong Kong')
>
>-- * Step 3 - Compare Data --
>SET @recIndex = 1
>SET @recCount = (SELECT COUNT(*) FROM @Country)
>SET @newRecPK = ISNULL((SELECT MAX(CountryPK) + 1 FROM Country),1)
>
>WHILE @recIndex <= @recCount
>	BEGIN 
>		SET @CountryCode = (SELECT TOP 1 CountryCode FROM @Country WHERE pk = @recIndex)		
>
>		IF NOT EXISTS (SELECT * FROM Country WHERE CountryCode = @CountryCode)
>			BEGIN
>				UPDATE @Country SET newPK = @newRecPK, isNew = 1 WHERE pk = @recIndex
>				SET @newRecPK = @newRecPK + 1	
>			END
>		
>		SET @recIndex = @recIndex + 1
>	END
>
>-- * Step 4 - Checking for State, City, Province ... --
>-- ...
>
>-- * Step 5 - Insert New Country, State, City, Province ... --
>INSERT INTO Country(CountryPK, CountryCode, CountryName)
>	SELECT newPK, CountryCode, CountryName FROM @Country WHERE isNew = 1
>
>SELECT CAST(@@ROWCOUNT AS VARCHAR) + ' records added...'
>

>
>Actual Table in Database
>

>CREATE TABLE Country
>	(CountryPK INT PRIMARY KEY, CountryCode CHAR(3), CountryName VARCHAR(60))
>

>
>The main reason I choose to use table variable as I need a place to store the newly added PK as they will be used in the further steps of update.
>
>Example
>China is added and has a PK 3, all the states in China will have a FK 3 when inserted.
>
>Perhaps my thought is too complicated, using a UPDATE/SELECT would be more efficient?

Surely it's too complicated. You can simply declare a table and then use OUTPUT clause on INSERT command to get all new IDs into it. And then you can use this table to perform insertion into another table with new IDs.

See samples here
http://forums.asp.net/p/1438956/3260325.aspx#3260325 - at the end of this thread.