Creating a temp table with an identity.

Question

Post reply

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: 11381 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: 245813 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

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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, 5 months ago by ben.brugman.
This reply was modified 3 years, 5 months ago by ben.brugman.

ScottPletcher SSC Guru Points: 100262 More actions · Answer 4

ScottPletcher

SSC Guru

Points: 100262

February 1, 2021 at 8:01 pm

#3839804

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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

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