Do you understand this Error msg (ERROR Msg 512, Level 16, State 1, Line 33)

[Sally1053]

I have created a procedure which start by fecthing data from DB-X and put in into the temporary memory. what im trying to do now is to take data from temporary memory insert/update DB-Y.

But now I get this ERROR Msg 512, Level 16, State 1, Line 33
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


HERE IS MY PROCEDURE


DECLARE @MineID int,
@MineName varchar(80),
@MineDescriptio n [varchar](80) ,
@MineLocation varchar(80),
@Country varchar(80),
@Northing float,
@Easting float,
@Elevation float,
@Latitude float ,
@Longitude float,
@MineLogo varbinary(max)

DECLARE MYCURSOR CURSOR
FOR SELECT * FROM [TLC].[DBO].[MINE]
OPEN MYCURSOR
FETCH NEXT FROM MYCURSOR INTO
@MineID,
@MineName ,
@MineDescriptio n ,
@MineLocation ,
@Country ,
@Northing ,
@Easting ,
@Elevation ,
@Latitude,
@Longitude,
@MineLogo
WHILE @@FETCH_STATUS = 0

BEGIN

IF @MineID =(select MineID from [TEST].[dbo].[MINE1])
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] ON
BEGIN UPDATE [TEST].[dbo].[MINE1]
SET MineName = @MineName
,MineDescriptio n = @MineDescriptio n
,MineLocation = @MineLocation
,Country =@Country
,Northing = @Northing
,Easting = @Easting
,Elevation = @Elevation
,Latitude = @Latitude
,Longitude = @Longitude
,MineLogo =@MineLogo
where MineId = @MineID


END
IF @MineID <> (select MineID from [TEST].[dbo].[MINE1])
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] ON
INSERT INTO [TEST].[dbo].[MINE1]
(MineID
,MineName
,MineDescriptio n
,MineLocation
,Country
,Northing
,Easting
,Elevation
,Latitude
,Longitude
,MineLogo )
VALUES (
@MineID
,@MineName
,@MineDescripti on
,@MineLocation
,@Country
,@Northing
,@Easting
,@Elevation
,@Latitude
,@Longitude
,@MineLogo)

FETCH NEXT FROM MYCURSOR INTO

@MineID,
@MineName ,
@MineDescriptio n ,
@MineLocation ,
@Country ,
@Northing ,
@Easting ,
@Elevation ,
@Latitude,
@Longitude,
@MineLogo

END
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] OFF
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] OFF
CLOSE MYCURSOR
DEALLOCATE MYCURSOR

[almaz]

You don't need cursors for this purpose. Please look at following sample that demonstrates how you should synchronize your data:

create database temp
create database temp1
go
create table temp.dbo.mine(i d int identity, name nvarchar(max))
create table temp1.dbo.mine( id int identity, name nvarchar(max))

insert temp.dbo.mine(n ame) values('Updated Value1')
insert temp.dbo.mine(n ame) values('Updated Value2')
insert temp.dbo.mine(n ame) values('Updated Value3')
insert temp.dbo.mine(n ame) values('Updated Value4')
insert temp.dbo.mine(n ame) values('Updated Value5')
insert temp.dbo.mine(n ame) values('NewValu e6')
insert temp1.dbo.mine( name) values('OldValu e1')
insert temp1.dbo.mine( name) values('OldValu e2')
insert temp1.dbo.mine( name) values('OldValu e3')
insert temp1.dbo.mine( name) values('OldValu e4')
insert temp1.dbo.mine( name) values('OldValu e5')
select * from temp.dbo.mine
select * from temp1.dbo.mine

update temp1.dbo.mine
set
name = Source.name
from temp.dbo.mine Source
where mine.id = Source.id

set identity_insert temp1.dbo.mine on
insert temp1.dbo.mine( id, name)
select id, name
from temp.dbo.mine
where id not in (select id from temp1.dbo.mine)
set identity_insert temp1.dbo.mine off

select * from temp.dbo.mine
select * from temp1.dbo.mine

drop database temp
drop database temp1

[Sally1053]

Thank you for your response, But my problem is not resolved yet, What im actually trying to do is to take data from Another DB-TLC to DB-Test. And these two database they are not integrated and we can't intergrate them.So what will happen is: if the data exist it can update else insert a new record. I'm not creating database from scratch.

this error is trigged by these statemement

IF @BlasterID <> (select BlasterID from Blasters )
INSERT INTO [TEST].[dbo].[Blasters]

BELOW IS THE ERROR
{Msg 512, Level 16, State 1, Line 33
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.}

It seems like SQL doesn't want that , I have also tried the IF NOT EXIST clause but still it doesn't help. Please if you have any ideas agains on how to handle this with SQL please help

[almaz]

Thank you for your response, But my problem is not resolved yet, What im actually trying to do is to take data from Another DB-TLC to DB-Test

Have you looked at sample I provided? It demonstrates how to synchronize data from two different databases in more efficient way than you do.
----
Concerning your particular problem with
IF @BlasterID <> (select BlasterID from Blasters ):
You can compare a variable (@BlasterID) with resultset (select BlasterID from Blasters) only when resultset contains a single row with single column. In your case resultset contains all rows from Blasters table, not one. Correct solution is:
IF NOT EXISTS (select * from Blasters WHERE BlasterID = @BlasterID)