T-SQL INSERT INTO disabling Constraints check

Question

i would like to temporarly(just for the t-sql statement) disable the constraints check. My statement is:

insert into branchOffice(
branchOfficeTypeId, 
labirintoClientiId, 
companyId,
signboardName,
address,
addressNumber,
zipCode, 
city, 
province, 
officePhoneNumber, 
officeFaxNumber, 
officeEmail,
statusId,
officeNotes,
squareMeters,
familyHelpersCount,
employeesCount,
workingCompanyPartnerCount)

SELECT
    1, 
    [NewBiz.Labirinto].dbo.Clienti.id, 
    1,
    [NewBiz.Labirinto].dbo.clienti.Insegna,
        case 
        when PATINDEX('%[0-9]%',[NewBiz.Labirinto].dbo.clienti.SedeLegaleIndirizzo)>0 then LEFT([NewBiz.Labirinto].dbo.clienti.SedeLegaleIndirizzo, PATINDEX('%[0-9]%',[NewBiz.Labirinto].dbo.clienti.SedeLegaleIndirizzo)-1)
        when PATINDEX('%[0-9]%',[NewBiz.Labirinto].dbo.clienti.SedeLegaleIndirizzo)=0 then [NewBiz.Labirinto].dbo.clienti.SedeLegaleIndirizzo
        end as indirizzo, 
        case
        when PATINDEX('%[0-9]%',[NewBiz.Labirinto].dbo.clienti.SedeLegaleIndirizzo)>0 then right([NewBiz.Labirinto].dbo.clienti.SedeLegaleIndirizzo,len([NewBiz.Labirinto].dbo.clienti.SedeLegaleIndirizzo)-PATINDEX('%[0-9]%',[NewBiz.Labirinto].dbo.clienti.SedeLegaleIndirizzo)+1) 
        when PATINDEX('%[0-9]%',[NewBiz.Labirinto].dbo.clienti.SedeLegaleIndirizzo)=0 then '' 
        end as numero,
    [NewBiz.Labirinto].dbo.clienti.SedeLegaleCAP,
    [NewBiz.Labirinto].dbo.clienti.SedeLegaleComune,
    [NewBiz.Labirinto].dbo.clienti.SedeLegaleProvincia,
    [NewBiz.Labirinto].dbo.clienti.SedeLegaleTelefono,
    [NewBiz.Labirinto].dbo.clienti.SedeLegaleFax,
    [NewBiz.Labirinto].dbo.clienti.SedeLegaleEMail,
    [NewBiz.Labirinto].dbo.clienti.SituazioneId,
    [NewBiz.Labirinto].dbo.clienti.Note,
    [NewBiz.Labirinto].dbo.clienti.Superficie,
    [NewBiz.Labirinto].dbo.clienti.Coadiuvanti,
    [NewBiz.Labirinto].dbo.clienti.Dipendenti,
    [NewBiz.Labirinto].dbo.clienti.SociLavoratori
    from [NewBiz.Labirinto].dbo.Clienti
    where [NewBiz.Labirinto].dbo.Clienti.AziendaId=1

http://stackoverflow.com/questions/737115/turn-off-constraints-temporarily-ms-sql — Mihai, Jul 01 '15 at 10:34
Imagine, just for a second, that we don't have access to *your* database. We don't know *what* constraints are on that table, nor *which* of them you want to disable. Also, you can't disable constraints in SQL Server for just one statement - you have to disable them for all users. — Damien_The_Unbeliever, Jul 01 '15 at 10:44

score 1 · Accepted Answer · answered Jul 01 '15 at 11:27

1

Constraint is on the table not a single statement
Kind of ugly but
Put it in a transaction and take a tablock

begin transaction 
  ALTER TABLE branchOffice NOCHECK CONSTRAINT ALL
  insert into branchOffice with (tablock) 
  -- Re-enable the constraints on a table
  ALTER TABLE branchOffice WITH CHECK CHECK CONSTRAINT ALL
commit transation;

answered Jul 01 '15 at 11:27

paparazzo

44,497
23
105
176

hmm... still giving me constraints problems – Alessio Bacin Jul 01 '15 at 13:44
hmm ... can't help you if you don't get specific problems – paparazzo Jul 01 '15 at 13:48
Msg 547, Level 16, State 0, Line 6 The DELETE statement conflicted with the REFERENCE constraint "FK_branchOffice_businessType_branchOffice". The conflict occurred in database "NuLabs", table "dbo.branchOffice_businessType", column 'branchOfficeId'. The statement has... Checking identity information: current identity value '2'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Msg 2627, Level 14, State 1, Line 10 Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in object 'dbo.branchOffice'. The duplicate key value is (1). – Alessio Bacin Jul 09 '15 at 14:40

T-SQL INSERT INTO disabling Constraints check

1 Answers1