Real Quick Q On Select @@identity
Jul 8, 2004
Hi all. Just a quick question for you guys...I did a search on Select @@identity and I'm not sure I understand it. The program I'm migrating over (from sql server to oracle) has "Select @@identity" at the end of some insert statements. I'm just tryin to figure out what this does and what the Oracle equivlant would be? i have read posts(or in this case a blog) like this http://weblog.anthonyeden.com/archives/000054.html that states it gives you the value of the PK.
I've also read this http://www.kamath.com/tutorials/tut007_identity.asp stating select @@identity is handy to use as a "connection specific global variable." This all makes sense, however in the code I am examining select @@identity is not assigned to any variable. Basically, my question is if select @@ identity is not assigned to a variable in your code, what use does putting it at the end of a select statement serve? thank you
View 2 Replies
ADVERTISEMENT
Nov 11, 2007
Hey guys... i cant figure this out for the life of me. I have a long T-sql query, and when i enter the string "Rental" into the Listingtype, it says invalid column name "Rental" ... im not looking for the value to be a column, im looking for it to match the value in the ListingType column... here's the query:
(
@StudioINT = NULL,
@Br1INT = NULL,
@Br2INT = NULL,
@Br3INT = NULL,
@Br4INT = NULL,
@OverBr4INT = NULL,
@CondoINT = NULL,
@ListingTypevarchar(10) = NULL,
@WindowAirINT = NULL,
@CentralACINT = NULL,
@BalconyDeckPatioINT = NULL,
@UseOfYardINT = NULL,
@DishwasherINT = NULL,
@WasherDryerINT = NULL,
@FireplaceINT = NULL,
@EIKINT = NULL,
@HardwoodFloorsINT = NULL,
@BroadbandNetINT = NULL,
@TVINT = NULL,
@ThermostatINT = NULL,
@LandlordNotPresentINT = NULL,
@SmokingINT = NULL,
@NoPetsAllowedINT = NULL,
@CatINT = NULL,
@MoreCatsINT = NULL,
@SmallDogINT = NULL,
@LargeDogsINT = NULL,
@DoorpersonINT = NULL,
@IngroundPoolINT = NULL,
@AboveGroundPoolINT = NULL,
@ElevatorINT = NULL,
@UseOfGarageINT = NULL,
@LaundryFacilitiesINT = NULL,
@HealthCenterINT = NULL,
@StorageAreasINT = NULL,
@WheelchairAccessINT = NULL,
@BusinessCentersINT = NULL,
@RentChargeMinINT = NULL,
@RentChargeMaxINT = NULL,
@DebugBIT = 1
)
AS
SET NOCOUNT ON
DECLARE @SQL VARCHAR(8000)
SET @SQL = '
SELECT
r.REListingID,
r.REListingDate,
r.Username,
r.ZipCode,
r.ListingType,
r.StudioFlag,
r.BRFlag1,
r.BRFlag2,
r.BRFlag3,
r.BRFlag4,
r.OverBRFlag4,
r.CondoFlag,
a.WindowAir,
a.CentralAir,
a.BalconyDeckPatio,
a.UseOfYard,
a.Dishwasher,
a.WasherDryer,
a.Fireplace,
a.EIK,
a.HardwoodFloors,
a.BroadbandNet,
a.TV,
a.Thermostat,
a.LandlordNotPresent,
a.Smoking,
a.NoPetsAllowed,
a.Cat,
a.MoreCats,
a.SmallDog,
a.LargeDogs,
a.Doorperson,
a.IngroundPool,
a.AboveGroundPool,
a.Elevator,
a.UseOfGarage,
a.LaundryFacilities,
a.HealthCenter,
a.StorageAreas,
a.WheelchairAccess,
a.BusinessCenters,
a.RentCharge,
a.RentFrequency
FROMdb_REListings as r
INNER JOINdb_RentalAmenities AS a ON a.REListingID = r.REListingID
WHERE1 = 1
'
IF @Studio IS NOT NULL
SET @SQL = @SQL + ' AND r.StudioFlag = ' + CONVERT(VARCHAR(20), @Studio)
IF @Br1 IS NOT NULL
SET @SQL = @SQL + ' AND r.BRFlag1 = ' + CONVERT(VARCHAR(20), @Br1)
IF @Br2 IS NOT NULL
SET @SQL = @SQL + ' AND r.BRFlag2 = ' + CONVERT(VARCHAR(20), @Br2)
IF @Br3 IS NOT NULL
SET @SQL = @SQL + ' AND r.BRFlag3 = ' + CONVERT(VARCHAR(20), @Br3)
IF @Br4 IS NOT NULL
SET @SQL = @SQL + ' AND r.BRFlag4 = ' + CONVERT(VARCHAR(20), @Br4)
IF @OverBr4 IS NOT NULL
SET @SQL = @SQL + ' AND r.OverBRFlag4 = ' + CONVERT(VARCHAR(20), @OverBr4)
IF @Condo IS NOT NULL
SET @SQL = @SQL + ' AND r.CondoFlag = ' + CONVERT(VARCHAR(20), @Condo)
IF @ListingType IS NOT NULL
SET @SQL = @SQL + ' AND r.ListingType = ' + CONVERT(char, @ListingType)
IF @WindowAir IS NOT NULL
SET @SQL = @SQL + ' AND a.WindowAir = ' + CONVERT(VARCHAR(20), @WindowAir)
IF @CentralAC IS NOT NULL
SET @SQL = @SQL + ' AND a.CentralAir = ' + CONVERT(VARCHAR(20), @CentralAC)
IF @BalconyDeckPatio IS NOT NULL
SET @SQL = @SQL + ' AND a.BalconyDeckPatio = ' + CONVERT(VARCHAR(20), @BalconyDeckPatio)
IF @UseOfYard IS NOT NULL
SET @SQL = @SQL + ' AND a.UseOfYard = ' + CONVERT(VARCHAR(20), @UseOfYard)
IF @Dishwasher IS NOT NULL
SET @SQL = @SQL + ' AND a.Dishwasher = ' + CONVERT(VARCHAR(20), @Dishwasher)
IF @WasherDryer IS NOT NULL
SET @SQL = @SQL + ' AND a.WasherDryer = ' + CONVERT(VARCHAR(20), @WasherDryer)
IF @Fireplace IS NOT NULL
SET @SQL = @SQL + ' AND a.Fireplace = ' + CONVERT(VARCHAR(20), @Fireplace)
IF @EIK IS NOT NULL
SET @SQL = @SQL + ' AND a.EIK = ' + CONVERT(VARCHAR(20), @EIK)
IF @HardwoodFloors IS NOT NULL
SET @SQL = @SQL + ' AND a.HardwoodFloors = ' + CONVERT(VARCHAR(20), @HardwoodFloors)
IF @BroadBandNet IS NOT NULL
SET @SQL = @SQL + ' AND a.BroadbandNet = ' + CONVERT(VARCHAR(20), @BroadbandNet)
IF @TV IS NOT NULL
SET @SQL = @SQL + ' AND a.TV = ' + CONVERT(VARCHAR(20), @TV)
IF @Thermostat IS NOT NULL
SET @SQL = @SQL + ' AND a.Thermostat = ' + CONVERT(VARCHAR(20), @Thermostat)
IF @LandlordNotPresent IS NOT NULL
SET @SQL = @SQL + ' AND a.LandLordNotPresent = ' + CONVERT(VARCHAR(20), @LandLordNotPresent)
IF @Smoking IS NOT NULL
SET @SQL = @SQL + ' AND a.Smoking = ' + CONVERT(VARCHAR(20), @Smoking)
IF @NoPetsAllowed IS NOT NULL
SET @SQL = @SQL + ' AND a.NoPetsAllowed = ' + CONVERT(VARCHAR(20), @NoPetsAllowed)
IF @Cat IS NOT NULL
SET @SQL = @SQL + ' AND a.Cat = ' + CONVERT(VARCHAR(20), @Cat)
IF @MoreCats IS NOT NULL
SET @SQL = @SQL + ' AND a.MoreCats = ' + CONVERT(VARCHAR(20), @MoreCats)
IF @SmallDog IS NOT NULL
SET @SQL = @SQL + ' AND a.SmallDog = ' + CONVERT(VARCHAR(20), @SmallDog)
IF @LargeDogs IS NOT NULL
SET @SQL = @SQL + ' AND a.LargeDogs = ' + CONVERT(VARCHAR(20), @LargeDogs)
IF @Doorperson IS NOT NULL
SET @SQL = @SQL + ' AND a.Doorperson = ' + CONVERT(VARCHAR(20), @Doorperson)
IF @IngroundPool IS NOT NULL
SET @SQL = @SQL + ' AND a.IngroundPool = ' + CONVERT(VARCHAR(20), @IngroundPool)
IF @AboveGroundPool IS NOT NULL
SET @SQL = @SQL + ' AND a.AboveGroundPool = ' + CONVERT(VARCHAR(20), @AboveGroundPool)
IF @Elevator IS NOT NULL
SET @SQL = @SQL + ' AND a.Elevator = ' + CONVERT(VARCHAR(20), @Elevator)
IF @UseOfGarage IS NOT NULL
SET @SQL = @SQL + ' AND a.UseOfGarage = ' + CONVERT(VARCHAR(20), @UseOfGarage)
IF @LaundryFacilities IS NOT NULL
SET @SQL = @SQL + ' AND a.LaundryFacilities = ' + CONVERT(VARCHAR(20), @LaundryFacilities)
IF @HealthCenter IS NOT NULL
SET @SQL = @SQL + ' AND a.Health Center = ' + CONVERT(VARCHAR(20), @HealthCenter)
IF @StorageAreas IS NOT NULL
SET @SQL = @SQL + ' AND a.StorageAreas = ' + CONVERT(VARCHAR(20), @StorageAreas)
IF @WheelchairAccess IS NOT NULL
SET @SQL = @SQL + ' AND a.WheelchairAccess = ' + CONVERT(VARCHAR(20), @WheelchairAccess)
IF @BusinessCenters IS NOT NULL
SET @SQL = @SQL + ' AND a.BusinessCenters = ' + CONVERT(VARCHAR(20), @BusinessCenters)
IF @RentChargeMin IS NOT NULL AND @RentChargeMAX IS NOT NULL
SET @SQL = @SQL + ' AND a.RentCharge BETWEEN ' + CONVERT(VARCHAR(20), @RentChargeMin) + ' AND ' + CONVERT(VARCHAR(20), @RentChargeMax)
IF @RentChargeMin IS NOT NULL AND @RentChargeMAX IS NULL
SET @SQL = @SQL + ' AND a.RentCharge >= ' + CONVERT(VARCHAR(20), @RentChargeMin)
IF @RentChargeMAX IS NULL AND @RentChargeMAX IS NOT NULL
SET @SQL = @SQL + ' AND a.RentCharge <= ' + CONVERT(VARCHAR(20), @RentChargeMax)
IF @Debug = 1
PRINT @SQL
EXEC (@SQL)
View 5 Replies
View Related
Mar 20, 2007
I am using SQL Server Express and ASP.I have a table that contains news articles, headlines, start and enddates. I am trying to create a recordset that shows all of thearticles that are greater than or equal to the start date and lessthan or equal to the end date. For some reason I cannot get it tofunction properly using a statement similar to StartDate >= Now() ANDEndDate <= Now(). SQL doesnt like the Now() piece of the statement.Anyone have an idea how I can get this to work? I have also triedCURDATE() but to no avail.Please help.
View 3 Replies
View Related
Nov 9, 2006
hi all
another easy one
how would i write it to where i select a value = 0 where all are equal to 0 rather than one line. when selecting based off a key from another table how would i select the values that all bring back 0 for that key rather than just a line or 2? i hope that makes sense, lol.
View 5 Replies
View Related
May 31, 2008
All- I have what i'm sure is a question with a simple answer: Supposing I want to modify the select command below to force the display of records with only unique combinations of the two fields person_id and act_session_id (bolded). How would I do this. Note that neither is a key field. (Key field headcount_id was added for completeness.)
Thanks!
SELECT hd.headcount_id, hd.person_id, hd.act_session_idFROM headcount as hd
View 2 Replies
View Related
Jun 14, 2005
Hi.
1.
Have a query that fetches a real value (e.g. 4.3345643)
Let say the field is called TheReal.
How can I format the value in the select statement so I get a thousand separator(s) and two decimals in the resultset.
2. In the same query I have a left join as well.
Table 2 retur (sometimes) <NULL>.
Is it possible to force this <NULL> value to be a fixed string value instead in the select statement.
View 6 Replies
View Related
Nov 6, 2000
i am getting an 'Incorrect syntax' error while setting @@identity into a variable
declare @vcount int
set @vcount =Select @@identity
can someone help me?
regards,
View 3 Replies
View Related
Sep 18, 2007
Hi guys,
I've a question about the 'select into' clause when using an identity column with an order by clause.
I've attached a sample of code at the bottom.
I've recently read an article (http://support.microsoft.com/kb/273586) where it states that you can't guarantee the order of the identity columns using this statement, however I'm not sure whether this applies to 2005 or not.
Using this statement, I always get the correct order.
----------
begin
CREATE TABLE #x (FileName varchar(50))
INSERT INTO #x(FileName) SELECT 'E'
INSERT INTO #x(FileName) SELECT 'A'
INSERT INTO #x(FileName) SELECT 'C'
INSERT INTO #x(FileName) SELECT 'B'
INSERT INTO #x(FileName) SELECT 'D'
SELECT * FROM #x
SELECT identity(int,1,1) AS ID, FileName
INTO #y
FROM #x
ORDER BY FileName
SELECT * FROM #y
DROP TABLE #x
DROP TABLE #y
end
----------
Can anyone tell me if it is safe to use rely on the ordering of this statement in 2005, is there an article which discusses this ?
Sean
View 6 Replies
View Related
Jul 20, 2005
I have the following query that has been working so far. I modifiedit slightly to work from another source table (new_products). Now,SQL Server complains that the identity is inherited already:"Cannot add identity column, using the SELECT INTO statement, to table'dbo.my_products', which already has column 'id' that inherits theidentity property."Any suggestions for a work aorund?This is the query:SELECT IDENTITY(INT,1,1) as seq_number, prod_number, prod_nameINTO my_productsFROM ( SELECT prod_number, prod_nameFROM new_productsWHERE ...ORDER BY ...) sub_table
View 1 Replies
View Related
Mar 6, 2008
Hi just a beginner question.. is this the same? SELECT @@IDENTITYandselect scope_identity() as returnidentity"; Thanks!
View 5 Replies
View Related
Mar 25, 2008
Hello,
Is it possible to generate a identityfield dynamically upon select, like this:
SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username ASC
I want the output to be:
1 - Name12 - Name23 - Name3
The reason for this, is that i want to change the sort order in many diffrent ways, but i need to get the IDs from 1-?? even when the sort order changes.
Like:
SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username DESC
I want the output to be:
1 - Name32 - Name23 - Name1
Patrick
View 4 Replies
View Related
May 16, 2007
Hi!
We are porting a database from DB2 to SQL-Server 2005. We use WAS 5.1. We have problems with insert staments followed by an SELECT @@IDENTITY. The sql error is that no result set is returned. The syntax is "<insert statment>;SELECT @@IDENTITY.
We have no problems with this in the 1.1 version (there we have the known "read date" error).
Joachim
View 9 Replies
View Related
Mar 29, 2007
Hi! I am having a problem getting the inserted id from a table.
Here is the table:
/*********************** Companies **********************************/
CREATE TABLE Companies(
CompanyId int IDENTITY(1,1) PRIMARY KEY,
Description nvarchar(30) NOT NULL
)
GO
When I type SELECT @@IDENTITY in SQL Management Studio after a successful insert, it works fine. However, in code (System.Data.SqlServerCe), I get an error using this code:
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
ret = (int)cmd.ExecuteScalar();
I saw a thread with this solution, but do I really have to go to this trouble?
DECLARE @MyTable table
( MyIdentity int )
INSERT INTO Employees ( FirstName, LastName )
OUTPUT inserted.EmployeeID INTO @MyTable
VALUES ( 'Bill', 'Smith' )
SELECT MyIdentity
FROM @MyTable
scope_identity does not seem to work either. Am I missing something?
Thanks!
Michael
View 11 Replies
View Related
Oct 10, 2007
We have SELECT INTO queries that use the IDENTITY function to assign a sequential row number to a result set based upon a sort order. This has been helpful in SQL Server 2000 for median determination. It appears, however, that in SQL Server 2005, the row numbers are not assigned sequentially, or maybe they are assigned before the sort order is applied.
Can anyone verify whether the IDENTITY function has changed behavior between 2000 and 2005? We would prefer not to have to make changes to existing queries. Thank you.
(BTW, the workaround we found so far is to put the initial SELECT...ORDER BY in a subquery, but then we had to include a phrase like TOP 10000000 to pass syntax check. Is there a better way?)
View 4 Replies
View Related
Mar 9, 2008
The following SQL statement fails on SQL CE 3.5 but works on SQL Express 2005:
"INSERT INTO BOOKINGS VALUES(@now,'"+note+"'," + p + "); SELECT @@IDENTITY;"
Compact 3.5 doesnt like the SELECT statement claiming that:
There was an error parsing the query. [ Token line number = 1,Token line offset = 72,Token in error = SELECT ]
Can anyone suggest the correct SQL to implement this via Compact? i.e. How do I retrieve the Identity value during and insert statement?
I have removed the SELECT @@IDENTITY; portion of the statement and it runs fine.
View 9 Replies
View Related
May 5, 2008
Hello everyone,
I have a view, NAS_vPosition that has a coloumn vLogin_Acting and I want to use the user.identity.name to select the row from this table that matches.
So far i have tried:
SelectCommand = "Select * FROM NAS_vPosition WHERE vLogin_Acting = ' <%=User.Identity.Name %> ' "
with no success.
Any help is appreciated
View 6 Replies
View Related
Nov 28, 2005
Greetings,If I use a "select into" to clone a table, all attributes are createdcorrectly, however, if I use the same statement across a linked server,my identity column loses its IDENTITY specification.Is this a known issue or basic functionality of using "select into"with linked servers?Kurt
View 2 Replies
View Related
Jun 5, 2007
Does SELECT @@identity works with in SQL Server compact edition? I have tried in SQL management sql query. It does work however when i use in VS.NET 2005 / Orcas beta1 , the generated tableAdapter does not recongize the syntax. So any workaround with it ? or any alternative that i can return the new inserted ID in my application.
Thanks.
View 1 Replies
View Related
Aug 25, 2006
I am having issues getting this to work. I have the user login to a page to
put in a request for vacation. When they login, I have a label that isn't
visible that is equal to their User.Identity.Name.
I select the user from the employee table where the username = the label User
Identity Name and pull in the emp_id which is the primary key that identifies
the user.
I need to insert the request into the request table with the emp_id from the
select statement, without showing the em_id on the screen. I tried using a
hidden field and assigning the emp_id as the value, but it isn't working.
Not sure if this is the best way to do this. Really new to ASP.NET 2.0 so I
really appreciate any help.
Thank you!
View 12 Replies
View Related
Jul 9, 2006
While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
TIA,
Barkingdog
View 10 Replies
View Related
Jun 30, 2006
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).
What is the most direct way to do this in SSIS?
TIA,
barkingdog
P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
View 12 Replies
View Related
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
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
May 13, 2008
I have to implement some logic in a view and I don't know how to do it. Let me outline the logic:
- I'm working with a view where records represent participant activities.
- Some participants can have numerous activities.
- Each activity has a start date and an activity code.
- Only records with activity code 23 or 33 appear in the view currently.
What I need to do is NOT include records where the participant has another activity with code 26 (from another table, the one the current view is derived from) which has the same start date as the activity 23/33 record in the view currently. Also, this should only be implemented if the start date is the first start date (minimum start date for that participant).
Little help?
View 9 Replies
View Related
Mar 15, 2007
I've noticed that there are a lot of questions on this forum, but not as many thank you's. I'm far from a DBA (more of a simple, small town, one stop shop), but have learned tons of information from reading your posts. I certainly appreciate everyone's willingness and excitment to contribute to the SQL community. So here's to you...thank you!
Erik
View 1 Replies
View Related
Jul 24, 2007
hey guys, how could you get the primary key field of a record you have just inserted into a table?
Many thanks
View 6 Replies
View Related
Jul 31, 2007
Hey guys,
How would you copy data between two identical tables residing on two different databases?
So we have table1 on database1 and table2 on database2. I would like to copy the entire contents from table1 to table2.
Muchos gracias
View 1 Replies
View Related
Aug 10, 2007
Hi All
What am i doing wrong with this im tired and for the life of me it wont work
Cheers
Phil
if
(Select sum(Unitprice*qtysold) from #DataExport_tmp where recordtype = 'L'group by transref )<> select Totalcost from #DataExport_tmp where recordtype = 'T'
BEGIN
PRINT 'no good'
end
View 3 Replies
View Related
Oct 28, 2007
i use windows vista and installed visual net 2005 and sqlserver 2005
but i have some proplem:
my step:
1.open vs net 2005
2.make one project
3.make one button
4.get data->datagridview
5.choose add data source
6.choose new data---> this step then choose microsoft sql server
7.typing servername
8.choose attach a database file and choose browe..
9.link to folder have database(.dbf)
but i have worked them very much but not run for me
i dont know why it is like that
"login faild"or you dont have permission to pen this filecontact the file ower or an administrator to obtain permissio
pls help me i very need for learning
my Y!M: phok28a@yahoo.com
pls contact me and help me
View 1 Replies
View Related
Dec 15, 2006
What is the parameters for starting & stoping sqlserver 2005 from start/run or from the command prompti tried 'sqlservr' & it didn't work
View 1 Replies
View Related
Dec 7, 2007
this query works, i want to add a 4th column that is the value of the 3rd column subracted from the value of the 2nd column, how can i add this?? SELECT `tagid` AS w1, (SELECT count( `value` )FROM tagsWHERE `value` =1AND `tagid` = w1) AS w2, (SELECT count( `value` )FROM tagsWHERE `value` =0AND `tagid` = w1) AS w3FROM tagsWHERE `value` > -1GROUP BY `tagid`ORDER BY w2 DESC
View 1 Replies
View Related
Apr 6, 2004
Hey,
I know thsi is a silly question but gonna ask it anyway :)
When you are using the 'insert' statement to insert records in to a database are all of the fields in the db table required for a successfull adding of a record.
Just that i have 16 fields in my db table and want to insert only 11 fields in to the new record and it is giving me an error, and i am sure my SQL is correct
So are all fields required ?
Thankyou
Chris
View 2 Replies
View Related
Apr 25, 2001
I just need something verified.. This has to do with IP's and names. We have a sql failover cluster. So is that 4 names.. One for each local machine, one for the cluster itself and one for the sql portion? I guess my question is can the cluster group and sql have the same name and IP or do they need to be different.
Thanks in advanced.
View 1 Replies
View Related
