Wow. Many thanks.

>>>>>>>>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.
>>
>>Many thanks for your support :)
>
>

>USE YourDataBase
>
>
>-- Get ALL contraints from the datatbase
>-- Keep in mind that this NOT deal with triggers
>DECLARE @Test TABLE (Id int IDENTITY(1,1),
>                     Constraint_Name nvarchar(max),
>                     FK_table        nvarchar(200),
>                     FK_Column       nvarchar(200),
>                     PK_Table        nvarchar(200),
>                     PK_Column       nvarchar(200),
>                     DELETE_RULE     nvarchar(200),
>                     UPDATE_RULE     nvarchar(200),
>                     IsNotForReplication bit)
>
>
>INSERT INTO @Test (Constraint_Name,
>                   FK_table       ,
>                   FK_Column      ,
>                   PK_Table       ,
>                   PK_Column      ,
>                   DELETE_RULE    ,
>                   UPDATE_RULE    ,
>                   IsNotForReplication)
>SELECT fk.name                           AS Constraint_Name,
>    OBJECT_NAME(fk.parent_object_id)     AS FK_table,
>    c1.name                              AS FK_Column,
>    OBJECT_NAME(fk.referenced_object_id) AS PK_Table,
>    c2.name                              AS PK_Column,
>       CASE 
>           WHEN  fk.delete_referential_action_desc = 'NO_ACTION'
>                 THEN ''
>           ELSE fk.delete_referential_action_desc END AS DELETE_RULE ,
>       CASE 
>           WHEN  fk.update_referential_action_desc = 'NO_ACTION'
>                 THEN ''
>           ELSE fk.update_referential_action_desc END AS UPDATE_RULE,
>           Is_Not_For_Replication
>FROM  sys.foreign_keys fk
>INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
>INNER JOIN sys.columns c1 ON fkc.parent_column_id = c1.column_id AND fkc.parent_object_id = c1.object_id
>INNER JOIN sys.columns c2 ON fkc.referenced_column_id = c2.column_id AND fkc.referenced_object_id = c2.object_id
>WHERE is_ms_shipped = 0
>ORDER BY fk.name
>
>DECLARE @sql nvarchar(max)
>SET @sql = ''
>
>
>SELECT @sql = @sql + 'ALTER TABLE '+ FK_Table+' DROP CONSTRAINT '+Constraint_Name+CHAR(13)+CHAR(10)
>FROM @Test
>
>--- EXEC sp_executesql @sql -- Uncomment this to remove all contraints
>print @sql
>
>--- Do whatever you wan to do with your tables
>
>-- Let's get constraints back
>DECLARE @lcFk  nvarchar(max),
>        @lcFKFields  nvarchar(max),
>        @lcPKFields  nvarchar(max),
>        @lcPKTable  nvarchar(max),
>        @lcFKTable  nvarchar(max),
>        @lcUpdateRule  nvarchar(max),
>        @lcDeleteRule  nvarchar(max),
>        @IsNotForReplication bit,
>        @Id int
>
>SELECT @Id = MIN(Id) FROM @Test 
>print @Id
>
>SET @sql = ''
>WHILE @Id IS NOT NULL
>    BEGIN 
>
>        SELECT @lcFk  = Constraint_Name,
>               @lcPKTable = PK_Table,
>               @lcFKTable = FK_Table,
>               @lcUpdateRule = UPDATE_RULE,
>               @lcDeleteRule = DELETE_RULE,
>               @IsNotForReplication = IsNotForReplication
>        FROM @Test
>        WHERE Id =   @Id
>
>        SET  @lcFKFields = null 
>        SET  @lcPKFields = null
>
>        SELECT @lcFKFields = ISNULL(@lcFKFields+',','') + FK_Column,
>               @lcPKFields = ISNULL(@lcPKFields+',','') + PK_Column
>        FROM @Test
>        WHERE Constraint_Name = @lcFk
>
>        SET @sql = @sql + 'ALTER TABLE '+@lcFKTable+' WITH NOCHECK ADD CONSTRAINT '+ @lcFk+ ' FOREIGN KEY ('+ @lcFKFields+
>                              ') REFERENCES '+ @lcPKTable + '(' + @lcPKFields +')'
>         IF @lcUpdateRule <> ''
>            SET @sql = @sql + ' ON UPDATE '+@lcUpdateRule
>        
>         IF @lcDeleteRule <> ''
>            SET @sql = @sql + ' ON DELETE '+@lcDeleteRule
>
>         IF @IsNotForReplication = 1
>            SET @sql = @sql +  ' NOT FOR REPLICATION'+CHAR(13)+CHAR(10)
>
>        SET @Id = (SELECT MIN(Id) FROM @Test WHERE Id > @id AND Constraint_Name <> @lcFk)
>    END
>
>--- EXEC sp_executesql @sql -- Uncomment this to bring back all contraints
>SELECT @sql
>

>TEST this script very well before put it in production!!!!