|
You can't really make the insert query return anything else then the number of affected rows.
You will have to follow your insert statement with a select statement.
Something like:
INSERT INTO Table1 (Field1) VALUES('blablabla')
SELECT MAX(PrimaryKeyField) FROM Table1
My advice is free, and you may get what you paid for.
|
|
|
|
|
No, no no. Really that is about the worse way you can retrieve the inserted key.
|
|
|
|
|
sometimes several people, several apps are operating on the same database table. At the same time.
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
Well, they shouldn't. There should be a law against it.
My advice is free, and you may get what you paid for.
|
|
|
|
|
INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])] SELECT [source.]field1[, field2[, ...] FROM tableexpression;
you can first insert and then can retrive scalar value like below
select max(ColumnName1) as Max_RowNumber from table_name;
I guess this will help you.
Reasons are not Important but Results are Important.
http://www.sql4professional.blogspot.com
Swati Tripathi
|
|
|
|
|
Second response without a clue.
|
|
|
|
|
Please ignore both posts above telling you to select the max(field) after your insert. It will be prone to problems if you continue to use this method in a high-usage environment.
MSAccess has a built-in variable for this - @@IDENTITY. For SQL Server it is slightly safet to use the function SCOPE_IDENTITY().
So this is what you're after:
-- Assumes "MyTable" has an Autonumber PK field
INSERT INTO MyTable(Foo)
VALUES('Foo')
SELECT @@IDENTITY AS InsertedKey
|
|
|
|
|
AFAIK it needs some clarification, maybe this:
and that magic variable exists for every connection (similar to "thread-local storage"), so you can access it reliably as long as you continue to use the same connection.
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
Very nice. I didn't know about either function. Then again, I haven't had any need for this yet.
J4amieC wrote: MSAccess has a built-in variable for this - @@IDENTITY. For SQL Server it is slightly safet to use the function SCOPE_IDENTITY().
Just in case I will need it in the future, what is the difference / why is it safer to use SCOPE_IDENTITY() with MS SQL ?
My advice is free, and you may get what you paid for.
|
|
|
|
|
Assume two users each having own thread.
User1 inserts record then thread is pre-empted before doing the select.
User2's thread runs and inserts record.
User1's thread restarts and does the select.
At this point @@IDENTITY contains the pk of the last record inserted (i.e.User2's record). SCOPE_IDENTITY returns the last pk for the current thread (i.e. User1's thread).
At least that's how I think it works. 
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
The only valid measurement of code quality: WTFs/minute.
|
|
|
|
|
I don't really know, howver this answer was provided not so long ago:
IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
whatever that means...
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
dear friends i used
INSERT INTO MyTable(Foo)
VALUES('Foo')
SELECT @@IDENTITY AS InsertedKey
but it does not work
error is:
"Missing semicolon (;) at end of sql statement"
what should i do?
|
|
|
|
|
I've never done such things, however, given the error message, I would adapt the INSERT part so it fits my needs, then insert a semi-colon between the INSERT part and the SELECT part of that SQL snippet; then take a deep breath, hit ENTER, and watch it doing what I instructed.
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
reza assar wrote: error is:
"Missing semicolon (;) at end of sql statement"
what should i do?
Quote Selected Text
try reading the error message, then engaging brain.
Seriously, if you cant debug the error given the erro message then give up programming now.
|
|
|
|
|
The practical issue here is that if you have an insert trigger that creates an identity record the @@Identity will return the ID from the record created by the trigger whereas @Scope_Identity() will return the ID from the record you re inserting in the current procedure.
This is just another reason why triggers are EVIL!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
can you get the identity without an SP, by issuing two SQL commands separated by a semi-colon, like what the OP seemed trying?
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
Luc Pattyn wrote: can you get the identity without an SP
Never having tried it I needed to test this out, the following worked within SSMS. The Identity value should be an output parameter but thats not too difficult.
CREATE TABLE [IDTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TextData] [varchar](50) NULL)
DECLARE @SQL VARCHAR(1000)
SET @SQL = 'INSERT IDTest (TextData)VALUES(''Test1'');SELECT @@IDENTITY as ID'
EXEC (@SQL)
DROP TABLE IDTest
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
Dear CodeProject GURUs,
Please help me, I want to know that can I use multiple columns for GROUP BY clause after using CASE in GROUP BY? Here is my query.
When I execute my query error occour --> Incorrect syntax near ',' <-- at my GROUP BY clause.
declare @num int
set @num= (select ctrqty from ##temp4temp where ctrqty>1)
print @num
SELECT Distinct b.bl_no,b.laden_date , b.container_code , b.shipper_name ,b.consignee_name,sum(b.qty) As FLQty
FROM docdbdwh.dbo.mastercentral_bl b
INNER JOIN Credo.dbo.account_code ON b.account_code COLLATE DATABASE_DEFAULT = Credo.dbo.account_code.account_code COLLATE DATABASE_DEFAULT
inner JOIN ##tmp_ctr2005111852 c ON b.bl_no COLLATE DATABASE_DEFAULT = c.bl_no COLLATE DATABASE_DEFAULT
And b.container_code COLLATE DATABASE_DEFAULT = c.container_code COLLATE DATABASE_DEFAULT
AND b.vessel_code COLLATE DATABASE_DEFAULT = c.vesselcode COLLATE DATABASE_DEFAULT
And b.voyage_no COLLATE DATABASE_DEFAULT = c.voyageno COLLATE DATABASE_DEFAULT
WHERE ( upper(Credo.dbo.account_code.account_name) like '%FREIGHT%' and upper(Credo.dbo.account_code.account_name) not like '%FREIGHT REBATE%' )
and b.bl_type <> 'S' and b.bl_no Not In (SELECT bl_no FROM docdbdwh.dbo.MasterCentral_BL WHERE BL_ISSUE = 'IDBDO' AND BL_Type = 'T' )
group by case @num
WHEN 2 THEN
(b.bl_no, b.laden_date,b.container_code,b.shipper_name,b.consignee_name,c.status,b.qty)
Else
(b.bl_no, b.laden_date,b.container_code,b.shipper_name,b.consignee_name)
end
Thanks and Best Regards
modified on Thursday, May 20, 2010 5:24 AM
|
|
|
|
|
Apparently it can't be done (not in MS SQL anyway).
You may have to build the whole query as a string first, and add the GROUP BY clause separately, before executing it.
Something like this:
DECLARE @MainQuery as NVARCHAR(MAX)
DECLARE @GROUPBY as NVARCHAR(MAX)
SET @MainQuery = 'SELECT ItemCode, ItemName FROM OITM '
IF 1 = 1
SET @GROUPBY = 'GROUP BY ItemCode, ItemName'
ELSE
SET @GROUPBY = 'GROUP BY ItemCode, ItemName'
SET @MainQuery = @MainQuery + @GROUPBY
EXEC(@MainQuery)
My advice is free, and you may get what you paid for.
|
|
|
|
|
Johan Hakkesteegt wrote:
IF 1 = 1
SET @GROUPBY = 'GROUP BY ItemCode, ItemName'
ELSE
SET @GROUPBY = 'GROUP BY ItemCode, ItemName'
Eh? 
|
|
|
|
|
Just an example of a boolean test. I was too lazy to apply it to his code 
His' would be:
IF @num = 2
SET @GROUPBY = 'GROUP BY (b.bl_no, b.laden_date,b.container_code,b.shipper_name,b.consignee_name,c.status,b.qty)'
ELSE
SET @GROUPBY = 'GROUP BY (b.bl_no, b.laden_date,b.container_code,b.shipper_name,b.consignee_name)'
My advice is free, and you may get what you paid for.
|
|
|
|
|
Thank you so much. The query work fine with your help.
But, because of my idiot wrong logic the result doesn't make what I want. 
|
|
|
|
|
Couple of questions.
Why the use of Distinct? This doesn't make sense to me.
How can you group by b.qty when it is being summed?
This might vary by database, but trying to group by a field (c.status) that is not being selected is usually an error as well. I think you are trying to get two different results sets from similar queries. It might be better to keep the queries separate, or at least until both of them are working.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Yes, you are right group by b.qty doesn't make good result.
what I want is sum(b.qty)
SELECT Distinct b.bl_no,b.laden_date , b.container_code , b.shipper_name ,b.consignee_name,sum(b.qty) As FLQty
from T1. Have to join with T2.
So I make
Inner JOIN T2 on T1.F1=T2.F1 and T1.F2=T2.F2 and T1.F3=T2.F3
Sum(b.qty) work fine for F1,F2,F3 from both Table are same.
But, In T2 I have F4(c.status) that field don't have in T1.
c.status have two type T & F.
What I want sum(b.qty) is to sum c.status(T) one line and c.status(F) one line.
My query recently is sum both c.status(T) and c.status(F) into one line.
Please any idea for this? I am weak in query.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
