questions about a Generated Insert Procedure

  • Thread starter Thread starter Bruce A. Julseth
  • Start date Start date
B

Bruce A. Julseth

A couple of questions about a Generated Insert Procedure.

1. Why is there a "SELECT " statement after the insert?

2. This SELECT statement has "WHERE (PrimaryKey = @@IDENTITY)". I "assume"
that @@IDENTITY represents the newly generated "primary key" for the record
that was inserted. Is that something internal to the processing generated
this?

Thank you...

Bruce
 
1. Why is there a "SELECT " statement after the insert?
I assume you mean something like:

INSERT INTO MYTable (col1, col2, col3)
SELECT * FROM MyOtherTable

If so, this is simply copying from a table in the database to your new table.

2. This SELECT statement has "WHERE (PrimaryKey = @@IDENTITY)". I "assume"
that @@IDENTITY represents the newly generated "primary key" for the record
that was inserted. Is that something internal to the processing generated
this?

Not sure about the @@IDENTITY portion of the statement, as I cannot see it
in context to the rest of the statement. @@IDENTITY does point to a newly
created item with an IDENTITY column. SCOPE_IDENTITY() is generally better to
use than @@IDENTITY, especially on high volume sites. It may not be
applicable here, however.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
Cowboy (Gregory A. Beamer) - MVP said:
I assume you mean something like:

INSERT INTO MYTable (col1, col2, col3)
SELECT * FROM MyOtherTable

If so, this is simply copying from a table in the database to your new table.

2. This SELECT statement has "WHERE (PrimaryKey = @@IDENTITY)". I "assume"
that @@IDENTITY represents the newly generated "primary key" for the record
that was inserted. Is that something internal to the processing generated
this?

Not sure about the @@IDENTITY portion of the statement, as I cannot see it
in context to the rest of the statement. @@IDENTITY does point to a newly
created item with an IDENTITY column. SCOPE_IDENTITY() is generally better to
use than @@IDENTITY, especially on high volume sites. It may not be
applicable here, however.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
Click to expand...

No. the SELECT is "Selecting" from the same table (Births in both the INSERT
and the SELECT ). This is the Generated Insert statement by VB.NET. I'm
trying to figure out why the SELECT was generated. When I have writen an
INSERT, I have never followed it with a SELECT!!
INSERT INTO Births(NewspaperID, Date, Page, PageColumn, FatherFirst,
FatherMiddle, MotherFirst, MotherMiddle, MotherLast, First, Middle, Last,
DateOfBirth, DateOfBaptism, Church, Comments) VALUES (@NewspaperID, @Date,
@Page, @PageColumn, @FatherFirst, @FatherMiddle, @MotherFirst,
@MotherMiddle, @MotherLast, @First, @Middle, @Last, @DateOfBirth,
@DateOfBaptism, @Church, @Comments);

SELECT PrimaryKey, NewspaperID, Date, Page, PageColumn, FatherFirst,
FatherMiddle, MotherFirst, MotherMiddle, MotherLast, First, Middle, Last,
DateOfBirth, DateOfBaptism, Church, Comments FROM Births WHERE (PrimaryKey =
@@IDENTITY)

Bruce
 
Bruce,

Believe it or not, the DataAdapter Configuration Wizard is trying to
help. To avoid possible concurrency errors when performing multiple
updates against a row, it will include a query to refresh the contents of
the row after a successful update. This approach is helpful if the
database may update the contents of the row through triggers, defaults,
timestamps, auto-increment columns. You can turn this wizard option off by
clicking "Advanced Options" and un-checking "Refresh The DataSet".

In this case, the wizard (believes it) found an auto-increment column
in the table you're trying to update and uses @@IDENTITY to represent the
last identity value generated on that connection. If you're using SQL
Server 2000 you should use SCOPE_IDENTITY() instead.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.
 
Back
Top