Creating An Identity Column In A View

Mar 25, 2004

I need a view that will:
1) select the distinct values in a column (the easy part)
2) assign the equivalent of an identity column to each row containing the distinct value it returns.

For example TableA contains 25 records but one of the columns only contains three unique values (say, 'A','B', & 'C').

This is what I want my view to return:

1 A
2 B
3 C

In other words, I need the view to assign the 1, 2 and 3 to the individual rows.

Thanks in advance for any pointers on this one.

View 4 Replies


ADVERTISEMENT

How To Add An Identity PK Column To A View

Nov 16, 2007

For an ASP.net project, I have had a DropDownList with a static ArrayList.The ArrayList will be defined from a View, where there is no Identity PK. 
I also have used cbxDropDownListName.SelectedIndex to add new data toa table, where an Indetity PK is used to reference the ArrayList. 
 I am wondering how can I add an identity PK to my view?
TIA,Jeffrey

View 1 Replies View Related

Add IDENTITY Column In Runtime In A View...

Jul 20, 2005

Dear All,I'm facing a problem with creating a view.I have a table withfollowing descriptionTab1------------------------------------Col1 col2---- -----Val1 TomVal2 CatVal3 JackVal4 JimNow I'm trying to create a view with following information fromTab1.The output should beTab1View--------------------------------------Col1 Col2 Col3---- ---- -----1 Val1 Tom2 Val2 Cat3 Val3 Jack4 Val4 Jim---------------------------------------So the create view statement add this column dynamically in thisresult set.If any body has any doubt on this query please let me inform.RegardsArijit Chatterjee

View 1 Replies View Related

Issue With Identity Column In A View

Dec 28, 2006

Thanks a lot, Greg!

Well, I have another question.

In SQL-2005, till SP2 version, there is a bug, dialed with treating an identity column in a view as an ordinary int column, when the compatibility verison of the DB is set to 80: http://support.microsoft.com/kb/920974

In SQL Server 2005 Express Edition CTP (since version 9.00.3027), which is available to download from Microsoft site, this bug was fixed.

Please tell if you know, how can I get an evaluation copy of any other edition of this version, where merge publication feature could be tested?

View 1 Replies View Related

SPROC Probs Creating A Non-identity Number Column

Jun 26, 2006

Hi. I am trying to figure out the code for sorting a manual (non-identity) number column in my table. the purpose is to
show the user's pictures in perfect order (1,2,3,4,5,6...).

The Jist of my problem... When a user first inserts six pictures, he gets:

|1|
|2|
|3|
|4|
|5|
|6|

All is good. But, say he deletes picture |3|. Now the list order looks like this:

|1|
|2|
<- |3| is removed
|4|
|5|
|6|

And, then he inserts two more pictures, now he his this:

|1|
|2|

|4|
|5|
|6|
|7| <- |7| & |8| are added
|8|

What i want to acheive is a "reshuffling" of the number order every time a picture is removed. So, when |3| is removed, |4| becomes |3|, |5| becomes |4| and so on. There should never be a gap in the order.

I am new to stored procedures, and have been trying to figure this out. Below is my guesswork:


Code:


ALTER PROCEDURE dbo.sp_NewPersonalPic

(
@photo_name VARCHAR(50) = NULL,
@photo_location VARCHAR(100) = NULL,
@photo_size VARCHAR(50) = NULL,
@user_name VARCHAR(50) = NULL,
@photo_caption VARCHAR(150) = NULL,
@photo_default BIT = NULL,
@photo_private BIT = NULL,
@photo_number INTEGER = NULL,
@photo_date DATETIME = NULL
)

AS

BEGIN
SELECT @photo_date = CONVERT(DATETIME,convert(char(26), getdate(), 109))
END

BEGIN
SET @photo_number = 1
SELECT

@photo_number = (
SELECT COUNT(*)
FROM dbo.PersonalPhotos b
WHERE
a.photo_date < b.photo_date
)
FROM
dbo.PersonalPhotos a
ORDER BY
a.photo_date
END

BEGIN



