Single Insert instead of Cursor
-
This is how my current cursor looks like and i need someone's expertise to show me if this is possible with one single insert.
Thanks in Advance
DECLARE Tmp_Cursor CURSOR FOR
SELECT ColA, ColB
FROM #TableD
WHERE ColC = 0
SET @EXITLOOP = 0
WHILE (@EXITLOOP = 0)
BEGIN
SET @EXITLOOP = 1
OPEN Tmp_Cursor
UPDATE #TableD SET ColC = 1
FETCH NEXT FROM Tmp_Cursor INTO @TMP_A, @TMP_B
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@TMP_A <> @TMP_B)
IF(SELECT COUNT(*) FROM #TableD WHERE ColA = @TMP_B) = 0
INSERT INTO #TableD
SELECT A,B,C
FROM SomeTable WHERE A=@TMP_B
SET @CURCOUNT = @@ROWCOUNT
IF(@CURCOUNT <> 0)
BEGIN
SET @EXITLOOP = 0
END
FETCH NEXT FROM Tmp_Cursor INTO @TMP_A, @TMP_B
END
CLOSE Tmp_Cursor
END
DEALLOCATE Tmp_Cursor
-
Can be done quite easily but I'm not sure of what you want to do.
Can you read this and provide all the necessary details?
-
This should give some idea what i am trying to achieve, this is currently not giving the right results and i want to know what am i doing wrong. This is based on above cursor :
INSERT INTO #TableD
SELECT SomeTable.A, SomeTable.B,0
FROMSomeTable
INNER JOIN #TableD
ON SomeTable.A=#TableD.ColB
WHERE #TableD.ColA #TableD.ColB AND
#TableD.ColA NOT IN (SELECT #TableD.ColB
FROM #TableD)
DATA in two columns is such :
123 345
123 789
456 985
885 290
Thanks
-
Still not enough info, please take 2 minutes to read the links I gave you and act accordingly. It's gonna save us both a lot of time.
-
This might help. Let me know if you need anything more. Thanks
CREATE TABLE TableD (
ColA varchar(50),
ColB varchar(50),
ColC int
)
INSERT INTO dbo.TableD (ColA,ColB,ColC) VALUES('018102c','018102c','0')
INSERT INTO dbo.TableD (ColA,ColB,ColC) VALUES('018134C','018134C','0')
INSERT INTO dbo.TableD (ColA,ColB,ColC) VALUES('0280130','0280130','0')
INSERT INTO dbo.TableD (ColA,ColB,ColC) VALUES('ADB0007','0280130','0')
INSERT INTO dbo.TableD (ColA,ColB,ColC) VALUES('ADB0013','0280130','0')
INSERT INTO dbo.TableD (ColA,ColB,ColC) VALUES('05239VA','05239VA','0')
INSERT INTO dbo.TableD (ColA,ColB,ColC) VALUES('0620B70','0620B70','0')
INSERT INTO dbo.TableD (ColA,ColB,ColC) VALUES('7ABF6602','0620B70','0')
INSERT INTO dbo.TableD (ColA,ColB,ColC) VALUES('0620B80','0620B80','0')
INSERT INTO dbo.TableD (ColA,ColB,ColC) VALUES('7ABF6602','0620B80','0')
INSERT INTO dbo.TableD (ColA,ColB,ColC) VALUES('0916C6A','0916C6A','0')
INSERT INTO dbo.TableD (ColA,ColB,ColC) VALUES('0918T40','0918T40','0')
-
I need the table definition and sample data for all the tables involved and the required result with the data sent. I know it's a pain but that removes all the guess work on our part and we can provide the correct solution the first time.
-
Yet another clear case of the one user's trouble with the sp_domyjob system procedure.
Looks like you want to add a row from another table if there's no row where cola=colb
Do an outer join and/or a subselect in one insert statement. That should be all you need to figure it out. If you want to be spoon fed an answer, do like RGR asks and spoon feed us with detail.
-
No comment on this one from me, already cost us a user yesterday
.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply