T-SQL INSERT INTO禁用约束检查 [英] T-SQL INSERT INTO disabling Constraints check

问题描述

我想临时(仅针对t-sql语句)禁用约束检查.我的声明是:

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

推荐答案

约束在表中不是单个语句
有点丑陋，但
将其放入交易中并进行制表符

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; 

这篇关于T-SQL INSERT INTO禁用约束检查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！