My thinking is that it would be a safe bet to use the "photo_date" column as a litmus for my "photo_number" column (ie, the most recent record inserted by the user will always be at a later date than the previously inserted record). So:

photo_number photo_date
|1| 2006-06-26 21:43:36.653
|2| 2006-06-26 21:43:50.000
|3| 2006-06-26 21:45:25.217
|4| 2006-06-26 21:45:33.763
|5| 2006-06-26 22:39:42.670
|6| 2006-06-26 22:39:49.200

If |3| is removed above, the numbers are reordered based on the time of entry sequence.

Any suggestions on how to acheive this in my stored procedure? Currenly, i get the correct order, but it goes crazy when i delete and add.

Thanks and sorry for the verbose post.

View 5 Replies View Related

Creating A View With A New Column

Jan 31, 2014

I'm using SQL Server 2008 to solve the following exercise.

I have three Tables:
Table CUSTOMER with columns: CustomerID,Company,ContactName,Phone
Table ORDER with columns: OrderID,OrderDate,ShippedDate,ShipperID,Freight,CustomerID
Table Shipper with columns:ShipperID,Company,Phone

I need to Create a view listing the shipper and the number of unshipped orders. I have been trying to use joins or possibly subqueries, but I'm not getting a correct result for a ShipperID with NO unshipped orders.My view should looks like: ShipperID, Company, Phone, UnshippedOrders

View 4 Replies View Related

Problem Creating View With DISTINCT Column

Oct 5, 2005

Hi Guys

I'm trying to create a view in SQL Server to display only one copy of all records in a table, where some of the records are exact matches to other records (except for an ID autoincrement field)

I have the following code that displays distinct titles:

sql Code:






Original
- sql Code




SELECT DISTINCT DocTitle
FROM dbo.TBL_TABLE_NAME






SELECT DISTINCT DocTitleFROM         dbo.TBL_TABLE_NAME



But when i use this code (adding one field to query), it displays all the duplicate records also, which I do not want:

sql Code:






Original
- sql Code




SELECT DISTINCT DocTitle, ID
FROM dbo.TBL_TABLE_NAME






SELECT DISTINCT DocTitle, IDFROM         dbo.TBL_TABLE_NAME




I want to get the distinct results for DocTitle, with 5 other columns added to the query so they can be displayed in a web page list.
The query I want is something like this, but I can not get it to work:

sql Code:






Original
- sql Code




SELECT DISTINCT DocTitle, ID, FirstName, LastName, Company, DocDescription, DocFile
FROM dbo.TBL_TABLE_NAME
ORDER BY DocTitle






SELECT DISTINCT DocTitle, ID, FirstName, LastName, Company, DocDescription, DocFileFROM         dbo.TBL_TABLE_NAMEORDER BY DocTitle



I know this query works from an MS Access DB, but I need it to work from SQL Server.

I am using SQL Server 2000

Any help is greatly appreciated.

Alex

View 1 Replies View Related

Problem In Using Sqlbulkcopy To Insert Data From Datatable(no Identity Column) Into Sql Server Table Having Identity Column

Jun 19, 2008

Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun

View 6 Replies View Related

Creating Index On A View To Prevent Multiple Not Null Values - Indexed View?

Jul 23, 2005

I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?

View 3 Replies View Related

Calling A Stored Procedure From A View OR Creating A #tempTable In A View

Aug 24, 2007

Hi guys 'n gals,

I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....

I tried:

CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc


and unfortunately, it does not let this run.

Anybody able to help me out please?

Cheers!

View 3 Replies View Related

Transact SQL :: Alter Non Identity Column To Identity Column

Aug 12, 2009

when i alter non identity column to identity column using this Query alter table testid alter column test int identity(1,1) then i got this error message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'identity'.

View 2 Replies View Related

T-SQL (SS2K8) :: How To Update Identity Column With Identity Value

Jan 25, 2015

I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.

Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?

View 6 Replies View Related

Identity...I Need To Get The Last (or Highest Number In Identity Column)...

Sep 19, 2005

Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint

View 2 Replies View Related

How To Use Identity On Non-identity Column (with Concurrence)

Aug 1, 2014

I'm working with a third-party database (SQL Server 2005) and the problem here is the following:

- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).

- The PK of T is an Integer.

Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.

Considering that I can't change the PK, what is the best approach to solve this problem?

To sum up:

* I need to have processes in parallel inserting on T

* I can't change anything on T

* The PK is NOT an Identity

View 4 Replies View Related

Creating Identity Thru Alter Ste.

Sep 14, 2000

I am not able to create identity on a existing column using ALTER statement.
Can anybody help on this issue ?

View 2 Replies View Related

How To Refer A Column When The Referencing Column Is An Identity Column

Oct 16, 2006

Hi all,

The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..

i cannot implement this constraint, it throws the error when i execute the below Alter query,

ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id)
REFERENCES parent_table (parent_id) ON DELETE CASCADE

the error thrown is :
Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT
fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE
CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be
created where the referencing column 'child_table.child_id' is an identity column.

any workarounds for this ?

View 3 Replies View Related

TSQL - Using ALTER TABLE - ALTER COLUMN To Modify Column Type / Set Identity Column

Sep 7, 2007

Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?

I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1

Thanks in advance,
Aldo.

I have tried the code below, but getting syntax error...



ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;


I have also tried:

ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;







View 18 Replies View Related

SQL 2012 :: What Account To Use For Identity When Creating Credential And To Use As Job Owner

Aug 12, 2014

We are running SQL Server 2012 on Windows 2008 Server. I created a credential with a proxy account. In creating the credential, it asked for an Indentity and Secret. I used my windows login and password. Now, I have tested the credential and proxy account by executing a Job which calls a SSIS Package. What is the 'best practice' to use when creating a credential? Should the credential be created with another windows login, created with the same abilities as my windows login, with a non-expiring password? Should that new windows login be used as the owner of my job with the Agent?

View 8 Replies View Related

Creating A View Help

Aug 6, 2007

i have a table which has 2 columns 1 'report'  2 'part'
now in my 'report' cloumn i have # with 6 digits ex. '111111' and 'part' has '1, 2, 3, 4,..to 50'
i want to create a view that will put them together in format like this:
1111110001
1111110002
1111110003 .. and on
it needs to be in 10 digits.
is there anyway i can create a View or may be a column in the table which can create the #'s in this format.

View 2 Replies View Related

Creating A View

Dec 5, 2005

What are some possible purposes of creating a view and how can it be used to reinforce data security. What description of circumstances can be used for a view to save re-programming?

View 1 Replies View Related

Help With Creating View

Apr 16, 2008

Hi All,

I have a sql command with temporary tables, but whenever I am trying to create a view with temporary tables
I am getting an error as below:

"Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables."


Please anybody let me know is it possible to create a view with temporary tables in SQL Server 2005.If not, then is their any way how I can create a view with temporary tables.



Thank You

View 2 Replies View Related

Help With Creating View

Apr 16, 2008

Hi All,

I have a sql command with temporary tables, but whenever I am trying to create a view with temporary tables
I am getting an error as below:

"Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables."


Please anybody let me know is it possible to create a view with temporary tables in SQL Server 2005.If not, then is their any way how I can create a view with temporary tables.



Thank You

View 2 Replies View Related

Need Help With Creating View

Jul 23, 2005

I need to create a view that scores a research assessment. So I havequestions 1 through 10, but the problem is that Q3 and Q5-10 have to berecoded so that if the response is 3, it becomes 0, 2=1, 1=2 and 0=3.So this is what I have. I keep getting an error message that there is"incorrect syntax near the keyword THEN". I don't know which "THEN" itis referring to (or all of them)?? HELP! I am new to this.CREATE VIEW name ASSELECT ID, DATE, TOTAL=Q1+Q2+CASE WHEN (Q3=0 THEN 3 WHEN Q3=1 THEN 2 WHEN Q3=2 THEN 1 WHEN Q3=3 THEN0)+Q4+CASE WHEN (Q5=0 THEN 3 WHEN Q5=1 THEN 2 WHEN Q5=2 THEN 1 WHEN Q5=3 THEN0)+CASE WHEN (Q6=0 THEN 3 WHEN Q6=1 THEN 2 WHEN Q6=2 THEN 1 WHEN Q6=3 THEN0)+CASE WHEN (Q7=0 THEN 3 WHEN Q7=1 THEN 2 WHEN Q7=2 THEN 1 WHEN Q7=3 THEN0)+CASE WHEN (Q8=0 THEN 3 WHEN Q8=1 THEN 2 WHEN Q8=2 THEN 1 WHEN Q8=3 THEN0)+CASE WHEN (Q9=0 THEN 3 WHEN Q9=1 THEN 2 WHEN Q9=2 THEN 1 WHEN Q9=3 THEN0)+CASE WHEN (Q10=0 THEN 3 WHEN Q10=1 THEN 2 WHEN Q10=2 THEN 1 WHEN Q10=3THEN 0) ENDFROM tablename

View 2 Replies View Related

Help With Creating View

Apr 16, 2008



Hi All,


I have a sql command with temporary tables, but whenever I am trying to create a view with temporary tables
I am getting an error as below:

"Views or functions are not allowed on temporary tables. Table names that begin with €˜#€™ denote temporary tables."


Please anybody let me know is it possible to create a view with temporary tables in SQL Server 2005.If not, then is their any way how I can create a view with temporary tables.



Thank You

View 3 Replies View Related

Question About Creating A View.

May 19, 2007

I currently have a website with a page that displays the flags/ keys of the entire roster of guilded characters.  Some more background is that I run a website for my Everquest guild, users can log in , create characters , and update their flags / keys.
 There are 4 associated tables that are used in displaying the flags.
 the Characters , Flags, Expansion, and Char_Flags tables.   The char_flags table consists of 2 foreign keys and a bit field for enabled / disabled. 
Char_flags = char_fk, flag_fk, enabled (bit)
The select statement I'm currently using to get information is as follows.
SELECT Expansion.ExpansionName, Flags.Flag_Name, Characters.Char_Name, char_flags.enabled FROM char_flags INNER JOIN Flags ON char_flags.Flag_FK = Flags.Flag_PK INNER JOIN Expansion ON Flags.Expansion_FK = Expansion.ExpansionPK INNER JOIN Characters ON char_flags.Char_FK = Characters.Char_PK WHERE (Expansion.ExpansionPK = @expansion)
 That displays the information in a format like
Expansion Name,  FlagName, CharacterName, Enabled.   And there are usually 10 -15 flags per expansion so this lists the expansion 10 times, each flag name, then the character name 10-15 times, and wether or not the flag is enabled.
 
I would like to display the information a little differently and I'm not sure how to.   I would like to display the inormation like this
             Flag Name 1     Flag Name 2,  Flag name 3, Flag Name 4, etc...
Char 1          X                                            X                  X
Char2                                  X                     X                  X
Char 3         X                      X                      X                 X
Char 4
Char 5
etc
where the characters make up the first column, the flag names make up the first row(headers) and wether or not the flag is enabled is in the column under the corresponding flag.
This way the name of the flag, and the character name are only displayed one time instead of the flags and character names being repeated over and over.  
If anyone can help me on how to display the data I would appreciate it.   Here is a link to the page to show how it looks now if it helps www.shattereddestinies.net/flagstest.aspx
Thanks
Brad Fortner

View 3 Replies View Related

Problems Creating A View

Jan 19, 2005

I have a query on one of my pages that is just too large to keep in the page, so I need to reference a stored view in sql. I'm kind of new to this, so I'm having trouble getting the syntax right. The query is just a simple select statement using the results of several textboxes as parameters. I know how to do the query inside an asp.net page, but when I move it to sql, I don't know how to reference the textbox value i.e. @textbox. Here's what I have so far:
USE [Maindb]
GO
CREATE VIEW [tblMain_view] (@textbox nvarchar(30)) ????
AS SELECT dbo.tblMain.Field1, ....
FROM dbo.tblMain
WHERE dbo.tblMain.Field1 = @textbox and ....


First of all, I know that where I declare @textbox is wrong, so where is the right place to declare it? Also, how do I reference the view from the webpage and do I still use:
cmd.SelectCommand.Parameters.Add . . .
in the page to establish the value. Anyone know a good tutorial on this. All the ones I've found were either in C# or didn't really apply. I need to know how to do this in VB. Thanks

View 1 Replies View Related

Creating View On Another Database

May 23, 2006

Hi I have two SQL server 2000 machine in my corporateHow to create a SQL View if the database table is located on another SQL server?Example :I am currently working at SQL Server "S01", but need to create a sql view to table customer on "Marketing" database located on SQL Server "S02"if the table is reside on the same machine then it's simple for me, but this customer table is not located on "S01" but located on different machine "S02"how to do this? any tutorial?thanks a lotYonscun

View 2 Replies View Related

Creating View Through Sproc

Jun 15, 2001

Hi guys,
I am trying to create a view through SP
as follows, but I am getting as error as Invalid syntax near view.
Please let me know where I am doing wrong.

Thanks,Rau
CREATE PROCEDURE PurgeReport
@intJob as int
AS
if @intjob=1
begin
CREATE VIEW Purge AS SELECT Btch_id AS Batch_ID, DLN, Process,End_DLN, Job,
Tcode
FROM Batch_HDR
end

View 2 Replies View Related

Creating Indexes On View

Nov 9, 2000

Hi

I want to know if we can create indexes on Views in SQL 7.0. If yes, then how

Thanks

alsi

View 1 Replies View Related

Help Needed For Creating View

Sep 2, 2004

Hi

Need help in writing a query. I have a table contains details about an item. Each item belongs to a group. Items have different status. If any one of the item in a group is not "Completed", then the itemgroup is in state incomplete. if all the item under the group is completed then the item group itself is completed. Now I need to create a view with itemgroup and itemstatus.
Suppose I have five records

item itemgroup status
1 1 complete
2 1 Xyz
3 2 complete
4 2 complete
5 2 complete

my view should be

itemgroup status
1 incomplete
2 complete

All the Statuses are not predefined...they get added as and when required........

Right now I am using a function. But dont want to use it for performance reasons. Would appriciate any help.


Thanks

View 6 Replies View Related

Creating A View Using A Union?

Jan 14, 2015

I am looking to create a new view by combining two tables, but i would like to create a new column in the view that identifies what table the data came from. For example I have a Table A and Table B, using a union i combined the two table but i want a new column titled Source which could be populated by A or B.

This is my code so far:

Create View Table_AB As
Select *From Table_A
Union All
Select*From Table_B

View 1 Replies View Related

Creating Columns In A View

May 10, 2006

Hi im trying to create a view that creates another column for a table and then adds the value but i cant get it could sum1 help

Create View BONUS_PAYOUTS As

Alter Table Employee
Add EMP_BONUS Decimal(6,2)

Update Employee
Set EMP_BONUS = EMP_PCT / 500000

Select Employee.EMP_LNAME, Employee.EMP_FNAME, Employee.EMP_PCT, Position.POS_TITLE, Employee.EMP_BONUS
From Employee
Inner Join Position On Employee.POS_ID = Position.POS_ID

View 5 Replies View Related

Error Creating View

Jul 25, 2007

i tried running the statement :

(1)
create view qcostcentre
as
select * from dbo.costcentre.dtblcostcentre

>ERR: Msg 208, Level 16, State 1, Procedure qcostcentre, Line 4
Invalid object name 'dbo.costcentre.dtblcostcentre'.

(2)
create view qcostcentre
as
select * from costcentre.pcusers.dbo.dtblcostcentre

>ERR: Msg 7202, Level 11, State 2, Procedure qcostcentre, Line 4
Could not find server 'costcentre' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.


note: pcusers is a user of 2 database (costcentre & datamaster) with dbo_datareader owned & role schema .

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved