|
|
Posted by Erland Sommarskog on 12/08/05 00:19
(pankaj_wolfhunter@yahoo.co.in) writes:
> Can anyone show me the right path? I am working on ASE 12.5
ASE? That is Sybase. Are you using Sybase SQL Server or Microsoft
SQL Server? I don't think Sybase has EXEC, so I assume that you are
using MS SQL Server.
> I want to bulk load data into user defined SQL Server
> tables. For this i want to disable all the constraints on all the user
> defined tables.
> I got solution in one of the thread and did the following:
>
> declare @tablename varchar(30)
> declare c1 cursor for select name from sysobjects where type = 'U'
> open c1
> fetch next from c1 into @tablename
> while ( @@fetch_status <> -1 )
> begin
> exec ( 'alter table ' + @tablename + ' check constraint all ')
> fetch next from c1 into @tablename
> end
> deallocate c1
> go
>
> Now when i try to truncate one of the tables (say titles) it gives me
> the following error:
>
> Cannot truncate table 'titles' because it is being referenced by a
> FOREIGN KEY constraint.
First, to disable constraints the command is NOCHECK CONSTRAINT ALL.
But you still cannot run TRUNCATE TABLE. You will have to drop the
constraints entirely, and then reapply them. Or you will have use the
DELETE command to delete the data.
When you renable constraints, be careful to say:
ALTER TABLE tbl WITH CHECK CHECK CONSTRAINT ALL
This forces SQL Server to recheck the constraints. If the constraints are
reapplied without this check, the optimizer will consider as "not trusted"
and will ignore the constraints in execution plans. In some cases, this
can have serious impact on the performance, for instance with partitioned
views.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|