SET IDENTITY_INSERT <tablename> ON required

Question

I want to insert records using  the following code in ssis variable
if ((SELECT OBJECTPROPERTY( OBJECT_ID(N'[dbo].[TableName]'), 'TableHasIdentity')) = 1)
begin
SET IDENTITY_INSERT  [dbo].[TableName] Off
 insert into [dbo].[TableName] select * from [efi_dot_com].[dbo].[TableName]  
SET IDENTITY_INSERT  [dbo].[TableName] On
end
else
 insert into [dbo].[TableName] select * from [efi_dot_com].[dbo].[TableName]

Then I got error

An explicit value for the identity column in table 'dbo.TableName' can only be specified when a column list is used and IDENTITY_INSERT is ON.

The reason behind the erroir is try to use insert into with select * from ... with a table has identity (primary key) column.It is required to list out all the column name.

I can't list out all column names since the above code use dynamically for various tablename. How can I include the lsit of column name dynamically into the insert sql statement?

Is there other solution?

Thanks

Answer 1

I want to insert records using  the following code in ssis variable
if ((SELECT OBJECTPROPERTY( OBJECT_ID(N'[dbo].[TableName]'), 'TableHasIdentity')) = 1)
begin
SET IDENTITY_INSERT  [dbo].[TableName] Off
 insert into [dbo].[TableName] select * from [efi_dot_com].[dbo].[TableName]  
SET IDENTITY_INSERT  [dbo].[TableName] On
end
else
 insert into [dbo].[TableName] select * from [efi_dot_com].[dbo].[TableName]

Then I got error

An explicit value for the identity column in table 'dbo.TableName' can only be specified when a column list is used and IDENTITY_INSERT is ON.

The reason behind the erroir is try to use insert into with select * from ... with a table has identity (primary key) column.It is required to list out all the column name.

I can't list out all column names since the above code use dynamically for various tablename. How can I include the lsit of column name dynamically into the insert sql statement?

Is there other solution?

Thanks

your use of identity insert is not correct. the corrected code should be:

if ((SELECT OBJECTPROPERTY( OBJECT_ID(N'[dbo].[TableName]'), 'TableHasIdentity')) = 1)
begin
SET IDENTITY_INSERT  [dbo].[TableName] On
 insert into [dbo].[TableName] select * from [efi_dot_com].[dbo].[TableName]  
SET IDENTITY_INSERT  [dbo].[TableName] Off
end
else
 insert into [dbo].[TableName] select * from [efi_dot_com].[dbo].[TableName]

 

---

Please mark the post as answered if it answers your question

Answer 2

I want to off first the identity_insert since the tablename has identity column.

I got the same error.

 An explicit value for the identity column in table 'dbo.TableName' can only be specified when a column list is used and IDENTITY_INSERT is ON.

The issue "specified when a column list is used and IDENTITY_INSERT is ON."

Answer 3

I want to off first the identity_insert since the tablename has identity column.

I got the same error.

 An explicit value for the identity column in table 'dbo.TableName' can only be specified when a column list is used and IDENTITY_INSERT is ON.

The issue "specified when a column list is used and IDENTITY_INSERT is ON."

To insert a value in the identity field you have to set "identify_insert" to ON. (Follow the documentation below).

http://msdn.microsoft.com/en-us/library/aa259221(v=sql.80).aspx

You can get the list of your required columns using an Execute SQL task with the query below:

DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + name FROM sys.columns WHERE object_id = OBJECT_ID('dbo.TableName')
PRINT @Names

---

Please mark the post as answered if it answers your question

Answer 4

I tried to generate list of columns using the following code

"DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + name FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].["+@[User::TableName]+"]')
if ((SELECT OBJECTPROPERTY( OBJECT_ID(N'[dbo].["+@[User::TableName]+"]'), 'TableHasIdentity')) = 1)
begin
SET IDENTITY_INSERT  [dbo].["+@[User::TableName]+"] Off
 insert into [dbo].["+@[User::TableName]+"]("+PRINT @Names +") select * from [efi_dot_com].[dbo].["+@[User::TableName]+"]  
SET IDENTITY_INSERT  [dbo].["+@[User::TableName]+"] On
end
else
 insert into [dbo].["+@[User::TableName]+"] select * from [efi_dot_com].[dbo].["+@[User::TableName]+"] 
"

Then I could not parse in Expression evaluation of SSIS. The error is

Attempt to parse the expression ""DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + name FROM sys.columns WHERE object_id = OBJECT_ID('[dbo].["+@[User::TableName]+"]')
if ((SELECT OBJECTPROPERTY( OBJECT_ID(N'[dbo].["+@[User::TableName]+"]'), 'TableHasIdentity')) = 1)
begin
SET IDENTITY_INSERT  [dbo].["+@[User::TableName]+"] Off
 insert into [dbo].["+@[User::TableName]+"]("+PRINT @Names +") select * from [efi_dot_com].[dbo].["+@[User::TableName]+"] 
SET IDENTITY_INSERT  [dbo].["+@[User::TableName]+"] On
end
else
 insert into [dbo].["+@[User::TableName]+"] select * from [efi_dot_com].[dbo].["+@[User::TableName]+"]  "" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

Would you please help me in this regard?

Answer 5

Look at the formatting in your own post. you have got some wrong quotation placed there around Print keyword.

Also remove the "Print" keyword. Print keyword is just used to show the results of a variable in SSMS its not an actualy DML command.

"DECLARE @Names VARCHAR(8000) SELECT @Names = COALESCE(@Names + ', ', '') + name FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].["+@[User::TableName]+"]') if ((SELECT OBJECTPROPERTY( OBJECT_ID(N'[dbo].["+@[User::TableName]+"]'), 'TableHasIdentity')) = 1) begin SET IDENTITY_INSERT  [dbo].["+@[User::TableName]+"] Off  insert into [dbo].["+@[User::TableName]+"](@Names) select * from [efi_dot_com].[dbo].["+@[User::TableName]+"]  SET IDENTITY_INSERT  [dbo].["+@[User::TableName]+"] On end else  insert into [dbo].["+@[User::TableName]+"] select * from [efi_dot_com].[dbo].["+@[User::TableName]+"]" 

I see SQL syntax issue with your Query as well. I would suggest to break up this all into multiple steps i.e. using separate Execute SQL task for different the operations.

Answer 6

As per your suggestion, I will modularize the tasks into

1. get list of tables

2. get list of column names

3. perform insert sql statement

I have a task to get list of table from Excel source to Recordset destination and forward to Foreach loop to get the list of columns name. I am stuck in getting list of columns using variable and expression for the following sql code

DECLARE @c varchar(4000), @t varchar(128)
SET @c = ''
SET @t='tablename'

SELECT @c = @c + c.name + ', '
 FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id
 WHERE o.name = @t
 ORDER BY colid
SELECT Substring(@c, 1, Datalength(@c) - 2)

the problem is how to differentiate between @c and @t for sql variables and ssis variable.

If you provide me a hint to to assign a variable and use expression in ssis for the above code, then I can proceed from that point.

Thanks 

Answer 7

Are you implying how to set up For each loop to use the RecordSet Destination variable?

For that you have to set the For each loop to use the variable under the "Collection" tab set the enumeration as "Foreach ADO Enumerator".

here is a complete tutorial for this in msdn:

http://msdn.microsoft.com/en-us/library/cc879316.aspx

---

Please mark the post as answered if it answers your question

Answer 8

No. I am not implying how to use For each loop.

How to incorporate the following code in ssis and put the result into RecordSet. Tablename comes from another RecordSet. 

I tried using Execute T-SQL Statement Task and got an error

SET @[User::c] = ''
SET @[User::t]=@[User::TableName]
SELECT @c = @c + c.name + ', '
FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id
WHERE o.name =  @[User::t]
ORDER BY colid
SELECT Substring(@[User::c], 1, Datalength@[User::c]) - 2)

error message

Execute SQL Task] Error: Executing the query "
SET @[User::c] = ''
SET @[User::t]=@[User::Tabl..." failed with the following error: "Incorrect syntax near 'User::c'.
Must declare the scalar variable "@c".
Must declare the scalar variable "@".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

When I search on Internet I got the same question I have but no answer for it

similar issue that I am trying to insert data from Source DB to destination DB. Here table name should be dynamic.

Insert Into @DTableName
(@Columns)
values

Select  * from @STABLENAME

In Query detination columns names we should provide. So I have taken @columns variable to return column names with cama seperation

Declare @Columns varchar(max)

SET @Columns =(select SUBSTRING(
 (SELECT ',' + s.name
 FROM (select name from sys.columns
 where object_id=(select object_id from sys.tables  where type='u' and name =@DTableName
 )) s
 FOR XML PATH('')),2,200000) )

While I parse this expression in Execute SQL  SQL Source Staatement expression editor I am getting this Error:

[Execute SQL Task] Error: Executing the query "insert into dbo.Test(@columns  )selec..." failed with the following error: "Must declare the scalar variable "@column".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

If I get any help from either request, then that would be great

Answer 9

you have some misunderstanding about using the SSIS variables versus SQL Script variables. Please refer to some tutorial about how to use the variables in SSIS. Here is a short blog about using Variables with Execute SQL task:

http://www.simple-talk.com/sql/ssis/passing-variables-to-and-from-an-ssis-task/

Please note that you can use "?" to assign a value to a SQL Query variable by passing it as a parameter. SSIS variables cannot be be a part of query like you are trying to use:

Expression Below is Wrong -

"DECLARE @MyVar INT

SET @MyVar = @[User::TableName]"

Expression Below is correct -

"DECLARE @MyVar INT

SET @MyVar = "+ @[User::TableName]

Alternatively you can set TableName variable as the first parameter to the Execute SQL task then Query in the Task can be used as:

"DECLARE @MyVar INT

@MyVar = ?"

i.e. SSIS will replace ? with the first parameter that is mapped to question mark (in order).

 

---

Please mark the post as answered if it answers your question