How to use 'cursor' to loop through results and store into temp table

[Link82]

Hi everyone,

It's my first time with looping through data in a query. I know there are two ways of doing this and one is using CURSOR (I read it'snot the nicest thing to use). Wouldn't mind hearing your thoughts.

I will copy and paste my first attempt at usingCURSOR and FETCH. I need to loop through the results from a select statement and put them into a temp table called tblMain. Here it goes:

--keep in mind #tblMain has already been created...

--declaring my looper
DECLARE ACREmpsCursor CURSOR SCROLL FOR

--for the following query
SELECT DISTINCT
    date_time, 
    ssn,
    emplid,
    employee_name,
    division,
    payroll_sect
  FROM tblRecords a

    WHERE ((Finalized = 0)
			or
			(Finalized = 1 and date_time = currentPayrollDate ))
    ORDER BY Division, payroll_sect, employee_name

-- I need to store above results into #tblMain, not sure how this 
-- part works...
-- does ACREmpsCursor hold the result set from the above query?
OPEN ACREmpsCursor;
FETCH NEXT FROM ACREmpsCursor;

-- while there is a record, do the stmts between begin and end..?
WHILE @@FETCH_STATUS = 0
BEGIN

	INSERT INTO #tblMain(emplid, name, division, payroll_sect)
	VALUES(emplid, employee_name, division, payroll_sect)

END

-- doesn't quite work...

Here's the error message:
"Msg 128, Level 15, State 1, Procedure getACRSummaryEmployeesDuplicate, Line 52
The name 'emplid' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."

So it has a problem with 'emplid'. Am I to use variables like "select @tempEmplid = emplid ... from tblScanRecord..." ?

Thanks.

[cgyrob]

The first question I have is why are you using a cursor just to fill a temp table when you can simply fill the temp table with your select statement.

Select emplid, name, division, payroll_sect
Into #tableMain
FROM tblRecords a
WHERE ((Finalized = 0) or
(Finalized = 1 and date_time = currentPayrollDate ))
ORDER BY Division, payroll_sect, employee_name

If you must use a cursor you should look up the syntax again. You have to declare variables and fill the variables in your fetch statements.

--keep in mind #tblMain has already been created...
--declare cursor variables
DECLARE @emplid int, @employee_name varchar(50), @division varchar(10), @payroll_sect varchar(10)

--declaring my looper
DECLARE ACREmpsCursor CURSOR SCROLL FOR

--only select the fields you require
SELECT DISTINCT
    emplid,
    employee_name,
    division,
    payroll_sect
  FROM tblRecords a
 WHERE ((Finalized = 0) or
(Finalized = 1 and date_time = currentPayrollDate ))
 ORDER BY Division, payroll_sect, employee_name 
-- do you really need to order the records, waste of resources if not required

-- I need to store above results into #tblMain, not sure how this 
-- part works...
-- does ACREmpsCursor hold the result set from the above query?
OPEN ACREmpsCursor
FETCH NEXT FROM ACREmpsCursor
Into @emplid, @employee_name, @division, @payroll_sect
--make sure variables are in the same order as select statement
-- while there is a record, do the stmts between begin and end..?
WHILE @@FETCH_STATUS = 0
BEGIN

	INSERT INTO #tblMain(emplid, name, division, payroll_sect)
	VALUES(@emplid, @employee_name, @division, @payroll_sect)

--fill cursor with next set of values
FETCH NEXT FROM ACREmpsCursor
INTO @emplid, @employee_name, @division, @payroll_sect

END

I just quickly re-used the script you wrote so I am not positive if it will work but you should see the differences in syntax and the use of the variables in your fetches.

[yangski]

The first question I have is why are you using a cursor just to fill a temp table when you can simply fill the temp table with your select statement.

Select emplid, name, division, payroll_sect
Into #tableMain
FROM tblRecords a
WHERE ((Finalized = 0) or
(Finalized = 1 and date_time = currentPayrollDate ))
ORDER BY Division, payroll_sect, employee_name

If you must use a cursor you should look up the syntax again. You have to declare variables and fill the variables in your fetch statements.

--keep in mind #tblMain has already been created...
--declare cursor variables
DECLARE @emplid int, @employee_name varchar(50), @division varchar(10), @payroll_sect varchar(10)

--declaring my looper
DECLARE ACREmpsCursor CURSOR SCROLL FOR

--only select the fields you require
SELECT DISTINCT
    emplid,
    employee_name,
    division,
    payroll_sect
  FROM tblRecords a
 WHERE ((Finalized = 0) or
(Finalized = 1 and date_time = currentPayrollDate ))
 ORDER BY Division, payroll_sect, employee_name 
-- do you really need to order the records, waste of resources if not required

