Stuck trying to migrate two tables from one DB to another DB

I think you need to do this in 2 steps.

You need to import the old tables and keep the old ids (and generate new ones). Then once they're in the new database and they have both new and old ids you can use the old Id's to get associate the new ids, then you drop the old ids.

You can do this by importing into temporary (i.e. they will be thrown away) tables, then inserting into the permanent tables, leaving out the old ids.

Or import directy into the new tables (with schema modified to also hold old ids), then drop the old id's when they're no longer necessary.

EDIT:

OK, I'm a bit clearer on what you're looking for thanks to comments here and on other answers. I knocked this up, I think it'll do what you want.

Basically without cursors it steps through the parent table, row by row, and inserts the new partent row, and all the child rows for that parent row, keeping the new id's in sync. I tried it out and it should work, it doesn't need exclusive access to the tables and should be orders of magniture faster than a cursor.

declare @oldId as int
declare @newId as int

select @oldId = Min(ParentId) from OldTable1 

while not @oldId is null
begin
    Insert Into NewTable1 (Name)
    Select Name from OldTable1 where ParentId = @oldId

    Select @newId = SCOPE_IDENTITY()

    Insert Into NewTable2 (ParentId, Foo)
    Select @newId, Foo From OldTable2 Where ParentId = @oldId

    select @oldId = Min(ParentId) from OldTable1 where ParentId > @oldId

end

Hope this helps,