March 11, 2008 at 11:11 am
Hi guys.
i have two tables , Table1 has only Col1 and Table2 has Col1,Col2...
what i did, Table1(Col1) is defined as primary key and Identity Specification = Yes..
Table2(Col1) is defined as Fkey to Table1.
Now i want to insert the data into the tables. but i cann't ..
i know it's bit silly but Can any one give me idea.
Thanks
March 11, 2008 at 11:16 am
March 11, 2008 at 11:20 am
actually what i want to do..
i just want to genrate the key from primary table1 and then take key to Table2...
March 11, 2008 at 11:41 am
i don't know how to insert a row into a table whose only column is an identity column. you could add another column to it like so:
alter table table1 add dateCreated smalldatetime default getdate()
then immediately after doing an insert, @@identity will store the last generated value.
declare @fkey int
insert into table1 (dateCreated) values (getdate())
set @fkey = @@identity
insert into table2 (fkey_column, ...) values (@fkey, ... )
March 11, 2008 at 12:09 pm
You can do this:
INSERT INTO MyTable1
DEFAULT VALUES
Then, with that, you can capture the values you inserted like this:
CREATE TABLE #Id ( id INT )
INSERT INTO onecolumn
OUTPUT inserted.id
INTO #Id
DEFAULT VALUES
SELECT *
FROM #id
DROP TABLE #id
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2008 at 1:45 pm
ah... DEFAULT VALUES. i figured there had to be some way to do it, but never had the need before so i was unaware.
however, why use the OUTPUT clause if @@identity is available (and simpler)? and, what's your opinion of the OUTPUT clause? i've always found it redundant since (it seems) any of it's usage could be handled by a trigger or the calling routine and it's a MS SS only extension.
March 11, 2008 at 2:21 pm
antonio.collins (3/11/2008)
ah... DEFAULT VALUES. i figured there had to be some way to do it, but never had the need before so i was unaware.however, why use the OUTPUT clause if @@identity is available (and simpler)? and, what's your opinion of the OUTPUT clause? i've always found it redundant since (it seems) any of it's usage could be handled by a trigger or the calling routine and it's a MS SS only extension.
If you're only ever inserting one record, then no - OUTPUT probably won't buy you much. That being said - if you start using it while dealing with an actual set, then the virtual tables come in handy and have all of the records you just inserted/updated/selected/deleted. That's where it blows the doors off of @@identity (never mind all of the scenarios which make @@identity unreliable, where @@identity returns the wrong data, etc...).
It also allows you to pull back all of the default values that might have just been assigned.
The point is - it allows you to be a lot more flexible than a trigger. for example - you might care to just record a specific type of deletion: instead of a trigger that fires each and every time, simply put the OUTPUT into the delete statement, for example.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 11, 2008 at 2:39 pm
i see you point, but the script that performs the action can collect the contents of inserted/deleted by "select ... from ... where {criteria used to perform the action}" before and after the action. that works in every dbms, not just sql server.
and when would @@identity (or scope_identity()) be wrong? @@identity has to be something you can hang your hat on just like @@rowcount and @@error. if it's incorrect that's a bug and a bug that should corrected with a patch, not a workaround and certainly not a language extension.
March 11, 2008 at 3:09 pm
antonio.collins (3/11/2008)
i see you point, but the script that performs the action can collect the contents of inserted/deleted by "select ... from ... where {criteria used to perform the action}" before and after the action. that works in every dbms, not just sql server.and when would @@identity (or scope_identity()) be wrong? @@identity has to be something you can hang your hat on just like @@rowcount and @@error. if it's incorrect that's a bug and a bug that should corrected with a patch, not a workaround and certainly not a language extension.
don't confuse @@identity with scope_ident(). One has issues, the other doesn't.
Remember that @@identity returns the last identity value WITHIN THIS SESSION. So - for example - if the insert you have starts up a trigger which also does an insert, @@identity will return the highest identity of the SECOND insert, not the original one.
As to your first question: who says that you have a specific set of criteria identifying rows in the original insert? What if you're posting a "batch" of some sort with all sorts of data in it, and you want to get the identities from inserting that "batch". Sure - you could create and track a batchID in a batch table, increment that batch number every time you need a new batch, insert the batch records into a temp table, update the temp table with the batch ID, insert the batch into the production table (with the batchID), and then go and retrieve them all back once it's all done - but that's a lot of extra steps if you just need those records for something temporary and the batchID isn't needed later. OUTPUT provides a VERY efficient way to get those back (and highly reliable from what I can tell).
Finally - although theoretically possible, I have yet to see code written for SQL server that doesn't make Oracle cry like someone who slammed their thumb in the car door, and vice versa (if it runs at all). SQL code portability as of right now is a myth, IMO.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 11, 2008 at 4:02 pm
again, i see your point, but @@identity (or scope_identity()) used properly can still be used to produce the same results. true, there is an @@identity gotcha but it can be handled in the design since in (imho) a good db/app design identity columns should be used sparingly. (i've been bitten too often by the identity gotchas.)
as for batch inserts (insert into ... select ... from), you know what got inserted via "identityCol between (@@identity - @@rowcount - 1) and @@identity".
i have to write code for multiple DBMSs and thus i'm reluctant to lean to much on server-specific functionality. if there's a different (even less elegant) method that works in most DBMSs, i'm inclined to stick with that method. 🙂
March 11, 2008 at 4:29 pm
If it were simply a matter of "more elegant" - I wouldn't even argue. I'm however talking performance. It saves you a scan of the table altogether. The multiple queries require multiple passes; the insert+OUTPUT is the same IO as the INSERT by itself (no re-reading the data).
Now again, doing this once or twice a week - fine, it won't register on the radar. But doing this a lot, this would make quite a bit of difference. Think of it this way - on GUI code doing this 1M times a week (even for just one record), well - that 1M times fewer index scans/seeks you have to do.
I'm all for writing code for various DBMS'es, and keeping portable. But I play for performance too.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 11, 2008 at 4:33 pm
touche! I/O is an important concern that I had not considered.
Game to miller.
March 11, 2008 at 4:37 pm
woohoo! I won!!!!! 😀
:cool::w00t:;):P:D:):hehe:
Just kidding around. It's good to have to defend your positions from time to time: makes you have to remember why you now assume a certain way. I had to go back and test my previous statement, just to be sure I wasn't remembering another situation...:)
So - thanks for pushing back..:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 11, 2008 at 5:09 pm
i gladly admit when someone has a stronger position that me. i enjoy jousting on topics like this.
😉
March 11, 2008 at 7:09 pm
Oh so do I (enjoy the jousting as you describe it). it stretches the mind.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply