Cursor with dynamic table name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cursor with dynamic table name

Author

Topic

Neftoma
Starting Member

12 Posts

Posted - 2012-09-06 : 08:09:42

Dear All,

Please help with the following: I do not manage to make the code which would put the results into a new table with each new cursor value,the name of the table would contain a dynamic part: the value of the current row in cursor. For example, if the value in the current cursor row is 10, I would like to create the table with the name ‘table10’, or even just ‘10’ would already make me happy.

Here is the code and thank you in advance:

DECLARE mancd CURSOR FOR select distinct managercode from dbo.X
declare @cd int

OPEN mancd
FETCH NEXT FROM mancd INTO @cd

WHILE @@FETCH_STATUS = 0

begin
begin transaction

SELECT distinct x,y,z

??? into table’@cd’ ???
from Z
where managercode=@cd

commit

FETCH NEXT FROM mancd INTO @cd
END

Mike Jackson
Starting Member

37 Posts

Posted - 2012-09-06 : 08:23:08

sql= 'Select * into ' + @cd + ' From Z where managercode = ' + @cd

execute sql

Neftoma
Starting Member

12 Posts

Posted - 2012-09-06 : 08:28:43

thank you, but it didn't work. Gives an error in sql line

quote:
Originally posted by Mike Jackson

sql= 'Select * into ' + @cd + ' From Z where managercode = ' + @cd

execute sql

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-06 : 08:48:40

Here is an example (it is working).
You can change it to what you need.

create table #manager (mgrId int, ttt varchar(30))
insert #manager
select 1, 'aaa' union all
select 10,'bbb' union all
select 45,'ccc'

create table #managerData (mgrId int, data varchar(255))
insert #managerData
select 1,'abcdefg' union all
select 1,'yxvt' union all
select 10,'test' union all
select 45,'blabla' union all
select 45,'cursors are bad'

DECLARE mancd CURSOR FOR select distinct mgrId from #manager
declare @cd int
declare @sql varchar(max)

OPEN mancd
FETCH NEXT FROM mancd INTO @cd


WHILE @@FETCH_STATUS = 0

begin
begin transaction 

set @sql = 'SELECT distinct mgrId,data into table'+convert(varchar(10),@cd)+' from #managerData where mgrId='+convert(varchar(10),@cd)
exec (@sql)
commit

FETCH NEXT FROM mancd INTO @cd
END
close mancd
deallocate mancd

Too old to Rock'n'Roll too young to die.

Neftoma
Starting Member

12 Posts

Posted - 2012-09-06 : 12:38:39

Dear Webfred,

Thank you very much for your help! Your code works perfectly well!
There's just one more problem I have with this task: I do not know where to sqeeze a piece of my code into your code to make it compute the variables with which I actually try to populate the table. I would need to put the piece of code in "green" somwhere into your code...

Would be very grateful if you could have a look at it again:

DECLARE mancd CURSOR FOR select distinct managercode from dbo.S12hchanges2
declare @cd int
declare @sql varchar(max)

OPEN mancd
FETCH NEXT FROM mancd INTO @cd

WHILE @@FETCH_STATUS = 0

begin
begin transaction

set @sql = 'SELECT distinct managercode, data into table'+convert(varchar(10),@cd)+' from dbo.S12hchanges2 where managercode='+convert(varchar(10),@cd)

SELECT distinct datepart(qq,trd.tradedate) as q, YEAR(trd.tradedate) as y, trd.clientcode,trd.clientmgrcode,m.CRSP_permno,
SUM(trd.Side*trd.Volume) over (Partition by trd.clientcode,trd.clientmgrcode,m.CRSP_permno, datepart(quarter,trd.tradedate), YEAR(trd.tradedate) )as ANCchange
from aetd.dbo.TRD_EQ_US_MINI trd
inner join dbo.ANC_CRSP_stock_match m on m.ANCERNO_cusip=trd.cusip and m.Y=datepart(year,trd.tradedate) and m.m=datepart(month,trd.tradedate)
where managercode=@cd]

exec (@sql)

commit
print 'Managercode '+ convert(varchar,@cd) + ' processed'

FETCH NEXT FROM mancd INTO @cd
END
close mancd
deallocate mancd

quote:

Originally posted by webfred

Here is an example (it is working).
You can change it to what you need.

create table #manager (mgrId int, ttt varchar(30))
insert #manager
select 1, 'aaa' union all
select 10,'bbb' union all
select 45,'ccc'

create table #managerData (mgrId int, data varchar(255))
insert #managerData
select 1,'abcdefg' union all
select 1,'yxvt' union all
select 10,'test' union all
select 45,'blabla' union all
select 45,'cursors are bad'

DECLARE mancd CURSOR FOR select distinct mgrId from #manager
declare @cd int
declare @sql varchar(max)

OPEN mancd
FETCH NEXT FROM mancd INTO @cd


WHILE @@FETCH_STATUS = 0

begin
begin transaction 

set @sql = 'SELECT distinct mgrId,data into table'+convert(varchar(10),@cd)+' from #managerData where mgrId='+convert(varchar(10),@cd)
exec (@sql)
commit

FETCH NEXT FROM mancd INTO @cd
END
close mancd
deallocate mancd

Too old to Rock'n'Roll too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-06 : 12:45:42

maybe this:

DECLARE mancd CURSOR FOR select distinct managercode from dbo.S12hchanges2
declare @cd int
declare @sql varchar(max)

OPEN mancd
FETCH NEXT FROM mancd INTO @cd


WHILE @@FETCH_STATUS = 0

begin
begin transaction 


set @sql = 'SELECT distinct datepart(qq,trd.tradedate) as q, YEAR(trd.tradedate) as y, trd.clientcode,trd.clientmgrcode,m.CRSP_permno,
SUM(trd.Side*trd.Volume) over (Partition by trd.clientcode,trd.clientmgrcode,m.CRSP_permno, datepart(quarter,trd.tradedate), YEAR(trd.tradedate) )as ANCchange
into table'+convert(varchar(10),@cd)+
' from aetd.dbo.TRD_EQ_US_MINI trd
inner join dbo.ANC_CRSP_stock_match m on m.ANCERNO_cusip=trd.cusip and m.Y=datepart(year,trd.tradedate) and m.m=datepart(month,trd.tradedate)
where managercode = '+convert(varchar(10),@cd)

exec (@sql)

commit
print 'Managercode '+ convert(varchar,@cd) + ' processed'

FETCH NEXT FROM mancd INTO @cd
END
close mancd
deallocate mancd

Too old to Rock'n'Roll too young to die.

Neftoma
Starting Member

12 Posts

Posted - 2012-09-06 : 12:55:19

Cool!

Exactly what I needed! Thank you sooo much!

quote:

Originally posted by webfred

maybe this:

DECLARE mancd CURSOR FOR select distinct managercode from dbo.S12hchanges2
declare @cd int
declare @sql varchar(max)

OPEN mancd
FETCH NEXT FROM mancd INTO @cd


WHILE @@FETCH_STATUS = 0

begin
begin transaction 


set @sql = 'SELECT distinct datepart(qq,trd.tradedate) as q, YEAR(trd.tradedate) as y, trd.clientcode,trd.clientmgrcode,m.CRSP_permno,
SUM(trd.Side*trd.Volume) over (Partition by trd.clientcode,trd.clientmgrcode,m.CRSP_permno, datepart(quarter,trd.tradedate), YEAR(trd.tradedate) )as ANCchange
into table'+convert(varchar(10),@cd)+
' from aetd.dbo.TRD_EQ_US_MINI trd
inner join dbo.ANC_CRSP_stock_match m on m.ANCERNO_cusip=trd.cusip and m.Y=datepart(year,trd.tradedate) and m.m=datepart(month,trd.tradedate)
where managercode = '+convert(varchar(10),@cd)

exec (@sql)

commit
print 'Managercode '+ convert(varchar,@cd) + ' processed'

FETCH NEXT FROM mancd INTO @cd
END
close mancd
deallocate mancd

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources