Solved

SET IDENTITY_INSERT ON

Posted on 2013-05-12
2
483 Views
Last Modified: 2013-05-14
Hello
for some sync reasons ... i've create a trigger for Insert on a specific table and i need  need to insert the same data in other database
the problem is sql server not allow to use
 SET IDENTITY_INSERT ON  to refer a tble in other database

how i can do that
0
Comment
Question by:ali_alannah
[X]
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
2 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39159545
create a stored procedure, in the other database of yours and put the set identity insert on in the stored proc.

Now create a temp table with the same schema as the target table and insert the data into the temp table in the trigger.

Use the same temp table to populate the target table from the stored proc.
As temporary tables can be accessed acorss the databases, they will have the same data as long as it is the same server and the same SPID.

The below code is just a sample, this wont compile 100% in your system, it is just for assisting you.
Example code

[b]trigger[/b]

create trigger trg_abcd 
AS
BEGIN

create table #abcd
(
 a int
, b int
, c int
)

insert into #abcd select * from deleted -- (or inserted)

exec <anotherDatabase>.dbo.usp_abcd
END

[b]Stored procedure[/b]

create procedure usp_abcd
AS
BEGIN
SET IDENTITY_INSERT ON
insert into <target Table>
select * from #abcd
END

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39159666
Unrelated to your problem and in case you were not aware SET IDENTITY_INSERT requires the following permissions:
User must own the object, or be a member of the sysadmin fixed server role, or the db_owner and db_ddladmin fixed database roles.
This requirement, in most shops, disqualifies its use in any user code.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

688 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