Stored Proc; Cursor and temp table.

If I have a cursor, and I'm reading in data, and then inserting some of the columns into a temp table, is there a way to do this without reading the columns into local DECLAREd variables?     Is there a way to tell INSERT to use columns directly from the cursor?
LVL 1
HLRosenbergerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
"Is there a way to tell INSERT to use columns directly from the cursor? " - why not just INSERT them directly into the #temp table from the SELECT?

You can either(recommended):

CREATE TABLE #temp(col1 int,col2 int, col3 int, ...)

INSERT INTO #temp SELECT....

OR.....

SELECT col1, col2, col3, ...
INTO #temp
FROM....
HLRosenbergerAuthor Commented:
dont both of these method required that the select return the same number of columns as the temp table?  

This is not the case.  Can I do this?
lcohanDatabase AnalystCommented:
"select return the same number of columns as the temp table"

I'm not 100% sure what exactly you need to achieve and don't quite understand why "This is not the case"...Is the cursor used for anything else?
If yes then you can just "... tell INSERT to use columns directly from the cursor? " by inserting only those columns in the #temp table from that same SELECT you use in the cursor then populate/use your cursor in a separate statement and after the temp table was populated.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Leo TorresSQL DeveloperCommented:
If you can create a temp table some how that will have all the columns you need you can then run this to get the columns in your table

SELECT Column_Name 
FROM tempdb.INFORMATION_SCHEMA.COLUMNS 
where table_name like '%#bedtable%'-- @tempobj 
order by table_name

Open in new window

HLRosenbergerAuthor Commented:
I have two cursors.  one to populate X number of columns in the temp table and the second to populate Y number of columns.  If I'm insertinf a record into a temp table that say has 10 columns, but only 4 of those columns come from the first cursor, how do I do this?       I can't get the syntax correct.
Leo TorresSQL DeveloperCommented:
In order to do what your asking above you need to create a dynamic Insert becasue you dont know the number of columns you will need the cursor to iterate thru all possible columns

Declare @str varchar(max), Col1 varchar(200)

Set @str = 'Insert into dbo.MyTable('

Cursor

Set @str = @str +','+ @Col1+'$'

End Cursor

Set @str = Replace(@str,'(,','')
Set @str = @str+'EndStop'
Set @str = Replace(@str,'$EndStop', ')' )
Set @str = Replace(@str,'$','')

Select @str

Exec sp_executesql @str

Open in new window



This is pseudo you have to create correct cursor the SQL should be fine
HLRosenbergerAuthor Commented:
but I do know the number of columns .   With SQL can do this:

insert into mytable (number) values(2)

where mytable can have any number of columns.  This is what I want to do, but in a proc.  But if I try to specify a list of column names (shown below), I get an error;   "Invalid column name 'HomeID'"  


INSERT INTO #tmpTable (HomeID, HomeName, HomeAddress, HomeAgency, HomeStatus)
HLRosenbergerAuthor Commented:
here's my temp table:

CREATE TABLE #tmpTable
(
      HomeID bigint,
      HomeName varchar(100),
      HomeAddress varchar(200),
      HomeAgency  varchar(100),
      HomeStatus varchar(20),
      Bed1Number bigint,
      Bed1Status varchar(20),
      Bed2Number bigint,
      Bed2Status varchar(20),
      Bed3Number bigint,
      Bed3Status varchar(20),
      Bed4Number bigint,
      Bed4Status varchar(20),
      Bed5Number bigint,
      Bed5Status varchar(20),
      Bed6Number bigint,
      Bed6Status varchar(20)
)
Leo TorresSQL DeveloperCommented:
This worked try it
IF OBJECT_ID('tempdb..#tmp') Is Not NULL
	Drop table #tmp

Create table #tmp(
HomeID varchar(2)
, HomeName varchar(2)
 , HomeAddress varchar(2)
, HomeAgency varchar(2)
, HomeStatus varchar(2) )

Insert into #tmp(HomeID,HomeName,HomeAddress,HomeAgency,HomeStatus)
Select 1,1,1,1,1

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HLRosenbergerAuthor Commented:
right, but your number of values you are inserting is the same as the number of columns in the table; that's not my case.  I have 17 columns in my table, and I want to insert a record where only 5 of the columns have data; the other 12 are to be set to NULL initially, and then populate via an UPDATE later on in the proc.
HLRosenbergerAuthor Commented:
Thanks for your help.  The problem was that the temp table I defined needed to be dropped.   Seems like it was keeping around an older copy of my temp table.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.