Thursday 5 November 2009

Insert records into SQL Server without constraints

Question:
How do I insert records into a sql database without constrains?

I want to insert a record into the database and
  1. I want to set the primary key
  2. Want to disable any contraints
  3. I want no triggers to fire



Just just alter the table with NOCHECK CONSTRAINT.
ALTER TABLE [VokSample] NOCHECK CONSTRAINT ALL
GO
ALTER TABLE [VokSample] DISABLE TRIGGER ALL
GO
SET IDENTITY_INSERT [VokSample] ON 

INSERT INTO [VokSample] 
([SmpId], [SmpOwnerName], [SmpExpression], [SmpLanguage], 
[SmpIsVerified], [CreatedOn], [CreatedBy], [ModifiedOn], [ModifiedBy], [IsDeleted])

VALUES(12000, N'admin', N'test sample.', N'en-GB',  
0, '2009-03-10 17:55:08', N'admin', '2009-03-10 17:55:08', N'admin',  0)

SET IDENTITY_INSERT [VokSample] OFF 

ALTER TABLE [VokSample] CHECK CONSTRAINT ALL
GO
ALTER TABLE [VokSample] ENABLE TRIGGER ALL
GO

No comments: