Insert Many Records With Condition

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 2005 Forums

Transact-SQL (2005)

Insert Many Records With Condition

Author

Topic

FabioEinaus
Starting Member

41 Posts

Posted - 2007-02-02 : 09:24:54

Hi Mates!

I'd like to know how can I insert for example all the records based on a condition.

I have the following stored procedure:

USE [EINAUS]
GO
/****** Object:  StoredProcedure [dbo].[e_TransformaCarrinho]    Script Date: 02/02/2007 12:12:58 ******/
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),
		@d as VARCHAR(20)
AS
SET NOCOUNT ON

DECLARE @i AS INT
DECLARE @preco AS MONEY

IF (@CodDoc = '1')
BEGIN
	SELECT @preco =  C.PVP 
		FROM Carrinho C
			WHERE C.UserID = 'FAP'
END

IF (@CodDoc = '2')
BEGIN
	SELECT @preco =  C.PC 
		FROM Carrinho C
			WHERE C.UserID = 'FAP'
END




SELECT	@i = 1 + ISNULL(MAX(NrLinhas), 0)
FROM	DocumentosArtigos

BEGIN TRANSACTION

INSERT	DocumentosArtigos
SELECT	'T',
			@d,
			@CodDoc,
			@i,
			C.ArtigoID,
			C.NomeArtigo,
			C.Quantidade,
			@preco,
			0,
			0,
			0,
			21,
			C.Quantidade * @preco,
			0,
			C.Quantidade * @preco * 0.21,
			0,
			0

FROM Carrinho C
			WHERE C.UserID = 'FAP'

COMMIT TRANSACTION

The problem is that it gives me 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.

The question is how to run this SP x times (while existing records that satisfy the condition made)?

Or run only once and insert all the records that satisfy the condition.

Is there anyway to do this?

Thanks in advance for the reply

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-02 : 09:32:18

Primary key consists of which field(s) in DocumentosArtigos table?

You can add IF EXISTS() check before INSERT statement to avoid getting primary key violation error or you can add NOT EXISTS() condition in the INSERT statement to skip those records which are already added.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

FabioEinaus
Starting Member

41 Posts

Posted - 2007-02-02 : 09:40:06

It Consists in Empresa, CodDocumento, DocumentoID And NrLinhas.

But the problem is that there isn't a single record in the table

Thanks Harsh

BTW: I tried to do this:

INSERT	DocumentosArtigos (Empresa, DocumentoID, CodDocumento, NrLinhas, ArtigoID, NomeArtigo, Quantidade,
PrUnitario, Desc1, Desc2,Desc3, TaxaID, ValorFinalProd, ValorDescProd, ValorTaxaProd,DocPrecedenteID, DocConsequenteID)
(SELECT	'T', @d,CodDoc,	@i, C.ArtigoID, C.NomeArtigo,
C.Quantidade, @preco, 0, 0,	0, 21, C.Quantidade * @preco,
0, C.Quantidade * @preco * 0.21,0,0 
FROM Carrinho C)

But It returns me the same error

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-02 : 09:46:51

You can try something along the following lines:

INSERT	D
SELECT	
	'T',
	@d,
	@CodDoc,
	@i,
	C.ArtigoID,
	C.NomeArtigo,
	C.Quantidade,
	@preco,
	0,
	0,
	0,
	21,
	C.Quantidade * @preco,
	0,
	C.Quantidade * @preco * 0.21,
	0,
	0
FROM Carrinho C
LEFT JOIN DocumentosArtigos D
on C.pk = D.pk
Where D.pk is NULL

Note: pk indicates the common key column between the two tables.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

FabioEinaus
Starting Member

41 Posts

Posted - 2007-02-02 : 09:56:08

Msg 207, Level 16, State 1, Procedure e_TransformaCarrinho, Line 57
Invalid column name 'PK'.

WTF ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-02 : 10:08:02

quote:
Originally posted by FabioEinaus

Msg 207, Level 16, State 1, Procedure e_TransformaCarrinho, Line 57
Invalid column name 'PK'.

WTF ?

huh? Can't you see the note I posted at the end?

Note: pk indicates the common key column between the two tables.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

FabioEinaus
Starting Member

41 Posts

Posted - 2007-02-02 : 10:18:15

WOW sorry about this mystake..

