populating dynamically created table
-
I have created a table dynamically. I now need a way to populate the columns. I just don't know how many columns there are until after the table is created. Does anyone know of a way to dynamically insert data into a variably sized temp table?
Any ideas welcome!!!
Thanks,
Eva
-
Interesting.
How comes you don't know your tables fields?
_/_/_/ paramind _/_/_/ -
Because the table is being created dynamically. No table exists until a cursor is executed and the table is defined.
-
While executing that cursor you could assemble a retrieval statement at the same time
_/_/_/ paramind _/_/_/ -
Could you give me a brief example of what you mean?
-
surely. can you post the cursor?
_/_/_/ paramind _/_/_/ -
Dingbat me realized after posting this what you meant and OF COURSE that was the way to do it 🙂
I added the insert for each time I create a column. It works very nicely except that SQL Server wants to run my initial insert at the end again so I am getting an error. Any idea of how to stop that?
declare @armid bigint
set @armid = 2
CREATE TABLE #Proc (AddOn varchar(50), AddOnID int)
INSERT #Proc
select distinct pao.ProcedureAddOn, apao.ProcedureAddOnID
from tb_ArmProcedureAddOn apao
inner join tb_ProcedureAddOn pao on pao.ProcedureAddOnID = apao.ProcedureAddOnID
inner join tb_ProtocolArmProcedure pap on pap.ProtocolArmProcedureID = apao.ProtocolArmProcedureID
where pap.ProtocolArmID = @armid
order by pao.ProcedureAddOn
DECLARE @AddOn int
DECLARE @proc varchar(50)
DECLARE @procid bigint
declare proc_cur cursor
for
select ProcedureName, ProtocolArmProcedureID from tb_ProtocolArmProcedure pap
where pap.ProtocolArmID = @armid
order by sortorder
open proc_cur
fetch proc_cur into @proc, @procid
While @@fetch_status = 0
begin
exec('alter table #Proc add [' + @proc + '] smallmoney NULL')
exec('update #Proc set [' + @proc + '] =
((select (charge + (charge * (overhead/100))) from tb_ArmProcedureAddOn apao
where apao.ProcedureAddOnID = #Proc.AddOnID
and apao.ProtocolArmProcedureID = ' + @procid + ')*
(select count(ArmProcedureStageID) from tb_ArmProcedureStage aps
where aps.ProtocolArmProcedureID = ' + @procid + '))')
fetch proc_cur into @proc, @procid
end
close proc_cur
deallocate proc_cur
select * from #Proc
DROP TABLE #Proc
It runs the whole thing wonderfully and then trys to run the very first insert again. Of course, that fails since now the number of columns are more than 2. If I allow the error to show up in Query Analyzer and then select * from #Proc is works WONDERFULLY! How do I make it not try the initial insert again???
Thank you!!!!
Eva
-
I got it. I altered that first insert to be a select...into statement and it executes BEAUTIFULLY!!!
-
Dear Eva. Please use a bigger font size next time - I was looking for my glasses since two hours, instead of answering your question
_/_/_/ paramind _/_/_/ -
haha, it was small text....I'd recommend the magic of ctrl + scroll 😉
-
does that work for little brain sizes too?
_/_/_/ paramind _/_/_/ -
I don't personally like using Select Into because if one of your columns has no data in it (all nulls) you will get an error stating that it can not create a column of size 0.
You can generate a simple insert query by querying the Information_Schema.Columns view.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply