Help getting started with @@Identity

OK, so, from what people tell me I should be using @.@.Idnetity. What Im trying to do is insert data into a table, than revrieve the id from the new row in that table, than use that id in another sql insert statment later down the road. Currnetly the way im doing it is with a sql insert, than executte the scalar, than execute a reader, which is causeing me much grief.

This is my sql statment here:

Dim

sqlInsertAsNew SqlCommand("INSERT INTO Author (Lastname, FirstName, FullName) VALUES (@.LName, @.FName, @.FullName)", sqlConn)

From what I understand, using @.@.Identity, I can retrive data from my insert statment without using a sperate select statment. Can you guys point me in the right direction, ive looked alot in the forms and such, most I found is either assuming you know what todo, or is security related.

Well,

I just posted a very similar question to yours in the same forum. Wish I would have noticed your post first :->

Thanks

Josh

|||After reading a colleges code I figured out what to do, however I dont think I could explain it. If anyone wants to see my code, feel free to ask.|||

I do not thing you can retrive data from database without a select statement. to use @.@.identtiy, you have to have one column in the table are identity column, and you can not insert any data into this column. usually this column is used as a primary key of the table, so to get the primary key of the row which you just inserted you can do this

INSERT INTO Author (Lastname, FirstName, FullName) VALUES (@.LName, @.FName, @.FullName)
select @.@.iidentity

this statement will return the idtentiy number of the row you just inserted. but you have to make sure on table author there is no trigger to insert data into another table with a identity column, otherwise you will get the identity number of the other table

to avoid this, you probably should alway useselect scope_identity()as much as you can

Hope this help

|||

DavidDu:

I do not thing you can retrive data from database without a select statement. to use @.@.identtiy, you have to have one column in the table are identity column, and you can not insert any data into this column. usually this column is used as a primary key of the table, so to get the primary key of the row which you just inserted you can do this

INSERT INTO Author (Lastname, FirstName, FullName) VALUES (@.LName, @.FName, @.FullName)
select @.@.iidentity

this statement will return the idtentiy number of the row you just inserted. but you have to make sure on table author there is no trigger to insert data into another table with a identity column, otherwise you will get the identity number of the other table

to avoid this, you probably should alway useselect scope_identity()as much as you can

Hope this help

IDENTITY is not a column it is a property of a column.

help for creating stored procedure

ALTER PROCEDURE companyinsert

@.companyid INT IDENTITY(1,1) NOT NULL,
@.companyname varchar(20),
@.address1 varchar(30)

AS

INSERT INTO companymaster
( companyname, address1)
VALUES (@.companyname,@.address1)

i don't want the companyname having the same names are recorded again with the different company id..

Can anyone help me and modify my code according it's giving error...in the @.companyid.

It is being done in sql server 2005 with asp.net C# 2005

You cannot declare a parameter with IDENTITY property in a stored proc. I think what you are looking for is more along these lines. You might want to read up documentation on SCOPE_IDENTITY(). Briefly, it returns the Identity value that was created due to your INSERT.

ALTER PROCEDURE companyinsert@.companyidINT OUTPUT,@.companynamevarchar(20),@.address1varchar(30)ASBEGINSET NOCOUNT ONIFNOT EXISTS(SELECT *FROM companymasterWHERE CompanyName = @.companynameAND Address1 = address1)BEGININSERT INTO companymaster ( companyname, address1)VALUES (@.companyname,@.address1)SELECT @.companyid = SCOPE_IDENTITY()ENDSET NOCOUNT OFFEND

Help building a #temp table

I have the following query:

--build query for #temp table
select 'L' as RECTYPE, identity(int, 1, 1) as JELINENO, max(i.jeno) + 1 as JENO, 'AJE' as JECODE,
--build the fiscal year, when month = October, November or December add 1 to actual year
FY = case
when datepart(month, GetDate()) = '10' then datepart(year, GetDate()) + 1
when datepart(month, GetDate()) = '11' then datepart(year, GetDate()) + 1
when datepart(month, GetDate()) = '12' then datepart(year, GetDate()) + 1
else datepart(year, GetDate())
end,
-- The PD starts in April and increases by 1 each month
PD = case
when datepart(month, GetDate()) = '1' then '10'
when datepart(month, GetDate()) = '2' then '11'
when datepart(month, GetDate()) = '3' then '12'
when datepart(month, GetDate()) = '4' then '1'
when datepart(month, GetDate()) = '5' then '2'
when datepart(month, GetDate()) = '6' then '3'
when datepart(month, GetDate()) = '7' then '4'
when datepart(month, GetDate()) = '8' then '5'
when datepart(month, GetDate()) = '9' then '6'
when datepart(month, GetDate()) = '10' then '7'
when datepart(month, GetDate()) = '11' then '8'
when datepart(month, GetDate()) = '12' then '9'
else 'error'
end,
'1' as SUBPD, 'N' as REVFLAG, 'Journal Entry from MAXIMO' as HEADDESC,
--If the issuetype is a return the AMOUNT is a negative value, multiply by -1
AMOUNT = case
when m.linecost < 0 then m.linecost * -1
else m.linecost
end
into #temp
from matusetrans m, issuesreturns i
go
select * from #temp
drop table #temp

When I run this query, I get the following error:

Server: Msg 8118, Level 16, State 1, Line 2
Column 'm.linecost' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 2
Column 'm.linecost' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 2
Column 'm.linecost' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#temp'.

I can find no reason for this error. If I run the query without the AMOUNT = case statement, it works fine. Can anyone help?

Thanks,

Dave

The mistake only arises in the AMOUNT=CASE statement since it's the only one (except from the MAX(i.jeno) column) that's accesing a real column in the database. The rest of the columns are calculated.

So, as you already have an aggregate function (the MAX) the rest of the columns from real tables must either have an aggregate function or the SELECT clause must contain a GROUP BY clause.

You can't form a SELECT with only one MAX column and the rest of them normal columns without telling SQL how it should group the results from where to choose the MAX value from.

If you really want to achieve that you have to calculate the MAX column either before in a variable or in the same SELECT with a sub-SELECT query.

IE: SELECT var1, var2, var3, (SELECT MAX(othervar) FROM table)

Hope i was clear.
Good luck,
Alan.

|||

First of all I would compress the code and rewrite it as following.

Please post DDLs as it's not clear what do you want to do. the problem is clear as you have a reference to the column m.linecost that is not in a group by list. Just to get rid of the error you can add a group by abs(m.linecost), but it doesn't look like what you want to do.

select
'L' as RECTYPE,
identity(int, 1, 1) as JELINENO,
max(i.jeno) + 1 as JENO,
'AJE' as JECODE,

--build the fiscal year, when month = October, November or December add 1 to actual year

FY = datepart(year, GetDate()) + (datepart(month, GetDate()) + 2) / 12,

-- The PD starts in April and increases by 1 each month

PD = (datepart(month, GetDate()) + 8) % 12 + 1,

'1' as SUBPD,

'N' as REVFLAG,

'Journal Entry from MAXIMO' as HEADDESC,

--If the issuetype is a return the AMOUNT is a negative value, multiply by -1

AMOUNT = abs(m.linecost)

into #temp

from matusetrans m, issuesreturns i

|||Dave -

You can't use an Aggregate Function on only one column without using a Group By for the other columns

So you either need to group by m.linecost OR put an Agrregate around it - like max() or min().

To simpify your query

select
max(i.jeno) + 1 as JENO, -- YOU USED MAX HERE
AMOUNT = case
when m.linecost < 0 then m.linecost * -1
-- SO YOU EITHER NEED TO USE AN AGRREGRATE HERE OR
else m.linecost
end
into #temp
from matusetrans m, issuesreturns i
-- OR GROUP BY m.linecost HERE

Hope that helps

AWAL

Help - Unusual Problem

First, I can not change the data structure. I have a table called codesubsections that has an identity as the primary key. When a new codesubsection is entered the user can mark a formercodesubsection as a parent. The problem is that I need to be able to evaluate the table and identify the lowest CodeSubsectionID for a given row. so for instant when I look at row 7 it's text is 17 B p2 but because the CodeSubSectionID is not null I need to look at 6 then 5 and since 5 is null I need the text for 5,6,7 all to be 16:1 B. The only solution I have had any kind of luck with is to do a self join 3 times and coalesce the values up. See the code at the end. I am at my wits end, I had it working the other way assuming that the CodeSubSectionID was the parent of the record. This is the final piece of code on a project that is supposed to end tomorrow.

Any help is appreciated. I would normally post the procedures but I think that they are more confusing that the data explanation. This type of relationship is a first for me after 20 some years.

ID CodeSubSectionID Text

1 NULL 16:1 A

2 NULL 16:1 B

3 NULL 17 A

4 NULL 17 B p1

5 NULL 16:1 B

6 5 16:1 B

7 6 17 B p2

8 3 17 C

9 NULL 18

What is the version of SQL Server that you are using? You can use recursive CTEs in SQL Server 2005 to get your results. A generic solution on SQL Server 2000 requires some procedural code but you can expose it via non-inline table-valued function. Please specify your version and expected results. I am not sure how the concatenation is supposed to happen. Does this have to be in specific order for the related items.|||

This piece of code can get the result:

SELECT Y.id1 AS id, Y.minPid, text FROM codesubsections INNER JOIN

(SELECT Z.id1, COALESCE(Z.id4,Z.id3,Z.id2,Z.id1) AS minPid

FROM (SELECT A.id as id1, B.id as id2,C.id as id3, D.id as id4

FROM codesubsections AS A LEFT OUTER JOIN

codesubsections AS B ON B.id = A.CodeSubsectionID LEFT OUTER JOIN

codesubsections AS C ON C.id = B.CodeSubsectionID LEFT OUTER JOIN

codesubsections AS D ON D.id = C.CodeSubsectionID) AS Z) AS Y on Y.minPid=id

--You may keep adding the LEFT OUTER JOIN levels to move up.

|||

Sorry using 2000 and CTE not available.

The second solution would be fine and that is what I had done however, I don't know how deep I have to go beforehand, so I need to test the grandparent and if it has a code section and continue to loop. In order to do that I added an UnProcessed field and this is the loop that I set up and the UDF that I am using. The problem is that when the loop finishes that the bottom item's codesectionid is null and does not get included in the last loop so I never get the 5 text value to update the 6 and 7 value. it only goes to the 6 record. Hope this makes sense.

I really do appreciate the responses. Thanks in advance

DECLARE @.id INT
SET @.id = 1
WHILE @.id = 1

BEGIN
UPDATE CodeSectionsSummary SET
CodeSectionsSummary.UnProcessed = PCSUMMARY.UnProcessed,
CodeSectionsSummary.CodeSubSectionID =PCSUMMARY.CodeSubSectionID,
CodeSectionsSummary.CodeSection = PCSUMMARY.Parent,
CodeSectionsSummary.ParticipantTypeID = PCSUMMARY.ParticipantTypeID,
CodeSectionsSummary.CodeTypeID = PCSUMMARY.CodeTypeID
FROM
CodeSectionsSummary
INNER JOIN PCSUMMARY(0) PCSUMMARY ON CodeSectionsSummary.ID = PCSUMMARY.ID

IF EXISTS (SELECT COUNT(*) FROM CodeSectionsSummary WHERE UnProcessed = 1)
SET @.id = 0
ELSE
CONTINUE
END

Code for the UDF

CREATE FUNCTION dbo.PCSUMMARY
( @.CNT INT)

