>>>>>>>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!!!!