Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stored Procedure help 3

Status
Not open for further replies.
GUJUm0deL

GUJUm0deL

Programmer
Jan 16, 2001
3,676
0
0
US
Hi all, i'm trying to create a stored procedure where I push all data from one table to another in the same dB.

This is what I have done so far:
Code: 
CREATE PROCEDURE test1.sendtolive AS (
  insert into test2.dbo.Department_Info
  select * from Department_Info where Is_Live = 1
)

The above code should push ALL info from the Department_Info table from TEST1 dB to TEST2 dB. I am getting the following error:

Code: 
Error 8101: An explicit value for the identity column in table 'test2.dbo.Department_info' can only be specified when a column list is used on IDENTITY_INSERT is ON.

I think that means I need to do something like:

SELECT @Department_Info_ID=@@identity
SELECT @Department_Info_ID AS Department_Info_ID


Is that right? If not, then any help would be great.

Thanks.

[sub]
____________________________________
Just Imagine.
[sub]
 
Sort by date Sort by votes
select all the collumns except the identity column from the Department_Info table or use a column list

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --
 
Upvote 0 Downvote
Thanks for the help SQLDenis.

How do I use/create a column list?

By the way, does this effect the way the data is being pushed? I mean, let's say record 1012 is being pushed from TEST1 to TEST2, how does SQL server know to push record 1012 if the identity column is not being pushed or is shut off?

Thanks.

[sub]
____________________________________
Just Imagine.
[sub]
 
Upvote 0 Downvote
If you need the same exact data (including identity field)
do this

set identity insert on

insert into test2.dbo.Department_Info
select * from Department_Info where Is_Live = 1

set identity inset off

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --
 
Upvote 0 Downvote
A column is is the list of the columns

insert into tableA (field1, field2)
select field1, field2
from TableB

You just name the columns


“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --
 
Upvote 0 Downvote
Hi, thanks. I did this:
Code: 
CREATE PROCEDURE test1.sendtolive AS
  set @@identity insert on

  insert into test2.dbo.Department_Info
  select * from Department_Info where Is_Live = 1

  set @@identity insert off

I am getting an error:
Code: 
Incorrect syntax near @@identity

[sub]
____________________________________
Just Imagine.
[sub]
 
Upvote 0 Downvote
Code: 
SET IDENTITY_INSERT test2.dbo.Department_Info ON

INSERT test2.dbo.Department_Info
  select * from Department_Info where Is_Live = 1

SET IDENTITY_INSERT test2.dbo.Department_Info OFF

--James
 
Upvote 0 Downvote
My bad it's

SET IDENTITY_INSERT ON

insert into test2.dbo.Department_Info
select * from Department_Info where Is_Live = 1

SET IDENTITY_INSERT OFF


“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --
 
Upvote 0 Downvote
Hmm, maybe i'm doing something wrong. I still get an error. I have:
Code: 
CREATE PROCEDURE test1.sendtolive AS
  SET IDENTITY_INSERT ON

  insert into test2.dbo.Department_Info
  select * from Department_Info where Is_Live = 1

  SET IDENTITY_INSERT OFF

and I get: Incorrect syntax near ON and Incorrect syntax near OFF

I tried both your suggestion and JamesLean's

[sub]
____________________________________
Just Imagine.
[sub]
 
Upvote 0 Downvote
SET IDENTITY_INSERT ON is set for the connection
you can't put it in a proc (I think, but I might be wrong)

what you can do is not making the column an identity in the second table, move the data over and then making it identity again


“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --
 
Upvote 0 Downvote
> SET IDENTITY_INSERT ON is set for the connection

Where? In Yukon? :p [peace]

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
Oops

SET IDENTITY_INSERT test2.dbo.Department_Info ON

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --
 
Upvote 0 Downvote
Well the TEST2 dB is the exact mirror of TEST1 (TEST1 is meant for CMS and TEST2 is meant for live site). They want both tables to be same, even in respect to its datatypes.

I can't use "SET IDENTITY_INSERT test2.dbo.Department_Info ON" in the SP itself? Then where else does it need to be? I would be calling this SP from backend code (Coldfusion).

[sub]
____________________________________
Just Imagine.
[sub]
 
Upvote 0 Downvote
You can use it, I goofed a bit, I tested it out on my system and it works (make sure you reset it after you are done
SET IDENTITY_INSERT test2.dbo.Department_Info ON
do your stuff
SET IDENTITY_INSERT test2.dbo.Department_Info OFF

Why don't you do this with a trigger?

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --
 
Upvote 0 Downvote
I must be doing something wrong. I am getting the same error.

This is what I have now:
Code: 
CREATE PROCEDURE test1.sendtolive AS
  SET IDENTITY_INSERT test2.dbo.Department_Info ON
  INSERT INTO test2.dbo.Department_Info
  SELECT * FROM Department_Info WHERE Is_Live = 1
  SET IDENTITY_INSERT test2.dbo.Department_Info OFF

I am creating the SP from SQL Server by righ clicking "Stored Procedures" under TEST1 dB and selecting "New Stored Procedure". I click the CHECK SYNTAX button and I get that error.

[sub]
____________________________________
Just Imagine.
[sub]
 
Upvote 0 Downvote
your proc name can not be test1.sendtolive make it sendtolive instead

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --
 
Upvote 0 Downvote
No luck. :-(

CREATE PROCEDURE sendtolive AS
SET IDENTITY_INSERT test2.dbo.Department_Info ON
INSERT INTO test2.dbo.Department_Info
SELECT * FROM Department_Info WHERE Is_Live = 1
SET IDENTITY_INSERT test2.dbo.Department_Info OFF

[sub]
____________________________________
Just Imagine.
[sub]
 
Upvote 0 Downvote
[banghead]
SELECT * .... nope, won't work. Specify all column names instead.



------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
... more precisely, I think SELECT * should work but you must specify target column list, e.g.:

INSERT INTO test2.dbo.Department_Info (blah blah blah)
SELECT * FROM ...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
85
f0rg3tfu1
f0rg3tfu1
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
218
Chris Miller
Chris Miller
bmacbmac
  • Locked
  • Question
Help searching within this Json file
Replies
0
Views
43
bmacbmac
bmacbmac
G12Consult
  • Locked
  • Question
Help with some code
Replies
2
Views
64
SaltyTheFrog
SaltyTheFrog
Cpreston
  • Locked
  • Question
Field change as caused issues to store procedure
Replies
19
Views
98
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top