RETURNS @.RtnValue TABLE
(
[ID] INT ,
CodeSubSectionID INT,
Parent VARCHAR(10),
UnProcessed INT,
ParticipantTypeID INT,
CodeTypeID INT
)
AS
BEGIN
INSERT INTO @.RtnValue
SELECT CodeSectionsSummary.ID,
CodeSubsections_2.FormerCodeSubsectionID AS CodeSubSectionID,
COALESCE (CodeSections_2.CodeSection, CodeSections_1.CodeSection, CodeSections.CodeSection) AS Parent,
CASE TEST.FormerCodeSubsectionID
WHEN ISNULL(TEST.FormerCodeSubsectionID,0) THEN
CASE CodeSubsections_2.FormerCodeSubsectionID
WHEN isnull(CodeSubsections_2.FormerCodeSubsectionID, 0)
THEN 1 ELSE 0 END
ELSE 2
END AS UnProcessed,
COALESCE (CodeSubsections_2.ParticipantTypeID, CodeSubsections_1.ParticipantTypeID, CodeSubsections.ParticipantTypeID) AS ParticipantTypeID,
COALESCE (CodeSections_2.CodeTypeID, CodeSections_1.CodeTypeID, CodeSections.CodeTypeID) AS CodeTypeID

FROM CodeSectionsSummary INNER JOIN
CodeSubsections ON CodeSectionsSummary.ID = CodeSubsections.ID INNER JOIN
CodeSections ON CodeSubsections.CodeSectionID = CodeSections.ID AND
CodeSubsections.CodeSectionID = CodeSections.ID AND CodeSubsections.CodeSectionID = CodeSections.ID LEFT OUTER JOIN
CodeSections CodeSections_2 INNER JOIN
CodeSubsections CodeSubsections_2 ON CodeSections_2.ID = CodeSubsections_2.CodeSectionID LEFT OUTER JOIN
CodeSubsections TEST ON CodeSubsections_2.FormerCodeSubsectionID = TEST.ID RIGHT OUTER JOIN
CodeSubsections CodeSubsections_1 INNER JOIN
CodeSections CodeSections_1 ON CodeSubsections_1.CodeSectionID = CodeSections_1.ID AND
CodeSubsections_1.CodeSectionID = CodeSections_1.ID AND CodeSubsections_1.CodeSectionID = CodeSections_1.ID ON
CodeSubsections_2.ID = CodeSubsections_1.FormerCodeSubsectionID ON
CodeSubsections.FormerCodeSubsectionID = CodeSubsections_1.ID
WHERE (CodeSectionsSummary.CodeSubSectionID IS NOT NULL)
RETURN
END

|||

I once had a situation like yours...

Why don't you do this:

declare @.iSubSection as int,
@.vcText as varchar ( max ),
@.tbTable as table ( iID int )

set @.iSubSection = <<id of your starting section>>

while not @.iSubSection is null

begin
insert into @.tbTable
select @.iSubSection

select @.iID = ID , @.iSubSection = CodeSubSectionID , @.vcText = Text
From your_table
where @.iSubSection = id
end

update your_table
set text = @.vcText
from @.table
where iID = ID

|||

Miguelb, i think you are on to a possible solution to this nightmare. Business Analyst that use reverse logic had a special place in hades reserved. I played around with your suggestion. The problem is that in the following records 7 looks at 6, 6 at 6 and 5 never gets evaluated because of the null value. Note, the sample data does not show that the text for 5 will probably be different than 6, sorry about that. I will keep playing with this, most promising approach so far.

5 NULL 16:1 B

6 5 16:1 B

7 6 17 B p2

|||

Hi,

I read a few papers about this topic. I came across this solution by using "String-Based "Nested Sets" technique" from the following link. Here is the link to the resource by Dennis W. Forbes.

http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies2.htm

I tested your dataset with this solution and it worked pretty good.

|||

Ever since Heracles snatched Cerberus that they let anyone enter in hades, so I wouldn't be surprised to see those people there :)

You are right - I forgot the last leaf. Try this (you may have to tweak it a little more):

declare @.iID as int,

@.iSubSection as int,

@.vcText as varchar ( max ),

@.tbTable as table ( iID int )

set @.iSubSection = <<id of your starting section>>

set @.iID = null

while not @.iSubSection is null

begin

insert into @.tbTable

select @.iSubSection

select @.iID = ID , @.iSubSection = CodeSubSectionID , @.vcText = Text

From your_table

where @.iSubSection = id

end

if @.iID is not null

begin

insert into @.tbTable

values ( @.iID )

end

update your_table

set text = @.vcText

from @.table

where iID = ID