@@identity

Consider Table A having an Identity column.
If when I want insert a record to the table, I want to have the autogenerated value to be inserted into another column within the same table and same row, how am I going to do it?
rignhomCheck @.@.Identity from BOOKS ONLINE to get last-inserted identity value.|||Originally posted by Satya
Check @.@.Identity from BOOKS ONLINE to get last-inserted identity value.

Actually, to be safer, I have found that SCOPE_IDENTITY() is better than @.@.IDENTITY. Both do the same thing, but if you happen to have 2 identities being created within different execution scopes, SCOPE_IDENTITY always seems to return the correct one.|||Hi, what I want is
consider TableA with (Column1 Identity, Column2, Column3).

When I insert into this table
I want to issue sort of

INSERT INTO jobs (Column2,Colum3)
VALUES ('Col2Value', @.@.IDENTITY)

I want Column3 to have the same value as the newly autogenerated value for Column1.

Thanks
rignhom|||CREATE TRIGGER TRG_NAME ON TableA
FOR INSERT
AS
BEGIN
DECLARE @.ID BIGINT
SELECT @.ID = Column1 FROM INSERTED

UPDATE TableA
SET Column3 = @.ID
WHERE Column1 = @.ID
END|||if Column3 is a does not allow Null, would this trigger be useful?|||I don't know of another way of retrieving the id value, because the values for either @.@.identity and scope_identity() are only know after the insert in the table. To solve your problem you can define a default value of let's say 0 (zero), this value will then be overwritten by the trigger.|||hmm.. that's a way.. but would the cost be too high, since there's two write. And it may hit concurrency as well..|||Try this, use a computed column over a trigger

create table blah (ikey int identity(1,1), column2 varchar(20),column3 as ikey)

insert into blah select 'joe'

select * from blah

HTH|||Try this, use a computed column over a trigger

create table blah (ikey int identity(1,1), column2 varchar(20),column3 as ikey)

insert into blah select 'joe'

select * from blah

Can you explain what the intention is? Does your insert consists of two selects? How do you know the value for column3 before the actual insert?|||If you run this after running my prior SQL

select * from syscolumns where name in ('ikey','column2','column3')

You will see that ikey and column 3 are almost identical (except for xoffset which I am not sure what it's for, BOL says internal use only)

That makes me think that in one insert it is seeing the insert of those two columns as the same, that's why you are able to specify the insert with only one value even though you have three columns. Not sure if that is answering your question and I'm not sure exactly how it works but I know it's faster and preferred than a Trigger.|||nice ...sql

@@DBTS

Hi

There are tables in our database with timestamp column. As and when rows are updated/inserted in these tables, the timestamp column is getting populated which is getting reflected in the @.@.DBTS system variable.

However when we take the FULL database backup and restore the database, the @.@.BSTS is not showing the same timestamp value as it was when full DB backup was done (the behavior is unpredictable - once i did manage to get the correct @.@.DBTS value).

Does backup and restore process have any effects on the @.@.DBTS system variable.

Any guidance on parameter settings for Backup and restore will be appreciated to resolve this issue.

Cheers

Nishant Hate

I am assuming that is because Timestamp is a binary datatype used for row versioning, if you want the timeback you need to use datetime datatype. If you go to the Timestamp section of the link below Microsoft explains it in detail. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms191240.aspx

|||

Hi

Thanks for your prompt response.

Let me give you background on the issue

There is a source system which is already developed from which i have to take incremental data every day and load into my warehouse.

There are no datetime fields in the source system for me to use and there is no scope for us to get the source system modified,

however each table is having the timestamp column which should give me daily new/updated records in the source system.

We can acheive this by storing the last loaded Timestamp in a control table and getting the latest timestamp using @.@.DBTS

Using this range we can get the incremental data from the source system.

This works fine.

However assuming that there is some issue with the source system and the database has to be restored from the backup.

Ideally i would have liked the @.@.DBTS to have the same value that was there when the backup was taken.

But the @.@.DBTS value is not coming the same infact it moves forward.

Logically @.@.DBTS moving forward should also be fine as I am interested in the records within the range and all the records will be included in the range.

So the question now is should I rely on @.@.DBTS even though it is different from the one which was there at the time of backup.

or am i better off to aviod this because of its unpredictable nature and build something to simulate the @.@.DBTS functionality i.e. Take max(timestamp) across all tables.

Please do let me know your views.

Cheers

Nishant hate

(URgent) My update statement is not working..

Hi..

I have inserted couple of data in a particular table which is as follows..

Portfolio Table

PortId PlanId PortfolioName PorfolioDescription ClientPortolioId

77117838BALPORT NULLNULL77217838HIGHGROW NULLNULL77317838MODGROW NULLNULL

My FundDBF is as follows

RowNumber FUND_ID f.ASSETDESC Import

20BALPORT Balanced True21MODGROW Moderate Growth True22HIGHGROW High Growth True

and this is my Update statement

UPDATE Statements..PlanPortfolioSETPlanId = pm.PlanId,PortfolioName = pm.FUND_ID, PortfolioDescription = pm.ASSETDESCFROMStatements..PlanPortfolio pJoin (SELECT DISTINCTp.PlanId,pd.FUND_ID,f.ASSETDESC ---pd.FUND_IDFROMPartDBF pdINNERJOIN Statements..ClientPlan pon pd.PLAN_NUM = p.ClientPlanIdINNERJOIN FundDBF fon pd.FUND_ID = f.FUND_IDWHERE pd.Import = 1AND NOT (pd.FUND_IDISNULLORLen(pd.FUND_ID) = 0ORpd.FUND_IDNOT IN (SELECTPortfolioNameFROMStatements..PlanPortfolio ppWherepp.PlanId = p.PlanId))) pmon p.PlanId = pm.PlanId

I am trying to put the above table with f,AssetDesc in the PorfolioDescription field..

Any help will be appreciated..

Regards

Karen

Can you provide the result of this query:

SELECT DISTINCT
p.PlanId,
pd.FUND_ID,
f.ASSETDESC ---pd.FUND_ID
FROM
PartDBF pd
INNERJOIN Statements..ClientPlan p
on pd.PLAN_NUM = p.ClientPlanId
INNERJOIN FundDBF f
on pd.FUND_ID = f.FUND_ID
WHERE
pd.Import = 1
AND NOT (
pd.FUND_IDISNULL
OR
Len(pd.FUND_ID) = 0
OR
pd.FUND_IDNOT IN (
SELECT
PortfolioName
FROM
Statements..PlanPortfolio pp
Where
pp.PlanId = p.PlanId
)
)

|||

Karen,

Not for nothin' but if those are real table and column names from your database, you are exposing a lot more than you should of what is obviously a very sensitive database.

|||

charles i have change the database Name.... and included dummy names...

|||

Dinakar,

when i ran that query the result set is empty..

Regards

Karen

|||

Sorry Karen, just making sure - I had a guy post a month ago who posted some stuff he shouldn't have and we wound up taking the thread down.

|||

so until the query I posted (you subquery) returns something your UPDATE will not work. Take the SELECT I posted and start with that..

|||

Dinakar ,

I updated the query bit to this

Update Statements..PlanPortfolioSET--SELECT Distinct PlanId = pd.PlanId, PortfolioName = pd.FUND_ID, PortfolioDescription = pd.ASSETDESCFROMStatements..PlanPortfolio pJoin (SELECT DISTINCTcp.PlanId,pd.FUND_ID,f.ASSETDESC--pd.FUND_IDFROMPartDBF pdINNERJOIN Statements..ClientPlan cpon pd.PLAN_NUM = cp.ClientPlanIdINNERJOIN FundDBF fon pd.FUND_ID = f.FUND_ID/*AND NOT (pd.FUND_ID IS NULLORLen(pd.FUND_ID) = 0OR *//*AND NOT EXISTS (SELECTPortfolioName--PortfolioDescriptionFROMStatements..PlanPortfolio ppWherepp.PlanId = cp.PlanId) */--)) pdon p.PlanId = pd.PlanId

