Creating a temp table with an identity.

Question

Creating a temp table with an identity.

ben.brugman

SSChampion

Points: 13369
More actions
February 1, 2021 at 4:31 pm

#3839704

Two tables, they are not completely known at design time. They have no column_names in common.
At runtime the tables are joined and the result in put in a temp (##) table. The projection (columns) is determined at run time. Depending on the function sometimes there are fields which can be used to identify a row, in other situations there are situations where rows can not or not easely be identified.
So my goal is to create a temp file with an identity, but not a create statement build at design time. Also I want to avoid complex column selection generation code. Something simple as :
SELECT A.*,B.* INTO ##C FROM A JOIN B ON X = Y
Should work, but I would like to add an identity column with this, so that each row can be uniquely identified.
An elegant not to complex solution would be helpfull. A and B could be Tables, views, temptables or combinations.
Offcourse for each situation I can come up with a solution, but I would like to have a more generic solution. Thanks.
Thanks for your time and attention.
Ben

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

you can just add row_number() to the select. Not sure about the global temp table and not knowing the columns though. Do you know how many columns, datatypes, etc???

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Or add the IDENTITY after the SELECT INTO

SELECT TOP (10) * INTO #Test FROM sys.columns c

ALTER TABLE #Test ADD SomeId INT IDENTITY(1,1)

SELECT * FROM #Test t

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ben.brugman SSChampion Points: 13369 More actions · Answer 3

Thanks,

After puzzling a bit (a lot realy), and trying some construction, during a coffee break I decided that :In these modern times, there is often a solution on line, so before spending to much time on your own solution search for an existing one. Second ask people with the question. Even formulating the question might be enough to trigger a solution. So after I made the question I implemented the following. (Solution 1). I'll show this next to the solution of Phil Parkin. (Solution 2). I also tried the solution from Mike1 but did not succeed in adding the row_number directly. (Or maybe it is similar to my construction).

-- Add a zero integer before the column list.
SELECT 0 solution1, A.*,B.* INTO ##C FROM A JOIN B ON  X = Y

-- Fill the zero with unique row_numbers;
;WITH                                                                                          -- Strange formatting sorry for that. (Too much white space).
D  AS (SELECT ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY (SELECT NULL)) id, * FROM ##C
UPDATE D SET solution1=id

--Next solution (2)
ALTER TABLE ##C ADD solution2 INT IDENTITY(1,1)

(There is no need for both solutions, but it just shows both methods, both can be used, maybe one is better in certain situations, where the other is better in other situations).

"Not sure about the global temp table and not knowing the columns though. Do you know how many columns, datatypes, etc???"

No, a lot is not known.

There are situations where an educated user has a request which is very difficult to implement in existing software. But the data can be made available very easily in for example 2 views. The standard software can do some manipulations where a JOIN might be required on the resulting 'views'. The user does not have or want access to the database and often does not have the permission to browse. The views or tables get made especially for the user to his/her request. With the user we create what he does need (and is allowed to see), with the software he can then do what he knows and wants. With this cooperation some solutions can be created fast and there is no problem with safety or security. Educated users really appreciate this cooperation and because of their involvement, they are flexible and are open to suggestions and the other way around as wel.

Sometimes some key columns are not made available to these users, but he/she can still identify problem cases. The unique numbers can sometimes be used to identify to find the database rows for this.

This description is not completely accurate. But this is what (I think) am allowed to reveal here.

Thanks for your suggestions, and the questions,

Ben

This reply was modified 3 years, 9 months ago by ben.brugman.
This reply was modified 3 years, 9 months ago by ben.brugman.

ScottPletcher SSC Guru Points: 101097 More actions · Answer 4

Or you could do this 😉

SELECT IDENTITY(int, 1, 1) AS id, A.*,B.* 
INTO ##C 
FROM A 
JOIN B ON  X = Y

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ben.brugman SSChampion Points: 13369 More actions · Answer 5

Thanks Scott,

This is what I was looking for. Remembered that something like this was possible. But it had escaped from my memory bank How to do it. (Trying all different kinds of syntaxes mostly involving Row_Number, Oops ;-( )

Thanks, I'll put this in, (Although in the end my manager will say that I hardly produced code, so my less elegant and more line solutions gives me more credit. 🙂 Yes this is rather fact of life, the best solutions look elegant, less complex and easy. But often take some time.)

All of you thanks,

Ben