Solved

How to Truncate table if the table refered by the view

Posted on 2009-07-01
4
900 Views
Last Modified: 2012-05-07
Hi,
I got a error on my stored procedure " cannot tuncate a table if the table is refered by the view" how do I over come this?

Thanks,
Manjula
0
Comment
Question by:ken hanse
4 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 100 total points
ID: 24758796
you have to use  the 'DELETE ' statement instead of truncate or else drop the view and recreate it after the TRUNCATE statement
0
 
LVL 17

Accepted Solution

by:
pssandhu earned 200 total points
ID: 24758797
I think you will have to drop the view before you can tuncate. Here is what I suggest -
1. Generate the CREATE VIEW script before you can drop the view. You can get the original script for all the views crated on a table from the information schema:
Select View_Definition
from INFORMATION_SCHEMA.VIEWS
where Table_Name = <TableName>

2. Then drop the view, truncate the table and run the scripts you got by running the above query.
Hope this helps.
P.
0
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 100 total points
ID: 24759714
" cannot tuncate a table if the table is refered by the view"?
are you sure it is not "table referenced by a FOREIGN KEY constraint"?
or
you try to delete table instead of truncate?

can you please post the part of the code where from you got error and copy\paste error too?
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 100 total points
ID: 24760771
Hope you use an indexed view instead of a normal view.

You cannot use TRUNCATE TABLE on tables that:

    * Are referenced by a FOREIGN KEY constraint.
    * Participate in an indexed view.
    * Are published by using transactional replication or merge replication.

Hence you have to either DELETE or drop and recreate the Indexed view in order to use your TRUNCATE statement.

Hope this helps.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Abstract Express Replacement Software 12 28
SQL Replication question 9 43
SQL - Copy data from one database to another 6 20
Deal with apostrophe in stored procedures 8 43
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question