One more question, INSERT INTO (SELECT) question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

One more question, INSERT INTO (SELECT) question

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-27 : 14:53:41

I have the following, which works just fine, no problems. My question is this. The select for the insert inserts multiple rows. Is there anyway I can capture the IDENTITY of the row just inserted and run a stored procedure on it?

For example, below will Insert Projects with ProjectID 54, 55, 56 when an Insert occurs. Is there anyway when after 54 gets entered, to run a storedproc with that Identity seed? And the 55 and so on...Basically, everytime a record goes in, I need the Identity to be able to run another SQL statement against. Thoughts? Thanks

INSERT INTO Project
(
ProjectName,
SeasonID,
CustomerID,
ProductCategoryID,
CompanyID,
ShipDate,
SalesAmount,
ItemNumber,
ItemDescription,
CostAmount,
MarginAmount,
CustomerOrderNumber,
VendorOrderNumber
)
SELECT
ProjectName,
(SELECT SeasonID FROM Season WHERE [Name]=t.SeasonName) AS SeasonID,
(SELECT CustomerID FROM Customer WHERE [CustomerName]=t.CustomerName) AS CustomerID,
(SELECT ProductCategoryID FROM ProductCategory WHERE [Name]=t.ProductCategoryName) AS ProductCategoryID,
(SELECT CompanyID FROM Company WHERE [CompanyName]=t.CompanyName) AS CompanyID,
ShipDate,
Convert(money,SalesAmount),
ItemNumber,
ItemDescription,
Convert(money,CostAmount),
Convert(money,MarginAmount),
CustomerOrderNumber,
VendorOrderNumber
FROM TempTable t WHERE [ID] NOT IN
(
SELECT [ID]
FROM TempTable
WHERE (ProductCategoryName NOT IN (SELECT [Name] FROM ProductCategory) AND ProductCategoryName <> '')
OR
(SeasonName NOT IN (SELECT [Name] FROM Season) AND SeasonName <> '')
OR
(CustomerName NOT IN (SELECT [CustomerName] FROM Customer) AND CustomerName <> '')
)

Thanks
HC

Kristen
Test

22859 Posts

Posted - 2005-07-27 : 15:31:53

Dunno about multiple rows, but
SELECT SCOPE_IDENTITY()
may get you close to what you want

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-27 : 15:34:20

SCOPE_IDENTITY(), @@IDENTITY, and the other one (CURRENT_IDENT I think) will get you one of them only. So you won't be able to use these to get all of them. You'll need to check the MAX prior to the insert, lock the table, perform the insert, compare the pre max to the current max and you should have the list now.

Tara

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-07-27 : 15:34:39

you could use a trigger to accomplish what you are asking. Look them up in BOL, there are many good examples of how they are used.

-ec

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-27 : 15:57:08

Well, I did end up using a trigger (as a test, of course). What I decided to do was when this insert occurs, Lock the table, and then select all the ProjectID where the DateCreated was DateAdd(s,-60, GetDate())

But I have to say, that seems kinda hockey...Am I missing what your saying? Or is that the path you would choose as well? Thanks

HC

Kristen
Test

22859 Posts

Posted - 2005-07-28 : 02:04:15

What happens if you lock the table first, are the IDENTITY numbers allocated by SQL contiguous? If so you only need to know the last number and the number of rows inserted [@@ROWCOUNT].

An alternative to using an IDENTITY is to have an SProc that allocates the "next available number". We have done that in the past, and included the ability for the SProc to deliver a range of numbers so that processes could ask for a set of numbers, rather than asking for them one-by-one, and such a process would allow you to know, before hand, the numbers of the items you were about to allocate.

Kristen

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-07-28 : 07:20:27

You can use "Instead of insert" trigger in which create a cursor and in that cursor capture the identity field and then you can call the procedure with that identity value inserted.

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-28 : 10:20:00

Sorry I duped threads for what was essentially the same problem. This response was from Tara, and works just fine. Basically gets all the Inserted ProjectIDs per INSERT transaction...I have tested this by having four people do insert at the same exact time, with no erroneous data. Thanks to all of the hep and direction. This has been a great place to learn.

CREATE TRIGGER trgInsertProject

ON Project

FOR INSERT

AS
SET NOCOUNT ON
DECLARE @ProjectID int
DECLARE @SeasonID int
DECLARE @ProductCategoryID int
DECLARE @CompanyID int
DECLARE @ShipDate datetime
DECLARE my_cursor CURSOR STATIC FOR
SELECT ShipDate,
ISNULL(CompanyID, -1) AS CompanyID,
ISNULL(SeasonID, -1) AS SeasonID,
ISNULL(ProductCategoryID, -1) AS ProductCategoryID,
ProjectID
FROM inserted

OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @ShipDate, @CompanyID, @SeasonID, @ProductCategoryID, @ProjectID
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC pCreateProjectEvents @ShipDate, @CompanyID, @SeasonID, @ProductCategoryID, @ProjectID

FETCH NEXT FROM my_cursor INTO @ShipDate, @CompanyID, @SeasonID, @ProductCategoryID, @ProjectID
END
CLOSE my_cursor
DEALLOCATE my_cursor

That seems too easy...but works like a charm! Thanks Tara!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources