no i just want to delet all the data from database and then want to import from original database. is there othere way i can import the database which overwirtes the exsiting database?
if you do not want to log the transaction and you have admin rights
Truncate Table Tablename
From books online about truncate Table:
"You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger. TRUNCATE TABLE may not be used on tables participating in an indexed view."
Incidentally it's a good idea to make sure you have a fairly current backup before you delete whole tables worth of data. This is a bad time to find out that the database hasn't been backed up in three months. Many's the time someone tells someone to delete something and that person either deletes the wrong table's data by accident (oops) or the person requesting the delete changes his mind right after it was done or forgot about something else that would be affected by the delete and something breaks! The chances of something like this are higher, the longer it's been since your last backup. (See Murphy's Law!)
Oops didn't read what you wanted well enough. Ignore above. In DTS there is an option you can select that tells it whether you want to append or overwrite the data. When you select the source tables and views to import, click the transform button.
If the login is a system administrator or databse owner, use the following.
exec sp_msforeachtable
'If Exists (Select * From sysobjects Where id=object_id(''?'') And type=''u'' And name<>''[dbo].[dtproperties]'') Truncate Table ?'
go
If not SA or DBO, use DELETE in place of TRUNCATE Table.
exec sp_msforeachtable
'If Exists (Select * From sysobjects Where id=object_id(''?'') And type=''u'' And name<>''[dbo].[dtproperties]'') Delete ?'
go
If you want to get the best answer for your question read faq183-874 and faq183-3179. Terry L. Broadbent - DBA
SQL Server Page:
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.