MS SQL Server :: How To Refer A Column When The Referencing Column Is An Identity Column

SELECT Column Aliases: Refer To Alias In Another Column?

Apr 9, 2008

Using SQL Server 2000. How can I refer to one alias in another column?E.g., (this a contrived example but you get the idea)SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM dataNote how the speed alias is used in the definition of acceleration alias but this doesn't seem to work.

View 11 Replies View Related

SELECT Column Aliases: Refer To Alias In Another Column?

Apr 10, 2008

Using SQL Server 2000. How can I refer to one alias in another column?

E.g., (this a contrived example but you get the idea)

SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM data

Note how the "speed" alias is used in the definition of "acceleration" alias but this doesn't work.

View 14 Replies View Related

Is A Foreignkey Column Can Refer Two Primarykey Column From Two Different Tables?

May 13, 2008

Hi,

I need to create a table (Named as C) with a foreignkey column. That column should references with a primarykey column in table A and a primarykey column in table B. Is this possible?

Thanks in advance.

ramesh.p

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

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

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

GUID As Primay Column And Identity Column

Jan 9, 2007

Hello;
My Memebership table has Guid column as Primary key.
But I would like to add Auto numbering Identity column to this table.
Is this idea OK or it will bring some problems?
Thank you in advance for your help

View 3 Replies View Related

Alter A Column To Be The Table Identity Column

Aug 3, 2006

i have a table
table1
column1 int not null
column2 char not nul
column3 char

i want to script a change for table1 to alter column1 to be the table identity column. not primary.

View 5 Replies View Related

Making An Existing Column An Identity Column

Mar 5, 2004

Hi,

I have a column that is unique that I would like to make into an IDENTITIY column after I insert some data into it.

I tried

alter table <table_name>
alter column <col_name> int Identity (1,1)

but it fails.

Ajay

WORD4LIFE
(http://www.word4life.com)

View 2 Replies View Related

To Make An Existing Column Become An Identity Column

Jul 20, 2005

Hi(SQL Server 2000)I have an existing table (t) with a column that is NOT an identity column(t.ID), but it has manually inserted "row numbers". I want to make thiscolumn become an identity column. This column is a key field to othertables, so I want to keep the row numbers that are allready inserted.From the Query Analyzer, how do I do this?Thanks in advance!Regards,Gunnar V�yenliEDB-konsulent asNORWAY

View 3 Replies View Related

How To Bind A Column To Identity Column Of The Different Table.

Apr 2, 2007

Hi,

I have two tables table1 and new_table

Table1 has id_value column which is int and it is idenity specification is yes and identity increment is 1 .

And I have a NEW_TABLE with column name new_id which should store current id_value of Table1.

This type of functionality is requirement for my project.

I should get a current value of id_value from table1 . if I say SELECT * FROM NEW_TABLE ;

Please help me out to fix this issue

Thanks

Purnima

View 3 Replies View Related

Counter For Max Tickets - Refer To A Database Table Row/column Etc

Feb 7, 2008

I posted a thread in the Getting Started forum about how to make a counter for maximum tickets : http://forums.asp.net/t/1215258.aspx
but maybe this is a more appropriate forum for this subject.
In a school project, we are making a website for a fictional concert/festival (using Visual Studio 2005, C#). On that site users can register and order tickets.
We have access to an SQL-database, by the way, where we can create tables etc.
We want the maximum amount of tickets to be 10000 per day. The festival is supposed to last from friday to sunday.
What would be the best way to do this programatically? The counter should maybe be in an own database table?

View 3 Replies View Related

Change An Int Column To Identity Column

Dec 17, 2001

Hi, I want to change an int column (not null) to identity column. I tried
the following:

alter table myTable alter column ID int identity(10, 1) not null

But it failed with the error message:

Incorrect syntax near the keyword 'identity'.

Can someone please show me the correct statement (if it exists)>

Many thanks.

View 4 Replies View Related

GROUP BY Expressions Must Refer To Column Names That Appear In The Select List.

Jan 19, 2008

Hi,

Is there any way to group variables present in a select statement.

My code:

SELECT @var1=something, @var2=something_else
FROM ...
GROUP BY @var1

I wanted to group by 'something' hence I used a variable to assign it to. But I'm unable to group it.

Any thoughts?

Thanks,
Subha

View 1 Replies View Related

Integration Services :: Add New Column Based On Existing Column Using Derived Column Task?

Jul 28, 2015

I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".

Code:

A
B
C
D
E
F
G
H

If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".

View 4 Replies View Related

Referencing A Custom Column

Feb 27, 2008

Excuse me if my terminlogy is inaccurate, i'm a .NETer that's new to SQL.

I was wondering if it's possible to reference a column in the WHERE CLAUSE that has been customily defined in the SELECT statement

for example

select employeeID, case when JobCode = 'A' then 'Accountant'
case when JobCode = 'C' then 'Consultant'
case when JobCode = 'B' then 'Biller'
End as JobType
from Employee
where JobType is not null

This does not work, and saids JobType is an invalid column name. Basically, what I want is to display the jobtype of the employee but i also want to only display the employees that are Accountants, consultants and billers.

Is this possible and what would be the best way of doing this?

I do not seem to be about to reference JobType in the WHERE statement.

View 10 Replies View Related

Referencing A Custom Column

Feb 27, 2008

Excuse me if my terminlogy is inaccurate, i'm a .NETer that's new to SQL.

I was wondering if it's possible to reference a column in the WHERE CLAUSE that has been customily defined in the SELECT statement

for example

select employeeID, case when JobCode = 'A' then 'Accountant'
case when JobCode = 'C' then 'Consultant'
case when JobCode = 'B' then 'Biller'
End as JobType
from Employee
where JobType is not null

This does not work, and saids JobType is an invalid column name. Basically, what I want is to display the jobtype of the employee but i also want to only display the employees that are Accountants, consultants and billers.

Is this possible and what would be the best way of doing this?

I do not seem to be about to reference JobType in the WHERE statement.

View 1 Replies View Related

CHECK Constraint - Referencing Another Column

Aug 31, 2000

I receive the following error when creating a CHECK constraint that references another column. According to the good old Wrox SQL Server book, I'm using the correct syntax. Anyone have any ideas???

Thanks in advance!

Server: Msg 8141, Level 16, State 1, Line 1
Column CHECK constraint for column 'end_date' references another column, table 'Session'.

Here's an example of the script that I'm using:
CREATE TABLE Session (
session_key char(18) NOT NULL,
course_key char(18) NOT NULL,
site_key char(18) NOT NULL,
instructor_key char(18) NOT NULL,
start_date smalldatetime NULL,
end_date smalldatetime NULL
CHECK (end_date >= start_date)
)

.

View 1 Replies View Related

Referencing Column List For Foreign Key No Match

Oct 26, 2013

I'm encountering this very weird problem, so I create a staff table:

CREATE TABLE Staff (
staffNo numeric(10),
venueNo numeric(10),
name nvarchar(20),
DOB datetime,
position nvarchar(20),
salary numeric(8,2)
CONSTRAINT staff_PK PRIMARY KEY(staffNo, venueNo),
CONSTRAINT venue_FK FOREIGN KEY(venueNo) REFERENCES Venue
);

and then when I create a professional therapist table

CREATE TABLE Professional_Therapist (
staffNo numeric(10),
specialization nvarchar(20),
bonus numeric(8,2),
CONSTRAINT professional_PK PRIMARY KEY(staffNo),
CONSTRAINT staff_FK FOREIGN KEY(staffNo) REFERENCES Staff
);

It says : The number of columns in the referencing column list for foreign key 'staff_fk' does not match those of the primary key in the referenced table 'Staff'.

View 3 Replies View Related

SQL Server Table's Column Referencing Two Parent Tables.

Feb 15, 2008

Hi everybody,
I know that it is possible for one column to reference two different parent tables by defining two foreign keys on a same column, I have done it and SQL Server does give any error. But I want to know that is it advisable to define multiple foreign key on a column referncing two differnt parent table's primary key in differnt case... ? Means If Case 1 then It should reference to column1 or table1 otherwise it should reference to table2? How is it practicle...?

View 2 Replies View Related

Problem Referencing A Column In A Table Variable In A Query.

Apr 23, 2006

Hello All

I have the following problem running an sp with a table variable (sql server 2000) - the error which occurs at the end of the query is: "must declare the variable @THeader" . @THeader is the name of the variable table and the error occurs with such references as @THeader.ApplyAmt, @THeader.TransactionHeaderID, etc.

declare @THeader TABLE (
TransactionHeaderID [int] NOT NULL ,
PatientID [int] NOT NULL ,
TransactionAllocationAmount [money] NOT NULL ,
ApplyAmt [money] NULL ) - create table variable

insert into @THeader select TransactionHeaderID,PatientID,TransactionAllocationAmount,ApplyAmt from mtblTransactionHeader where PatientID = 9 - fill the table variable

UPDATE @THeader
set TransactionAllocationAmount =
(SELECT isnull(Sum(mtblTransactionAllocation.Amount),0)
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID or
mtblTransactionAllocation.CRID = TransactionHeaderID) from @THeader, mtblTransactionAllocation - do the updates on the table variable

Update @THeader
set ApplyAmt = (SELECT mtblTransactionAllocation.Amount
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID and
mtblTransactionAllocation.CRID = 187 and PatientID = 9) from @THeader, mtblTransactionAllocation - do the updates on the table variable

- below is where the problems occur. It occurs with statements referencing columns in the table variable, i.e. @THeader.ApplyAmt

UPDATE mtblTransactionHeader
SET mtblTransactionHeader.TransactionAllocationAmount = @THeader.TransactionAllocationAmount,
mtblTransactionHeader.ApplyAmt = @THeader.ApplyAmt
FROM @THeader, mtblTransactionHeader
WHERE @THeader.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID - put the values back into original table

Thanks in advance

smHaig

View 1 Replies View Related

Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?

Aug 3, 2015

How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?

View 5 Replies View Related

How To Set Column As Identity Column

Jan 2, 2014

CustomernameAgeIDN(identity)
ABS231
RAM435
RJD327
ROHIY548
AJAY239

I need to set the IDN column as identity column with same existing value for current existing data in the table but when new data insert in next row the IDN column must be autogrowth with next append value.

Currently I m working with SQL 2008...

View 2 Replies View Related

Computed Column Referencing A Computed Column

Sep 5, 2007

Hi all,

I have a table with 3 computed columns, the first two reference a function for there value. The last computed column should be total of the other computed columns, however when trying to write the formula for this column SQL Server rejects it.

Am i correct in thinking it is not possible to reference other computed columns in a computed column's forumla.

My current work around is to call the functions again that the other computed columns use to generate the total, though this seems to me like a performance issue.

Could anyone offer some advise on my situation.

Any input apreciated.

View 1 Replies View Related

Correlated Subquery Column Referencing Outer Date Range

Aug 25, 2006

Any ideas how can I pass date range values from the where clause of an outer query to the inner correlated subquery ... without using a stored procedure because I am using Report Builder?

Using the simplified sql below I need the average freight charge between the dates for all of the ShipCountry's orders. (I have hard coded the dates for demo purposes only as it is these that I need referenced from the outer query's where clause.)

select
OrderDate,
ShipCountry,
ShipCity,
Freight,
/* how do I get to the outer query's date range ? */
(SELECT AVG(Freight) FROM Orders WHERE ShipCountry = O.ShipCountry AND OrderDate between '01-jan-1997' and '01-jan-2000') AS CountryAverageFreight
from
Northwind.dbo.Orders O
where
ShipCity = 'Paris' and OrderDate between '01-jan-1997' and '01-jan-2000'

Thanks

View 9 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

Alter Table Add Column - How Do You Add A Column After Say The Second Column

Jul 11, 2007

When you use "Alter Table add Column", it adds the column to the end of the list of fields.

How do you insert the new column to position number 2 for instance given that you may have more than 2 columns?

Create table T1 ( a varchar(20), b varchar(20), c varchar(20))

Alter table add column x varchar(20)

so that the resulting table is

T1 a varchar(20), x varchar(20), b varchar(20), c varchar(20)

Can this be done programmatically?

View 33 Replies View Related

Trying To Get Identity Column

Nov 8, 2007

I have been trying to get the new sol_id that is added so I can use it in other code, but it is only returning 0. The new record is getting added to the table, but my varaiable myNewSolID is 0 in my response.write(myNewSolID)
I appeaciate any help!
Here is my steored procedure and my code:
ALTER PROCEDURE [dbo].[AddTrackings]

@DropDate datetime,
@Comment nvarchar(100),
@DateEntered datetime,
@EnteredBy nvarchar(50),
@Sol_ID Int OUTPUT
AS
INSERT INTO tblTrackings (DropDate, Comment, DateEntered, EnteredBy)

VALUES (@DropDate,@Comment,@DateEntered,@EnteredBy)

SET @Sol_ID=SCOPE_IDENTITY()

RETURN

Sub ProcessTracking()
Dim myNewSolID As Integer

Dim ConnectStr As String = _
ConfigurationManager.ConnectionStrings("2ConnectionString").ConnectionString

Dim MySQL As String = ""

If Me.chkDropSave.Checked Then
MySQL = "AddTrackings"
Dim MyConn As New SqlConnection(ConnectStr)
Dim Cmd As New SqlCommand(MySQL, MyConn)
Cmd.CommandType = CommandType.StoredProcedure
Dim InsertedInteger As New SqlParameter("@Sol_ID", SqlDbType.Int)
InsertedInteger.Direction = ParameterDirection.Output
Cmd.Parameters.Add(InsertedInteger)

Dim MySqlParamStart As New SqlParameter("@DropDate", SqlDbType.DateTime)
Cmd.Parameters.Add(MySqlParamStart)
MySqlParamStart.Value = Me.BasicDatePickerDropDate.SelectedValue

Dim MySqlParamAmtTo As New SqlParameter("@Comment", SqlDbType.NVarChar, 100)
Cmd.Parameters.Add(MySqlParamAmtTo)
MySqlParamAmtTo.Value = Me.txtComment.Text

Cmd.Parameters.AddWithValue("@DateEntered", Now())
Cmd.Parameters.AddWithValue("@EnteredBy", Profile.UserName)

Try
MyConn.Open()
myNewSolID = Cmd.ExecuteScalar()Response.Write(myNewSolID)

Catch ex As Exception
Response.Write(ex.Message)
Finally
MyConn.Close()
End Try

Else
End If
End Sub

View 8 Replies View Related

Is Identity Column?

Apr 24, 2008

Hi to all, Using sql query i want to check whether the column is Identity column or not? Please members reply me.

View 2 Replies View Related

Identity Column - DTS

Mar 27, 2001

Having an identity column in target table, how do I use SQL 7 DTS ?

In Import wizard, I chose 'ignore' in source, checked Enable Indenity Insert - DTS failed with a msg like - a column cannot have NULL...

In DTS package, had Fast load and Enable Indenity checked - no luck !

Unchecked Enable identity, do luck.

What would be the standard procs for this since this sounds like a very common
senario.
What I am expecting is to insert from source and SQL would take care identity column automatically.
Appreciate your help very much !

-Ivan

--------------------------------------------------------------------------------

View 1 Replies View Related

Identity Column

Sep 17, 2001

I want to know how to reset the identiy column when i delete a row from the existing table.

Example:

I have a table a with id (identity incr value 1) and name

I have 10 records inserted into it.,which would be from 1...thru...10 i delete a record say 5 then i have 1,2,3,4,6,7,8,9,10, now i want them to be rearranged to systamatic order like 1,2,3,4,5,6,7,8,9 how can i do this.

Thanks in advance,

Shaheen

"God Bless America"

View 2 Replies View Related