Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Dynamic sql insert identity

Posted on 2004-09-09
7
2,089 Views
Last Modified: 2012-05-05
I have a strored procedure that creates tables adding a prefix to the table name and then inserts data into the table.
I am doing this using dynamic sql. The problem is that when I try to turn on the insert identity I get an error.

SELECT @sql = 'SET IDENTITY_INSERT ['+ @prefix + '_Advancements] ON'
EXEC @sql

All the other code works great. If I do Select @sql to view the output it shows:
SET IDENTITY_INSERT Test_Advancements ON
and if I run this code it works fine. So what would stop this from executing correctly

Here is a bigger portion of the sp. Once I get this to work then I will add the data.

CREATE PROCEDURE Create_New_Tables
@prefix nvarchar(15)

 AS

DECLARE @sql nvarchar(4000)

/*CREATE ADVANCEMENTS TABLE*/

-- Table structure for table '_Advancements'
SELECT @sql = 'IF EXISTS (SELECT * FROM sysobjects WHERE (name = "' + @prefix + '_Advancements")) DROP TABLE ['+ @prefix + '_Advancements]'
EXEC (@sql)

SELECT @sql = 'CREATE TABLE ['+ @prefix + '_Advancements] ('+
'      [Advancement_ID] [int] IDENTITY (1, 1) NOT NULL ,'+
'      [Advancement_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,'+
'      [Advancement_Description] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,'+
'      [Advancement_Time] [decimal](18, 0) NOT NULL ,'+
'      [Advancement_Available] [datetime] NULL ,'+
'      [Advancement_Cost] [decimal](18, 0) NULL ,'+
'      [Advancement_Req1] [int] NULL ,'+
'      [Advancement_Req2] [int] NULL ,'+
'      [Advancement_Req3] [int] NULL ,'+
'      [Advancement_Timestamp] [datetime] NOT NULL CONSTRAINT [DF_'+ @prefix + '_Advancements_Advancement_Timestamp] DEFAULT (getdate()),'+
'      CONSTRAINT [PK_'+ @prefix + '_Advancements] PRIMARY KEY  CLUSTERED '+
'      ('+
'            [Advancement_ID]'+
'      )  ON [PRIMARY] '+
') ON [PRIMARY]'
EXEC (@sql)

-- Dumping data for table _Advancements'
--
-- Enable identity insert
SELECT @sql = 'SET IDENTITY_INSERT ['+ @prefix + '_Advancements] ON'
EXEC @sql
-- Disable identity insert
SELECT @sql = 'SET IDENTITY_INSERT ['+ @prefix + '_Advancements] OFF'
EXEC @sql
GO

The Errors that I get are:
Server: Msg 203, Level 16, State 2, Procedure Create_New_Tables, Line 36
The name 'SET IDENTITY_INSERT [Test_Advancements] ON' is not a valid identifier.
0
Comment
Question by:sesurb
  • 3
  • 3
7 Comments
 
LVL 17

Expert Comment

by:BillAn1
ID: 12021611
you need to enclose the @SQL in brackets, is all - i.e.
EXEC (@sql)
0
 
LVL 2

Author Comment

by:sesurb
ID: 12021853
I definately think that may have been a problem but after doing that I get an error when I try to insert something into the identity column right after this:

Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Test_Country' when IDENTITY_INSERT is set to OFF.

Here is the code to go with this:

SELECT @sql= 'SET IDENTITY_INSERT ['+ @prefix + '_Country] ON'
EXEC (@sql)


SELECT @SQL = 'INSERT INTO ['+ @prefix + '_Country] ([Country_ID], [Country_Name], [Country_Capital_Option], [Country_Team], [Country_Team_Capital], [Country_Water_Distance], [Country_Land_Condition], [Country_Morale], [Country_Food], [Country_Money], [Country_Fuel], [Country_Oil], [Country_Steel], [Country_Population], [Country_X], [Country_Y], [Country_Description])' +
'VALUES(2, "Afghanistan", 0, 0, 0, 0, 100, 50, 0, 700, 0, 0, 0, 3837.0, 640, 144, NULL)'
EXEC (@SQL)
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12022095
THe scope of the SET IDENTITY is limited to the EXEC. once you exit from the EXEC it is no longer active. You will gave to do the setiing of the identity insert in the same piece of SQL as the insert itself :

SELECT @SQL = 'SET IDENTITY_INSERT ['+ @prefix + '_Country] ON
INSERT INTO ['+ @prefix + '_Country] ([Country_ID], [Country_Name], [Country_Capital_Option], [Country_Team], [Country_Team_Capital], [Country_Water_Distance], [Country_Land_Condition], [Country_Morale], [Country_Food], [Country_Money], [Country_Fuel], [Country_Oil], [Country_Steel], [Country_Population], [Country_X], [Country_Y], [Country_Description])' +
'VALUES(2, "Afghanistan", 0, 0, 0, 0, 100, 50, 0, 700, 0, 0, 0, 3837.0, 640, 144, NULL)'
EXEC (@SQL)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Author Comment

by:sesurb
ID: 12023531
That is odd... because if I run the code in query analyzer and set the insert to ON it will stay on until I turn it off, why is this different?
I will try it in the morning and test it out.
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 80 total points
ID: 12024450
Within the one QA session, the insert will stay ON, but if you open up another QA window,  it will be OFF, even though it is currently ON in the first session. Similarly, when you do EXEC it is a new scope.
0
 
LVL 18

Assisted Solution

by:ShogunWade
ShogunWade earned 20 total points
ID: 12025966
yep combine the sql statements:


SELECT @sql= 'SET IDENTITY_INSERT ['+ @prefix + '_Country] ON  ' +   -- plus is for clarity only
 'INSERT INTO ['+ @prefix + '_Country] ([Country_ID], [Country_Name], [Country_Capital_Option], [Country_Team], [Country_Team_Capital], [Country_Water_Distance], [Country_Land_Condition], [Country_Morale], [Country_Food], [Country_Money], [Country_Fuel], [Country_Oil], [Country_Steel], [Country_Population], [Country_X], [Country_Y], [Country_Description])' +
'VALUES(2, "Afghanistan", 0, 0, 0, 0, 100, 50, 0, 700, 0, 0, 0, 3837.0, 640, 144, NULL)'
EXEC (@SQL)
0
 
LVL 2

Author Comment

by:sesurb
ID: 12039113
Thanks BillAn1 for all the support for this. I have it working now. I also am giving ShogunWade a few points for helping out.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question