January 8, 2007 at 9:28 am
Hi,
My stored procedures looks like this:
USE [EINAUS]
GO
/****** Object: StoredProcedure [dbo].[e_TransformaCarrinho] Script Date: 01/08/2007 09:01:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[e_TransformaCarrinho]
-- Add the parameters for the stored procedure here
@User as VARCHAR(5),
@CodDoc as VARCHAR(5)
AS
SET NOCOUNT ON
DECLARE @i AS INT
SELECT@i = 1 + ISNULL(MAX(NrLinhas), 0)
FROMDocumentosArtigos
INSERTDocumentosArtigos
SELECT'T',
'50',
@CodDoc,
@I,
ArtigoID,
NomeArtigo,
Quantidade,
CASE
WHEN @CodDoc = 1 THEN PVP
WHEN @CodDoc = 2 THEN PC
ELSE NULL
END,
0,
0,
0,
21,
Quantidade * PC * 1.21,
0,
Quantidade * PC * 0.21,
0,
0
FROMCarrinho
WHEREUserID = @User
I want to insert all the records that I have on the Carrinho table on the DocumentosArtigosTable with a sequencial number for each record that I insert on the fiels NrLinhas.
I call the SP with this command:
Use Einaus
exec e_TransformaCarrinho 'FAP','2'
I've made the @i variable to do this but it is not doing his job and I'm getting the following error:
Msg 2627, Level 14, State 1, Procedure tgDocumentosArtigos_Insert, Line 28
Violation of PRIMARY KEY constraint 'PK_DocumentosArtigos_1'. Cannot insert duplicate key in object 'dbo.DocumentosArtigos'.
The statement has been terminated.
January 8, 2007 at 9:56 am
I would assume that more than one row are being inserted... causing the error. Can you verify that?
January 8, 2007 at 10:23 am
Yes there is 3 rows to be inserted.
My problem is that It have to be inserted one row at a time and it could be done by a while or something like that but how can I know the records that I've inserted before?
Like the arrays I use to do like this:
a=[0,0,0,0,0,0,0,0,0]
i=0
temp=5
do
a
i++
while(i!=10)
But in this case I know the index that i'm working.. how can I now this in SQLServer?
January 8, 2007 at 10:50 am
I recall the best way to solve this one as I've never worked with server generated identities (other than identity column).
I'll see what other dbas have to offer.
PS May I ask why you are not using the build in identity functions of the server for this task?
January 9, 2007 at 2:38 pm
Use IDENTITY(INT,1,1).
How ever this statement will work only for SELECT INTO type of queries. You may need to change your code a little bit to take care of it.
For eg SELECT IDENTITY(INT,1,1) as A into TABLE B from TABLE A
January 9, 2007 at 2:44 pm
Or as I assumed.... change the datatype of the column to use that feature. Message us if you need help to do either.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply