>>>>>I would like to be able to copy a few tables (some in a relationship) from time to time and then restore the state from the copies.
>>>>>To save the tables
insert to _newtable_ from _oldtable_
works nicely. But to recopy into the orignal tables seems to be tricky, due to identity, foreign keys etc.
>>>>>
>>>>>Is there a simple way to do this? (copy with production :) )
>>>>
>>>>You will have to copy tables in order (Parent table first, then related tables) and you would need to SET IDENTITY_INSERT ON for these tables.$
>>>
>>>... and list the columns in the restoring process right?
>>
>>Yes, of course. You should always list all columns in the INSERT command, don't take shortcuts.
>Sure but this is just a utility i need in development. And I wanted to make something generic for multiple tables.
>I was wondering if it would not be better to strip off the keys and the constraints, copy back (via insert into from) and then restore the keys and the relations (in tsql).
This is another approach you can take. It may be easier and quicker.
If it's not broken, fix it until it is.
My Blog