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),
@.MineDescription [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 ,
@.MineDescription ,
@.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
,MineDescription = @.MineDescription
,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
,MineDescription
,MineLocation
,Country
,Northing
,Easting
,Elevation
,Latitude
,Longitude
,MineLogo )
VALUES (
@.MineID
,@.MineName
,@.MineDescription
,@.MineLocation
,@.Country
,@.Northing
,@.Easting
,@.Elevation
,@.Latitude
,@.Longitude
,@.MineLogo)
FETCH NEXT FROM MYCURSOR INTO
@.MineID,
@.MineName ,
@.MineDescription ,
@.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 MYCURSORYou 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(id int identity, name nvarchar(max))
create table temp1.dbo.mine(id int identity, name nvarchar(max))
insert temp.dbo.mine(name) values('UpdatedValue1')
insert temp.dbo.mine(name) values('UpdatedValue2')
insert temp.dbo.mine(name) values('UpdatedValue3')
insert temp.dbo.mine(name) values('UpdatedValue4')
insert temp.dbo.mine(name) values('UpdatedValue5')
insert temp.dbo.mine(name) values('NewValue6')
insert temp1.dbo.mine(name) values('OldValue1')
insert temp1.dbo.mine(name) values('OldValue2')
insert temp1.dbo.mine(name) values('OldValue3')
insert temp1.dbo.mine(name) values('OldValue4')
insert temp1.dbo.mine(name) values('OldValue5')
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|||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|||
Quote:
Originally Posted by 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
--
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)
No comments:
Post a Comment