last question....yes its true

[sparks]

Ok I have all the data imported.
I have all the code and want to duplicate this in a new copy of the
destination.

To preserve all schema since the destination is full of data.
what is the correct way to delete all the data from these tables and
preserve all keys etc.

I just want to make sure I am doing it correctly and be done with
this.

[Erland Sommarskog]

Post by sparks
Ok I have all the data imported.
I have all the code and want to duplicate this in a new copy of the
destination.
To preserve all schema since the destination is full of data.
what is the correct way to delete all the data from these tables and
preserve all keys etc.
I just want to make sure I am doing it correctly and be done with
this.

A dirt simple way is to run

SELECT 'DELETE ' + quotename(s.name) + '.' + quotename(o.name)
FROM sys.schemas s
JOIN sys.objets o ON s.schema_id = o.object_id
WHERE o.type = 'U'

And then run the result until it does not give any more errors.

If you have triggers that perform actions on delete, you have have to add
ALTER TABLE DISABLE/ENABLE to the script you generate.

An more "professional" alternative is to right-click the database and select
"Generate scripts", but you need to be careful with which options you
select, so that you don't lose anything. For instance, I believe that
triggers are not included by default.

Then again, in the end you need the script anyway so you can put the
code under version control.