We help IT Professionals succeed at work.

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?
Comment
Watch Question

lcohanDatabase Analyst

Commented:
"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....

Author

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 Analyst

Commented:
"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.

Commented:
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

Author

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.

Commented:
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

Author

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)

Author

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)
)
Commented:
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

Author

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.

Author

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.