cursor not adding data, adding only when executing separately

Question

I have stored procedure & cursor loop which I am using to add data in main table issue is I am adding data from csv file to temp table and from temp table to main table

alter PROCEDURE [dbo].[CreateHub]
  @HubName varchar(100)
AS

INSERT INTO dbo.HUB1
                         (HUB_NAME)
SELECT        @HubName
WHERE        (NOT EXISTS
                             (SELECT        ID_HUB, HUB_NAME
                               FROM            dbo.HUB1
                               WHERE        (HUB1.hub_name = @HubName)))

SELECT        ID_HUB AS newHubId
FROM            dbo.HUB1
WHERE        (hub1.hub_name = @HubName)

GO

Once this is done, there is another code which is doing bulk insert from csv file and running cursor loop for adding data in main table

bulk insert [dbo].[HUB_temp]
from 'C:\POPAD-DAT\HUB1.csv'
with (fieldterminator = ',', rowterminator = '\n')
go

DECLARE @sSQL AS nVARCHAR(100)
DECLARE @ItemsFromCSV  AS nvarchar(200)

DECLARE sql_cursor_hub CURSOR
    FOR SELECT HUB_NAME FROM HUB_temp
OPEN sql_cursor_hub

FETCH NEXT FROM sql_cursor_hub 
INTO @ItemsFromCSV  -- Multiple variables for multiple CSV columns will be required

WHILE @@FETCH_STATUS = 0
BEGIN

set @sSQL = 'EXEC  [dbo].[CreateHub] ' + @ItemsFromCSV  -- AND OTHER Parameters
print @sSQl
EXECUTE sp_executesql @sSQL

FETCH NEXT FROM sql_cursor_hub
END 

CLOSE sql_cursor_hub;

DEALLOCATE sql_cursor_hub;

Running cursor is not adding data its showing like same id and showing different u=hubname but not inserting data

While if I am running separately executing SP it is adding like this

EXEC  [dbo].[CreateHub] 'SGGSP30'
EXEC  [dbo].[CreateHub] 'USGSP20'

can you please help where exactly iam going wrong

Joakim Danielson · Accepted Answer · 2018-10-15 11:49:44Z

1

You forgot to do INTO in the second fetch

WHILE @@FETCH_STATUS = 0
BEGIN

  set @sSQL = 'EXEC  [dbo].[CreateHub] ' + @ItemsFromCSV  -- AND OTHER Parameters
  print @sSQl
  EXECUTE sp_executesql @sSQL

  -- This is changed
  FETCH NEXT FROM sql_cursor_hub INTO @ItemsFromCSV
END

You should also take care of your parameter declarations, @ItemsFromCSV is varchar(200) but the stored proc parameter is only varchar(100) and @sSQL is only 100 in size but you add @ItemsFromCSV to it.

answered Oct 15, 2018 at 11:49

Joakim Danielson

53.1k5 gold badges36 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

cursor not adding data, adding only when executing separately

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related