Problem with a multi-row insert

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)

Problem with a multi-row insert

Author

Topic

Linok
Starting Member

34 Posts

Posted - 2006-11-20 : 14:40:01

Hey everyone,

I'm new to SQL Server, and I've got a problem with a multi-row insert. Right now, have a table with the following schema:

CREATE TABLE [dbo].[acc_prod](
[acc_id] [int] NOT NULL,
[prod_id] [int] NOT NULL
) ON [PRIMARY]

That I'm using to relate products to accessories. I'm righting a stored procedure that'll add a record for every accessory to a product. I've got the insert in the accessories table working just find and I'm grabbing the identity from that insert and saving it as a variable since it'll be used in this table. However, I can't get the syntax for the insert into this table right--this the closest I've gotten so far:

INSERT INTO acc_prod (@identity2, [prod_id])
SELECT prod_id
FROM products
WHERE name = @prod_name

I'm thinking this is something really simple, but I don't have the experience to see it.

Anyone have an idea?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-20 : 15:02:57

[code]
INSERT INTO acc_prod
(@identity2, [prod_id]) -- must be a list of columns in the table
SELECT prod_id -- must match the number of items in the insert list

[/code]

CODO ERGO SUM

Linok
Starting Member

34 Posts

Posted - 2006-11-20 : 15:07:58

Ummmm, I'm not following.

@identity2 is @@identity from the insert into the accessories table. It won't be changing, only the [prod_id] will be changing for the number of records retrieved from the SELECT statement. Am I making any sense?

Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-20 : 15:13:47

"Must be a list of columns in the table."

Is @identity2 one of the columns in your table?

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-20 : 15:14:21

INSERT INTO acc_prod (acc_id, [prod_id])
SELECT @identity2, prod_id
FROM products
WHERE name = @prod_name

I think is what you want

MVJ was showing what's wrong

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Linok
Starting Member

34 Posts

Posted - 2006-11-20 : 15:30:30

Here's a full version of the stored procedure:

ALTER PROCEDURE [dbo].[PC_InsertAccessory]
-- Add the parameters for the stored procedure here
@name varchar(50),
@partnumber varchar(50),
@description varchar(500),
@type varchar(50),
@language varchar(50),
@country varchar(50),
@product_id varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert Accessory
INSERT INTO accessories
(name, part_number, description, type, language, country)
VALUES (@name,@partnumber,@description,@type,@language,@country)

DECLARE @identity2 int;
SET @identity2 = @@IDENTITY --get PK of last insert

INSERT INTO acc_prod (@identity2, [prod_id]) --create row where accessory ID is with each row where from SELECT statement
SELECT prod_id
FROM products
WHERE name = @prod_name
END

Does that help?

btw, that's everyone for being so patient with me. :-)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-20 : 15:38:36

Does it help that I pointed out what was wrong with your code, and Brett posted a probable solution?

CODO ERGO SUM

Linok
Starting Member

34 Posts

Posted - 2006-11-20 : 15:51:48

Yeah, it's helps. I don't think Brett's code is where I needed to go, but I'm getting in the right direction.

Here's what I've got now:

INSERT INTO acc_prod (acc_id, prod_id)
VALUES (@identity2, [dbo].[products].[prod_id])
SELECT prod_id
FROM products
WHERE name = @prod_name

However, I'm still getting the following error:
Msg 128, Level 15, State 1, Line 8
The name "dbo.products.prod_id" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Is there a way I can get the value (in each row of the returned recordset) in dbo.products.prod_id into the VALUES area?

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-20 : 16:01:39

Do you also understand that the column list needs to match the colmns in your select?

And you cab't have a local variable in the column list for the select, it has to be only the columns that are in the table

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-20 : 16:02:21

Also replace @@IDENTITY with SCOPE_IDENTITY() if possible, due to concurrency issues and/or audit triggers.

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-20 : 16:05:26

I don't understand why you aren't using the code Brett posted.

Also, the local variable @prod_name is not defined in your procedrue.

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-20 : 16:21:55

You also know that you got the first select correct....you need the same syntax almost for the second on

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources