Add a temp unique ID column to a view?

Question

Add a temp unique ID column to a view?

ericb1

SSCrazy

Points: 2354
More actions
May 1, 2008 at 12:02 pm

#71498

Any help is appreciated. Using sql2000, I have a Stored Procedure that someone gave me with 9 unions in it. I placed this stored procedure into a View. Now the .net module I want to view this through requires a unique key column to display the data.
Is there a way to add a uniqueID column to a view where it's just adding an ID field when the view is generated and I don't really have to add it to the stored procedure itself? Any help is appreciated.

Viewing 15 posts - 1 through 15 (of 17 total)

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

No way to do it in a view in SQL Server 2000 and actually have some performance left. How many rows are you talking about?

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

ericb1 SSCrazy Points: 2354 More actions · Answer 2

Thanks for the reply. It only returns about 25 rows, it's a summary of many different tables, but sql returns it in a secord or two.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

In SQL Server 2000, the absolute fastest way would be to insert the results of the query on the view into a temp table with an IDENTITY column and read from that.

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

ericb1 SSCrazy Points: 2354 More actions · Answer 4

Ok, thanks for the reply! Are there any examples out there that I can look at to figure this out? Also, I do have SQL Server 2005 if there's an easier way to do it in 2005, I just wasn't using it for this view.

Thanks again for everyone's help!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

In 2005 - you can emulate a uniqueID with the ROW_NUMBER() predicate.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

ericb1 SSCrazy Points: 2354 More actions · Answer 6

I'm trying to do it in sql2000. So I've created my temp table, then the rest of the SQL is just a bunch of union all joins:

CREATE TABLE #temptable

(ID int IDENTITY,

col1 varchar,

col2 varchar,

col3 varchar,

col4 varchar,

col5 varchar,

col6 varchar)

insert into #temptable

SELECT DISTINCT ...

FROM ...

UNION ALL

insert into #temptable

SELECT ...

FROM ...

WHERE ...

UNION ALL

insert into #temptable

SELECT ...

FROM ...

WHERE ...

But I get an error "Incorrect syntax near the keyword 'insert'." on each of my unions. Am I doing this temp table wrong? Any help is appreciated, thanks!

ericb1 SSCrazy Points: 2354 More actions · Answer 7

thanks for everyone's help. I got it to execute in a stored procedure:

CREATE TABLE #temptable

(ID int IDENTITY,

col1 varchar(100),

col2 varchar(100),

col3 varchar(100),

col4 varchar(100),

col5 varchar(100),

col6 varchar(100))

INSERT into #temptable

EXEC original_sp

Select *

From #temptable

Drop Table #temptable

But I'm using a DNN module that needs these results in a view, it cannot access a stored procedure directly. Is there a way to call this or put this into a view?

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 8

...requires a unique key column to display the data....

Keep in mind you'll not be able to use that key column to go back to the db !

Isn't a combination of join-key-values delivering a unique combination somehow ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

ericb1 SSCrazy Points: 2354 More actions · Answer 9

I don't need to get back to the db, the id column is just 1 through 44, it's only there because my module needs a unique ID column to display the results.

thanks!

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 10

Post what you have for the view already, please.

Strange that something should require a view to work...

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

ericb1 SSCrazy Points: 2354 More actions · Answer 11

Thanks for the help. This is the sql that puts the stored procedure into a temp table with a unique ID:

CREATE TABLE #trustgoals

(ID int IDENTITY,

Trust varchar(100),

InitialReview varchar(100),

ReReview varchar(100),

TotalReview varchar(100),

TotalQAApproval varchar(100),

TotalProcessed varchar(100))

INSERT into #trustgoals

EXEC get_total_numbers_Total

Select ID, Trust, InitialReview, ReReview, TotalReview, TotalQAApproval, TotalProcessed

From #trustgoals

Drop Table #trustgoals

The results is 7 columns and 44 rows.

I need to use a view because this is a DNN module that uses filters to display the results, so it cannot execute a stored procedure, and it must have a unique ID for paging and sorting the results. So I've got the results into SQL that I need, but somehow have to display these results in a view.

seems like executing a sp in a view should be easier than it looks. Thanks again for your help.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 12

If you just need a column to be a unique identifier, then you could just add this to your view:

Select

MyUniqueID = newid(),

…Rest of columns…

From

MyView

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

You are probably going to have to use OPENQUERY or OPENROWSET for this.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 14

ebarsamian (5/2/2008)
Thanks for the help. This is the sql that puts the stored procedure into a temp table with a unique ID:
CREATE TABLE #trustgoals
(ID int IDENTITY,
Trust varchar(100),
InitialReview varchar(100),
ReReview varchar(100),
TotalReview varchar(100),
TotalQAApproval varchar(100),
TotalProcessed varchar(100))
INSERT into #trustgoals
EXEC get_total_numbers_Total
Select ID, Trust, InitialReview, ReReview, TotalReview, TotalQAApproval, TotalProcessed
From #trustgoals
Drop Table #trustgoals
The results is 7 columns and 44 rows.
I need to use a view because this is a DNN module that uses filters to display the results, so it cannot execute a stored procedure, and it must have a unique ID for paging and sorting the results. So I've got the results into SQL that I need, but somehow have to display these results in a view.
seems like executing a sp in a view should be easier than it looks. Thanks again for your help.

I could be wrong, but I'm pretty sure that we can't use EXEC in a view... can I see the code for the proc you call "EXEC get_total_numbers_Total", please.

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