-- I need to store above results into #tblMain, not sure how this 
-- part works...
-- does ACREmpsCursor hold the result set from the above query?
OPEN ACREmpsCursor
FETCH NEXT FROM ACREmpsCursor
Into @emplid, @employee_name, @division, @payroll_sect
--make sure variables are in the same order as select statement
-- while there is a record, do the stmts between begin and end..?
WHILE @@FETCH_STATUS = 0
BEGIN

	INSERT INTO #tblMain(emplid, name, division, payroll_sect)
	VALUES(@emplid, @employee_name, @division, @payroll_sect)

--fill cursor with next set of values
FETCH NEXT FROM ACREmpsCursor
INTO @emplid, @employee_name, @division, @payroll_sect

END

I just quickly re-used the script you wrote so I am not positive if it will work but you should see the differences in syntax and the use of the variables in your fetches.

can I also use cursor when performing bulk insert (from .csv file to MS SQL 2005) ???

Sorry for being off-topic.

[cgyrob]

You should open a seperate topic because someone might have a different answer and they will not see the question plus it is frowned upon.

I would use a DTS/SSIS package for bulk inserts. They can be easily created for bulk inserts and scheduled to run whenever you want. You can also do transform operations within the package to cleanse the data being inserted.

[yangski]

You should open a seperate topic because someone might have a different answer and they will not see the question plus it is frowned upon.

I would use a DTS/SSIS package for bulk inserts. They can be easily created for bulk inserts and scheduled to run whenever you want. You can also do transform operations within the package to cleanse the data being inserted.

I already did... :) maybe you'd like to take a look at it.. maybe you have any ideas.. :) newly created thread.

[Link82]

You should open a seperate topic because someone might have a different answer and they will not see the question plus it is frowned upon.

I would use a DTS/SSIS package for bulk inserts. They can be easily created for bulk inserts and scheduled to run whenever you want. You can also do transform operations within the package to cleanse the data being inserted.

yangski, the hi-jacking of thread--not so cool. G'luck with your thread.

cgyrob, thanks for the help. I didn't know about INTO. I'm a C#/.NET programmer who now has to make a backend report and display it in a web application. These are old stored procs of which I'm trying to make sense of. The report (seems) massive. I appreciate all the help I've gotten here at daniweb. The solutions are usually... simple. Just that I haven't used sql past the usual inner or outer joins.

Back on topic. Using INTO to fill the temp table. The temp table, #tblMain, has similar fields and headers, but when I do what you suggested, it takes the header names from the select statement. Anyway to do something like the following:

-- they both have same fields, but I want tblMain to keep its own
-- header titles like emplid vs. emp_ID from tblRecord
SELECT emp_ID, emp_name, div, sect
INTO #tblMain(emplid, name, Division, payroll_sect)
FROM tblRecord a

/*
[B]Edit:[/B] I'm sure you can do something like emp_ID as emplid... but... when I create #tblMain, it has more than just these fields listed.  It has more like 10.  After filling it here, I was thinking that the rest of the fields would be left empty, but it seems that #tblMain will only contain emplid, name, Division, payroll_sect.  How do I do this so I can have a temp table that's half full after this statement and then leaves the other fileds/columns alone?  I.e. Want this:
emplid    name    division    payroll_sect   address    pay     addtl_money
123        Allie H    56           12                 
234        Alex M    70           15                  

NOT:
emp_ID        emp_name      div         sect   
123              Allie H               56         12
2234            Alex M               70         15


--my other fields are gone from when I created this temp table!

*/

I've also noticed that if I do select * from tblMain where I created the table, I get 0 rows returned. But if I do it from the nested sp, it returns rows fine. Does that mean that if I call two sp's, i.e.
1. create table #tblMain(blah, blah)
2. call sp which will fill #tblMain
3. call sp to manipulate data already in #tblMain

that #3 won't really work because #tblMain's data has been erased? Would #3 need to be nested in #2 to persist data?

If something sounds confusing, let me know so I can elaborate on it some more. Thanks.

[Link82]

Ok, so after googling I tried something like:

INSERT INTO #tblMain(emplid, name, Division, payroll_sect)
SELECT DISTINCT
    emplid,
    dbo.getEmployeeNameFromSSN( SSN ) as employee_name,
    Division,
    payroll_sect
FROM tblRecord a
WHERE ....

It seems to keep my other fields in tact. And also, when I do select * from #tblMain where I created the table, I get results then. So why doesn't it when using select field1, field2 INTO #tblMain FROM....?

I'm really interested in this now. Lastly, which one is more flexible? And least costly? Why?

Appreciate any help!

[cgyrob]

When your doing the select into #temp statement you are creating an actual temp table that is living in virtual memory and is only visible within the session it is created and is immediately dropped once this session is closed.

You can either perform the whole process within one sp or use a base procedure that calls the other procedures, you just have to remember that the table session must be maintained or else it is dropped.