LEFT JOIN DocumentosArtigos D
on C.Empresa = D.Empresa AND C.CodDocumento = D.CodDocumento AND C.DocumentoID = D.DocumentoID
Where (D.Empresa AND D.CodDocumento AND D.DocumentoID) is NULL

Msg 4145, Level 15, State 1, Procedure e_TransformaCarrinho, Line 57
An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.

What am I doing wrong mate?

Thanks for all

FabioEinaus
Starting Member

41 Posts

Posted - 2007-02-02 : 10:23:08

I tried this:

FROM Carrinho C
LEFT JOIN DocumentosArtigos D
on C.CodDocumento = D.CodDocumento
Where D.Empresa is NULL AND D.CodDocumento is NULL AND D.DocumentoID is NULL

And The result was:

(1 row(s) affected)

(1 row(s) affected)
Msg 208, Level 16, State 1, Procedure e_TransformaCarrinho, Line 35
Invalid object name 'D'.
Msg 266, Level 16, State 2, Procedure e_TransformaCarrinho, Line 35
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
Msg 266, Level 16, State 2, Procedure e_CriaDocumento, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

(1 row(s) affected)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-02 : 10:23:56

[code]INSERT DocumentosArtigos
SELECT
'T',
@d,
@CodDoc,
@i,
C.ArtigoID,
C.NomeArtigo,
C.Quantidade,
@preco,
0,
0,
0,
21,
C.Quantidade * @preco,
0,
C.Quantidade * @preco * 0.21,
0,
0
FROM Carrinho C
LEFT JOIN DocumentosArtigos D
on C.Empresa = D.Empresa AND C.CodDocumento = D.CodDocumento AND C.DocumentoID = D.DocumentoID
Where D.Empresa IS NULL AND D.CodDocumento IS NULL AND D.DocumentoID IS NULL[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

FabioEinaus
Starting Member

41 Posts

Posted - 2007-02-02 : 10:31:11

(1 row(s) affected)

(1 row(s) affected)
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.

Same Error Harsh.. I just don't get it...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-02 : 10:43:04

Try this:

INSERT	DocumentosArtigos
SELECT	
	'T',
	@d,
	@CodDoc,
	@i,
	C.ArtigoID,
	C.NomeArtigo,
	C.Quantidade,
	@preco,
	0,
	0,
	0,
	21,
	C.Quantidade * @preco,
	0,
	C.Quantidade * @preco * 0.21,
	0,
	0
FROM Carrinho C
Where not exists
	(Select *
	From DocumentosArtigos D 
	Where C.Empresa = D.Empresa AND C.CodDocumento = D.CodDocumento AND C.DocumentoID = D.DocumentoID)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

FabioEinaus
Starting Member

41 Posts

Posted - 2007-02-02 : 10:52:32

The error is still the same..

does thisimage helps?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-02 : 10:54:58

Please post complete table structure along with sample data.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

FabioEinaus
Starting Member

41 Posts

Posted - 2007-02-02 : 11:03:31

the structure of this three tables?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-02 : 14:40:43

Does any of the fields - CodeDocumento, ArtigoID, NomeArtigo or Quantidade - or combination of them is allowed to be duplicate in DocumentosArtigos table?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

FabioEinaus
Starting Member

41 Posts

Posted - 2007-02-05 : 05:04:33

Like the PK refers, the artigoID, NomeArtigo and Quantidade can be duplicate the CodDocumento is only allowed to be duplicate if it is ont a Diferent Empresa or DocumentoID

FabioEinaus
Starting Member

41 Posts

Posted - 2007-02-05 : 06:38:28

I think that the problem may be in this lines:

(SELECT @i = 1 + ISNULL(MAX(NrLinhas), 0)
FROM DocumentosArtigos)

Right now i putted this lines like this:

INSERT DocumentosArtigos
SELECT
'T',
@d,
@CodDoc,
(SELECT 1 + ISNULL(MAX(NrLinhas), 0)
FROM DocumentosArtigos),
C.ArtigoID,
C.NomeArtigo,
C.Quantidade,
@preco,
0,
0,
0,
21,
C.Quantidade * @preco,
0,
C.Quantidade * @preco * 0.21,
0,
0
FROM Carrinho C
Where not exists
(Select *
From DocumentosArtigos D
Where C.CodDocumento = D.CodDocumento)

But I think it return always the same @i because it get's a number from the DocumentosArtigos but as we can see it doesn't return a diferent number because the INSERT of the many records is only done when the select of the records is done completly.

Am I right?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources