SQL Server Performance Forum – Threads Archive

error in the stored proced execution

use testdb; IF OBJECT_ID (‘usp_getallrecballantine’,’P’) IS NOT NULL
DROP PROCEDURE usp_getallrecballantine;
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[ballantineholddups]’)
AND type in (N’U’))
DROP TABLE [dbo].[ballantineholddups]
go
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[ballantineholdkey]’)
AND type in (N’U’))
DROP TABLE [dbo].[ballantineholdkey]
go
CREATE PROCEDURE usp_getallrecballantine
AS
select lastname,firstname,max(address1) as address1,max(address2) as address2,max(address3) as address3,SALUTATION,count(*) as noofrecords
from ballantine
group by lastname,firstname,SALUTATION
HAVING COUNT(*)>1
GO SELECT lastname,firstname,max(address1) as address1,max(address2) as address2,max(address3) as address3,SALUTATION, col3=count(*)
INTO ballantineholdkey
FROM ballantine
GROUP BY lastname,firstname,address1,address2,address3,SALUTATION
HAVING count(*) > 1 SELECT DISTINCT ballantine.*
INTO ballantineholddups
FROM ballantine, ballantineholdkey
WHERE ballantine.lastname = ballantineholdkey.lastname
AND ballantine.firstname = ballantineholdkey.firstname
and ballantine.address1 = ballantineholdkey.address1
and ballantine.address2 = ballantineholdkey.address2
and ballantine.address3 = ballantineholdkey.address3
and ballantine.SALUTATION = ballantineholdkey.SALUTATION DELETE ballantine
FROM ballantine, ballantineholdkey
WHERE ballantine.lastname = ballantineholdkey.lastname
AND ballantine.firstname = ballantineholdkey.firstname
and ballantine.address1 = ballantineholdkey.address1
and ballantine.address2 = ballantineholdkey.address2
and ballantine.address3 = ballantineholdkey.address3
and ballantine.SALUTATION = ballantineholdkey.SALUTATION INSERT ballantine SELECT * FROM ballantine_address IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[ballantine_address]’)
AND type in (N’U’))
DROP TABLE [dbo].[ballantine_address]
go
Create table ballantine_address
(
ID int identity(1, 1)
constraint ballantine_pk primary key,
lastname nvarchar(510),
firstname nvarchar(510),
address1 nvarchar(510),
address2 nvarchar(510),
address3 nvarchar(510))
INSERT ballantine_address SELECT * FROM ballantine
this is stored procedure i am getting following error (2 row(s) affected) (0 row(s) affected) (0 row(s) affected)
Msg 208, Level 16, State 1, Line 27
Invalid object name ‘ballantine_address’.
Msg 8101, Level 16, State 1, Line 12
An explicit value for the identity column in table ‘ballantine_address’ can only be specified when a column list is used and IDENTITY_INSERT is ON. sql server

---

It seems also have ID on the ballantine table.
If you want to insert that value, you should place
SET IDENTITY_INSERT ballantine_address ON
before your insert statement and expand the statement to
INSERT ballantine_address(
ID,
LASTNAME,
..
)
SELECT
ID,
LASTNAME, if you don’t want the ID from ballantine table, expand your insert statement like:
INSERT ballantine_address(
LASTNAME,
..
)
SELECT
LASTNAME,
.. May the Almighty God bless us all!
www.empoweredinformation.com

---

]]>