#identity_insert

SQL : Inserting metadata or values in master table

Usually when developing any SQL based system, we need some master tables which contains values and Ids which are consistent and same as enums in our code. But when you set an identity column in the table it would not let you insert the Id of your choice. So there is a flag which you can turn on and then insert the rows, and then turn the flag back off.

(more…)

В Microsoft SQL Server есть возможность вставлять явные значения в автоинкрементное поле (IDENTITY), используя свойство таблицы IDENTITY_INSERT. В этом материале мы рассмотрим пример использования данного свойства

Introduction to the INSERT Statement

The INSERT statement is used to add rows to a SQL Server data table.  In this article we’ll explore how to use the INSERT statement.  We discuss some best practices, limitations, and wrap-up with several examples.

This is the second article in a series of articles.  The first is entitled Introduction to SQL Server Data Modification Statements.

All the examples for this lesson are based on…

New Post has been published on http://www.varindersandhu.in/2012/06/07/sql-server-import-export-wizard-enable-identity-insert/

SQL Server – Import Export Wizard – Enable Identity Insert

I had requirement to move the data from source table with his identity column to destination table that have also identity column.

<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->

Identity columns are commonly used as primary keys in database tables. These columns automatically assign a value for each new row inserted. Identity column does not allow any kind of insertion from the user.

Before starting I was thinking I need to enable the IDENTITY_INSERT on the destination table by using

SET IDENTITY_INSERT myTable ON   and once I will move the data then will OFF the IDENTITY_INSERT by using SET IDENTITY_INSERT myTable OFF.

When I checked the SQL Server – Import Export Wizard then I checked that there is one option “Enable Identity Insert” that made my task easy.

SQL Server – Import Export Wizard – Enable Identity Insert

Post has been published on http://www.varindersandhu.in/2011/02/01/add-the-value-into-an-identity-column-in-sql-server/

Add the value into an Identity Column in SQL Server

Identity columns are commonly used as primary keys in database tables. These columns automatically assign a value for each new row inserted. But sometime Identity columns  missed the value, we want to insert missed value into the column. For that we need to enable IDENTITY_INSERT for the table.

USE mytempDB GO

CREATE TABLE myTable ( myIdentity INT NOT NULL IDENTITY(1,1) PRIMARY KEY, myValue NVARCHAR(30) NOT NULL ) GO

<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->

INSERT myTable(myIdentity, myValue) VALUES (5, ‘Varinder Sandhu’)

–Result =  Error because IDENTITY is OFF

–Below enable IDENTITY_INSERT

SET IDENTITY_INSERT myTable ON

INSERT myTable(myIdentity, myValue) VALUES (5, ‘Varinder Sandhu’)

SET IDENTITY_INSERT myTable OFF

Note:

Once we enabled IDENTITY_INSERT on a table you must specify a value for the identity column.

–If IDENTITY_INSERT is OFF then use  below insert statement for above example

SQL IDENTITY_INSERT kodu, tablodaki identity olarak belirlenmiş sütunlara veri eklememize yarayan güzide bir MSSQL kodudur. MSSQL'i belirtmemin sebebi ise MYSQL tarafında böyle bir ayara gerek olmadan identity bi alana veri eklemenize izin veriyor. O yüzden mysql ile uğraşanlar sayfayı isterlerse değiştirebilir yada adam uğraşmış etmiş diyip yazıyı paylaşabilirsiniz :D IDENDITY kolonu kısaca anlatmam gerekirse; ağırlıklı olarak veri id'lerinde kullanılan otomatik olarak sıralı veya karışık numeric değerler verilen kolondur. Aşağıdaki MEYVELER tablosundaki örnekte ID bölümü, identity bir kolondur. ID MeyveAdı --------------------------------- 1 Elma 2 Armut 3 Portakal Böyle alanlara MSSQL'de direk insert yapılamadığından INDENTITY_INSERT kullanılır. Normal insert yaparken kodun öncesinde; SET IDENTITY_INSERT "TABLO ISMI BURAYA GELECEK" ON sonrasında insert'ler yazılır. En son ise; SET IDENTITY_INSERT "TABLO ISMI BURAYA GELECEK" OFF eklenerek çalıştırıldığında problem çıkartmadan identity bir alana veri eklemiş olursunuz. //////// Üstteki tabloya göre örnek ///////// SET IDENTITY_INSERT MEYVELER ON INSERT MEYVELER (ID,MeyveAdı) VALUES (4,'Karpuz') INSERT MEYVELER (ID,MeyveAdı) VALUES (5,'Kayısı') SET IDENTITY_INSERT MEYVELER OFF