Re: Disabling Constraint for Bulk Loading

(pankaj_wolfhunter@xxxxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: Unique index not recognizing null
    ... if they're not subject to the constraints of the Index?" ... consistent with other products - including SQL Server, Oracle, and how MS ... value in one or more of the key fields will not be included in the ... Nulls setting made no difference. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Question on Structuring Product Attributes
    ... constraints, I have found a combination of features that allow for ... materialized-views that bring the data together, ... I have no experience with Oracle, ... feature SQL Server has) that joins data from subtype and supertype ...
    (comp.databases.theory)
  • Re: Use DEFAULT CONSTRAINTs or BOUND DEFAULTs?
    ... > I read SQL Server MVP Louis Davidson's post saying: ... > Constraints are the standard way to do this, and really should be the way ... > Even I read in the Microsoft SQL Server Introduction (SQL 7 book ... > DefaultZero ...
    (comp.databases.ms-sqlserver)
  • Re: Add columns to table, but in an order
    ... in the order that SQL Server mentions them, with no way for the end user ... existing constraints and recreate them when the new table is in place. ... be very critical of the script ... Make sure to test the conversion on a test server first. ...
    (microsoft.public.sqlserver.server)
  • Re: simple questions on field/table names and query
    ... > I am trying to adapt my knowledge of SQL server to Sybase. ... I believe ASE works very similarly to SQL Server, while ASA ...
    (comp.databases.sybase)