and when i run the select distinct part i get this

17841 BALPORT Balanced
17841 HIGHGROW High Growth

17841 MODGROW Moderate

which is correct but rem out the Select distinct and use an update it doesnt do any thing...

I dont know whats going wrong

|||

You just need to update the desctription.

Update Statements..PlanPortfolio
SET
--SELECT Distinct
PlanId = pd.PlanId,
PortfolioName = pd.FUND_ID,
PortfolioDescription = pd.ASSETDESC

Also, what does this return:

SELECT DISTINCT
cp.PlanId,
pd.FUND_ID,
f.ASSETDESC--pd.FUND_ID
FROM
PartDBF pd
INNERJOIN Statements..ClientPlan cp
on pd.PLAN_NUM = cp.ClientPlanId
INNERJOIN FundDBF f
on pd.FUND_ID = f.FUND_ID

|||

The Select distinct returns

17841 BALPORT Balanced
17841 HIGHGROW High Growth
17841 MODGROW Moderate

|||

So your subquery is returning 3 rows for same planId of 17841? which is different from planid of 17838 in the outer SELECT ?

|||

actually it is right cause i have delete plan Id 17838 and inserted a new plan..|||

Dinakar,

The reason its not updating it because for every result returned in the Select Distinct query has a different portfolioId... so if i just update the portfolio desicription its gonna do that for all the descriptions who planId matches ...

so do u have any ideas..

Regards

Karen

|||

Run these queries again:

SELECT DISTINCT cp.PlanId,pd.FUND_ID,f.ASSETDESC--pd.FUND_IDFROM PartDBF pdINNERJOIN Statements..ClientPlan cpon pd.PLAN_NUM = cp.ClientPlanIdINNERJOIN FundDBF fon pd.FUND_ID = f.FUND_IDSELECT Distinct PlanId = pd.PlanId, PortfolioName = pd.FUND_ID, PortfolioDescription = pd.ASSETDESCFROM Statements..PlanPortfolio pJoin (SELECT DISTINCT cp.PlanId,pd.FUND_ID,f.ASSETDESC--pd.FUND_IDFROM PartDBF pdINNERJOIN Statements..ClientPlan cpon pd.PLAN_NUM = cp.ClientPlanIdINNERJOIN FundDBF fon pd.FUND_ID = f.FUND_ID) pdON p.PlanId = pd.PlanId

|||

Dinakar this is the result of both of my queries and they are the same

17842 BALPORT Balanced
17842 HIGHGROW High Growth
17842 MODGROW Moderate