November 7, 2008 at 1:36 pm
Hi All,
I have a SQL 2k5 database that has a primary evidence table with identity/PK.
When data is inserted into a support table it needs to be in the primary evidence table first, and it's PK/Identity needs to post into the support table
The original process was written with cursors that loaded data one row at a time. I want to bulk load the table (700-1000 rows) is normal load.
I tried writing a user-defined-function to do the insert and return Scope_identity but am getting an error compiling the UDF.:
Msg 443, Level 16, State 15, Procedure udf_Insert_Wind_Evdn, Line 20
Invalid use of side-effecting or time-dependent operator in 'INSERT' within a function.
Code for table - very simple
-- CODE STARTS --
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[wind_evdn_TCH](
[data_load_id] [bigint] IDENTITY(1,1) NOT NULL,
[evdn_type_cd] [varchar](2) NOT NULL,
[srvr_id] [int] NOT NULL,
CONSTRAINT [x_wind_evdnTCH_1] PRIMARY KEY CLUSTERED
(
[evdn_type_cd] ASC,
[data_load_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
-- CODE ENDS --
Here is the UDF code:
-- CODE STARTS --
CREATE FUNCTION [dbo].[udf_Insert_Wind_Evdn]
(
@EvdnCd char(2),
@ServerID char(6)
)
RETURNS bigint
AS
BEGIN
DECLARE @DataLoadID char(80)
--DECLARE@EvdnCd char(2); SET @EvdnCd = '99'
--DECLARE@ServerID char(4); SET @ServerID = '9999'
INSERT INTO [CMF].[dbo].[wind_evdn]
([evdn_type_cd],[srvr_id])
VALUES (@EvdnCd, @ServerID)
SELECT @DataLoadID = SCOPE_IDENTITY()
--PRINT @DataLoadID
RETURN @DataLoadID
END
-- CODE ENDS --
Is there a way around this? Thanks in advance for your help
November 7, 2008 at 1:51 pm
The problem you have is that you can't use INSERT, UPDATE, or DELETE statements inside a user-defined function to update database objects (tables).
November 7, 2008 at 2:01 pm
I was looking at your a code a bit more, and it looks like it would still be used in a row by row fashion. What we really need to see is how your are currently processing the data using a cursos, so that we can determine a better way for you to accomplish the task at hand.
Take the time to read the article in my signature block, it provides excellent guidance on how to post your questions so you get the best help possible.
November 7, 2008 at 3:18 pm
Hi Lynn,
Thanks for your reply, but I'm not using the cursors anymore. Just a straight TSQL Insert Into x Select y
There must be a way to do what I'm after without resorting to cursors...
Thank you
November 7, 2008 at 3:35 pm
Tom - the challenge is that you're providing so few details as to what you're doing (who these rows you need to insert are being provided to you, do you need something back after the insert, or just get them in the table), it's hard to be specific. Anything that is going to do the operation one single row at a time might be a perforamce drag.
That being said - you CAN do inserts through stored procs (which you cannot do through T-SQL functions like Lynn mentioned).
If that doesn't help - perhaps try filling in the "back story" (you've already shown what the table looks like, but) and let us know where you are stuck.
----------------------------------------------------------------------------------
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?
November 7, 2008 at 3:45 pm
Hi Matt and Lynn,
This whole process in run inside a stored procedure, which is why I thought of trying a UDF for this Insert. I do need teh Scope_Identity back from Insert, which is used in the support data insert in the stored procedure.
How can I insert into another table - getting the scope_identity, when I'm already a stored procedure.
Really trying to get away from the Cursor thing...
Thanks
November 7, 2008 at 6:09 pm
Hi Tom,
Perhaps OUTPUT clause would halp you? You could process inserts in batches then, retrieving relevant values to a temporary table or table variable.
Regards
Piotr
...and your only reply is slàinte mhath
November 7, 2008 at 7:20 pm
Hi Piotr - thank you for your reply
I thought about that but not sure how to do it.
For instance I want to insert 3 fields into my main table after I get the Scope_Identity
would the insert look like this?
Insert Into MyMainTable fld1, fld2, fld3
SELECT fld1, fld2, (INSERT @fld1, @fld2 OUTPUT SCOPE_IDENTITY INTO @fld3)
Where the Scope_Identity is the value I need back from my master table.
What do you think?
November 7, 2008 at 8:16 pm
Please show us what you already have for your stored procedure.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 7, 2008 at 8:27 pm
Problem: Not enough information. We are guessing at what needs to be done to help you solve your problem. We need the whole story to be able to help you. Starting with what you are trying to accomplish for one. Second, if we are looking at how to improve the stored procedure, then we need to see the whole procedire, including all inputs and outputs, the DDL for the tables that it affects, sample data to put in those tables so we can test what we develop to assist you, sample output of the procedure based on the sample data to be processed.
Best place to start, read the article below in my signature block. It will help you help us help you. Follow its guidelines completely.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply