tempTable w/ identity column
-
hi!
i have an unsolved problem: i want to use 'select distinct & into' to insert datas from a perm table into a temp table. this is simple. but i have to know the rownumber of each row, couse i want to work w/ that in a while cicle.
i had an idea to alter this tempTable and add an identity column.
it works still i use 'select * from tempTable' (it means that i can see the rownumbers in the column 'id') but when i want to use 'select * from tempTable where id= 1' sqlserver says that id is an invalid column name.
and i don't understand this situation.
can u help or explain me?
thx
-
SELECT IDENTITY(INT,1,1) AS RowNum,*
INTO #yourtemptable
FROM #yourpermtable
Don't forget to add a primary key for speed and accuracy...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
>> alter this tempTable and add an identity column.
>> sqlserver says that id is an invalid column name.
what is the column name for the identity column ? Looks like you did not named it id
create table #tempTable
(
id int identity(1, 1),
....
)
-
i tried with more column names. i called it ID, Counter, RowNumber, etc, but got the same result at each time.
-
Best idea would be to post the SQL you're using. Then we might be able to see where the error is.
--------------------
Colt 45 - the original point and click interface
-
[Edit] Ugh. I read the question all wrong, original reply removed.
Just be sure to read this if ordering of rows is involved:
http://support.microsoft.com/default.aspx?scid=kb;en-us;273586
-
thanks PW. this article is very useable, but i think, the problem is not that.
i try to create another column in a tempTable and write a select query for this column, but the result was the same: this created column is an invalid column name.
here is the script:
select Col1+Col2+Col3 AS NewCol, *
into tempTable
from permTable
select NewCol from tempTable
SQLServer says: MSG 207 Invalid column name 'NewCol'
is there any nameconversation for temptables' columns which i dont know or SQLServer doesn't allow to query virtual columns?
-
Hello,
you didn't mention whether you are working in QA or launch the scripts some other way... maybe your SQL fails, but you don't see the error message? If you run SELECT * FROM #temptable, you should see whether the column is there or not, and what data are in the table.
Make sure to include existence check and conditional drop before you ever try to use SELECT INTO, and preferably avoid * in the select when inserting - it works, but it can easily become a problem if the table structure changes.. and when you name columns explicitly, you will see any possible duplicities in column names:
IF object_id('tempdb..#temptable) IS NOT NULL DROP TABLE #temptable
select Col1, Col2, Col3, (Col1+Col2+Col3) AS NewCol
into #temptable
from permTable
SELECT * FROM #temptable
-
hi!
i use it in QA. so i can see that this NewCol is one of the #tempTable's. when i use this:
select * from #tempTable
but when i use the next, i get the message about the invalid column name:
select NewCol from #tempTable
is there any idea?
-
Airolg Icsadjag , I think you may have over simplified the code you posted. What is permTable? What is it's structure? Where do Col1, Col2, Col3 come from? Also, the example code you posted doesn't seem to have anything to do with an id column.
I tried the following in the pubs database and while not exactly what I'd suggest you'd be doing, it seems to work fine,
select lowqty+highqty AS NewCol, * into tempTable from dbo.discounts
select NewCol from tempTable
--------------------
Colt 45 - the original point and click interface -
I CANT BELIVE IT!
your script works in pubs! so i DON'T understand what's wrong with my script. it sholud have to do the same like yours. but it doesnt....
-
So I suggest you post your script without modifcations so we can track down where the error is.
--------------------
Colt 45 - the original point and click interface -
I SOLVED ID!
... or i think, i solved it, 'couse it works!
i changed the DB's collation to the same as SQL Server's collation. and it's okay.
what do you think about it?
thx for all answers
G -
Because the Temp DB is created each time SQL server is started it takes its collation from the server (or, more correctly, from the 'model' db). If you have databases installed from other servers, with different collations, then you will get collation problems when using temp DB (most commonly manifesting as developers crying 'but I`m using your database with your known working code, why do I get this stupid collation error?'
)Having said that, this is the first time I have come across the problem on column names, rather than column contents. I can only assume that the collation on the table where the column names are stored (syscolumns in the temp DB) is different from the collation of the DB you are connected to.
An interesting variation on collation problems that I`ll have to keep in mind.
"Don`t try to engage my enthusiasm, I don`t have one."(Marvin)
-
Your best bet is going to be to use an identity column in the temp table. For the initial select statement try something like:
select distinct Identity (int, 1, 1) as MyId, RowA, RowB into #MyTempTable from MyPermTable
Then on the retrieval side use:
select RowA, RowB from #MyTempTable where MyId = 1
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply