How to create a Generic Script to remove Identity Insert OFF of column for all tables of a DB in Sql server?

Question

0.00/5 (No votes)

See more:

How to create a Generic Script to remove Identity Insert OFF of column for all tables of a DB in Sql server?

I am doing Archival Automation Process. I am migrating data from Original DB to Archival DB . I want to remove the Identity Insert of the columns in Archival DB and move the data from Original DB to Archival DB.

I have a single code as below:

SQL

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT

BEGIN TRANSACTION
GO

CREATE TABLE dbo.Tmp_example1
(
    eid INT NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Tmp_example1 ON
GO

IF EXISTS(SELECT * FROM dbo.example1)
EXEC('INSERT INTO dbo.Tmp_example1 (eid)
SELECT eid FROM dbo.example1 WITH (HOLDLOCK TABLOCKX)')
GO

SET IDENTITY_INSERT dbo.Tmp_example1 OFF
GO

DROP TABLE dbo.example1
GO

EXECUTE sp_rename N'dbo.Tmp_example1', N'example1', 'OBJECT'
GO

COMMIT

Posted 17-Mar-15 19:48pm

Kandiya

Updated 17-Mar-15 22:01pm

Tomas Takac

v3

Add a Solution

Comments

ZurdoDev 18-Mar-15 8:24am

Where exactly are you stuck?

W∴ Balboos, GHB 18-Mar-15 11:52am

If the target file is an archive - exactly why do you need to preserve the identity property of field? It's generated in the source and should thus maintain its uniqueness the the target.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kuthuparakkal · Answer 1 · 2015-03-20T02:40:00

Try this:

SQL

DECLARE @Rows INT = 0
DECLARE @SQL NVARCHAR(MAX)
DECLARE @MySQL AS TABLE
(
    ID int IDENTITY (1, 1) Primary key NOT NULL 
  , SQLTEXT NVARCHAR(MAX)
)

SET NOCOUNT ON
INSERT @MySQL
SELECT DISTINCT 'SET IDENTITY_INSERT ' + ST.TABLE_SCHEMA +'.'+ ST.TABLE_NAME + ' OFF'
FROM INFORMATION_SCHEMA.TABLES AS ST
INNER JOIN  INFORMATION_SCHEMA.COLUMNS AS SC
ON (ST.TABLE_SCHEMA +'.'+ ST.TABLE_NAME) = (SC.TABLE_SCHEMA +'.'+ SC.TABLE_NAME)
WHERE ST.TABLE_TYPE = 'BASE TABLE'
      AND
	  COLUMNPROPERTY(OBJECT_ID(ST.TABLE_SCHEMA +'.'+ ST.TABLE_NAME)
                      ,SC.COLUMN_NAME,'IsIdentity') = 1

ORDER BY 1

SELECT @Rows=COUNT(1) FROM @MySQL

WHILE(@Rows > 0)
BEGIN
	SELECT @SQL = SQLTEXT FROM @MySQL WHERE ID = @Rows

	EXECUTE sp_executesql @SQL

	SET @Rows = @Rows -1

END

Thanks,

Kuthuparakkal