User Functions in batch inserts

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: TJoker .NET (nonono_at_nononono.no)
Date: 02/24/04 

Date: Tue, 24 Feb 2004 09:01:25 -0800

I'm having some trouble with an Insert statement.
Here are my table definifions:

table: test_id
 rowId -> INT - NOT NULL
 name -> CHAR(10) - NOT NULL
 date -> DATETIME - NOT NULL
 uid -> UNIQUEIDENTIFIER - NOT NULL

table: test_id_source
 name -> CHAR(10)

I populated the test_id_source table with a bunch of rows
of sample data.

In the test_id table, the rowId column cannot be an
identity column but it has a similar purpose of being the
PK of the table.
I removed the PK constraint for this test.

The value for the rowId, when inserting a new row, comes
from the following user function:

CREATE FUNCTION GetNextTestId() RETURNS INT
AS
BEGIN
  DECLARE @Res INT
  SELECT @Res=1 + ISNULL(MAX(rowId), 0) FROM test_id
  RETURN @Res
END

So, if I do a simple statement like the following it works
fine:
INSERT INTO test_id ( rowid, [name], [date], uid )
   VALUES (dbo.GetNextTestId(), 'some data', getdate(),
newid())

My problem is during the folowing batch insert:

INSERT INTO test_id ( rowid, [name], [date], uid )
   SELECT dbo.GetNextTestId(), [name], getdate(), newid()
FROM test_id_source

I got something like this:

[rowId][name] [date] [uid]
31 a 2004-02-24 10:27:01.523 73ED52BC-
BADF-42AF-B5F4-140E15D914B2
31 b 2004-02-24 10:27:01.523 A6D6D13F-
7CB2-4328-80EA-8E16E1B2BC8B
31 c 2004-02-24 10:27:01.523 0B04F89A-
5866-45FF-9B0E-A376FFC8F615
31 d 2004-02-24 10:27:01.523 4322486A-
4F5E-4918-AE68-F5CD1C60E492
31 e 2004-02-24 10:27:01.523 A4F52511-
F13C-4D2E-9B81-618F8C062538
31 f 2004-02-24 10:27:01.523 4E877AA2-
A1A4-42A5-A07A-AA36DFD6EC0A

So, the NEWID() function was evaluated once for each row,
but my function was not!
I wish it was numbered 31, 32, 33 .. etc ..
Is this the correct behavior? Is there another way of
doing this without adding extra columns to
the table (this is a production database, so I really
can't mess with it a lot)

Thanks a lot

TJ! 

--
TJoker
MVP: Paint, Notepad, Solitaire
****************************************

Relevant Pages

  • Re: Populate table with incremental id
    ... create table seq(val int not null); ... slow query) by the following steps: ... recordID and rowID, ... reference to record in the filtered table, and rowID is an incremental ...
    (microsoft.public.sqlserver.server)
  • Re: User Functions in batch inserts
    ... But how would the trigger know the value to be ... >> rowId -> INT - NOT NULL ... >> CREATE FUNCTION GetNextTestIdRETURNS INT ... >> newid()) ...
    (microsoft.public.sqlserver.programming)
  • Re: itinerating for insert into (rownumber, fetchstatus)
    ... insert into .....select row_numberover (order by id asc) as rowid from tbl --does not help? ... declare @i int ... thx, Regards. ...
    (microsoft.public.sqlserver.programming)
  • Re: Geting Identity back from SS SP
    ... You need to set the Direction property of the ... When I execute the SP in Management Studio @NewID is returned in the first ... public static string InsertOrderSP(ref int OrderID, string FranchiseID, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Geting Identity back from SS SP
    ... the Value property post execution. ... When I execute the SP in Management Studio @NewID is returned in the ... public static string InsertOrderSP(ref int OrderID, ... FranchiseID, int PrimaryVendorID, string Status, string Customer) ...
    (microsoft.public.dotnet.framework.adonet)