FETCH into TABLE? 1

[RJ5]

Hello.

Can I store the values of a cursor into a temporary table?

Can you give me a sampe code?

Thanks.

Randy

 

[Cagliostro]

if you are fetching into some variables
for example

fetch next xcur into @varbl

just use an insert statement

insert into xxx ... values(varbl)
John Fill

ivfmd@mail.md

 

[RJ5]

Thanks man.

 

[tlbroadbent]

If you are talking about cursors in T-SQL, such as in a stored procedure, it would be much more effiecient to insert the results of the query used to create the cursor into a temporary table. There is no need to use a cursor for this.

Rather than declaring the cursor

DECLARE tbl_cursor CURSOR
FOR SELECT col1, col2, col3, col4 FROM tbl

and then writing code to insert records to a temporary table, just insert directly into the temporary table.

SELECT col1, col2, col3, col4
INTO #temp FROM tbl

Of course, you can fetch the cursor data and insert.

Create table #temp(col1 char(6), col2 int, col3 int, col4 datetime)

DECLARE tbl_cursor CURSOR
FOR SELECT col1, col2, col3, col4 FROM tbl

OPEN tbl_cursor
FETCH NEXT FROM tbl_cursor INTO @col1, @col2, @col3, @col4

WHILE @@FETCH_STATUS = 0
BEGIN
Insert #temp(col1,col2,col3,col4)
Values(@col1,@col2,@col3,@col4)
FETCH NEXT FROM tbl_cursor INTO @col1, @col2, @col3, @col4
END

CLOSE tbl_cursor
DEALLOCATE tbl_cursor
Terry

http://members.home.net/tlbroadbent/prog.htm

Neither success nor failure is ever final. -Roger Babson

 

[RJ5]

Thanks Terry.

For example I have 2 tables, the first (tbl1) contains the distinct IDNo, Name , the second (tbl2) contains IDNo, Order, Total

tbl1: tbl2:
IDNO Name IDNO Order Total
A0001 MJ A0001 Sauce 3
A0002 CB A0001 Juice 4
A0003 KB A0002 Meat 3
A0002 Candy 4
A0003 Sauce 1
A0003 Meat 1

Using SQL, how do I process the data to get the following results?

IDNO Name Total (Items bought)
A0001 MJ 7
A0002 CB 7
A0003 KB 2

In VB6, I use DO..WHILE to get the result.

 

[tlbroadbent]

Select tbl1.IDNo, tbl1.[Name], Sum(tbl2.Total) As ItemsBought
From tbl1 Inner Join tbl2
On tbl1.IDNo=tbl2.IDNo
Group By tbl1.IDNo, tnl1.[Name] Terry

http://members.home.net/tlbroadbent/prog.htm

Neither success nor failure is ever final. -Roger Babson

 

[RJ5]

Thanks again Terry.

Now for my final question. How do I insert the result of this select statement into a table?

randy

 

[tlbroadbent]

Create table #t (IDNo int, [Name] Varchar(40), TotSold int)

Insert #t
Select tbl1.IDNo, tbl1.[Name], Sum(tbl2.Total) As ItemsBought
From tbl1 Inner Join tbl2
On tbl1.IDNo=tbl2.IDNo
Group By tbl1.IDNo, tnl1.[Name Terry

http://members.home.net/tlbroadbent/prog.htm

Neither success nor failure is ever final. -Roger Babson

 

[RJ5]

Thanks again Terry.

For my last question.....hehehehe...gotcha.

Randy