Stored Procedure to insert a table into another table Problem

[sinCity99]

Hi, I have table1 and table2 and i want to fill information from table1 into table2 using a stored procedure. Here's what i have:


create sp_table2
(@personId varchar(36),
@value varchar(40) OUTPUT
@value1 varchar(40) OUTPUT)

as

begin

insert into table2 (date, first_name, last_name, personId, value, value1)

select date, first_name, last_name, @personId,
@value = CASE item
WHEN 'food' Then '3' END,
@value1 = CASE item
WHEN 'drink' THEN '2' END
from table1

end


However, i keep getting this error message below. Can someone help? Thanks

Error message:
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

 

[gmmastros]

Try this:

create Procedure sp_table2
(@personId varchar(36),
 @value varchar(40) OUTPUT
 @value1 varchar(40) OUTPUT)

as

begin

insert into table2 (date, first_name, last_name, personId, value, value1)

select date, first_name, last_name, @personId,
CASE item
   WHEN 'food' Then '3' END,
CASE item
   WHEN 'drink' THEN '2' END
from table1

end

Note that I removed @value =



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

First of all, you have your value parameters set as output. What are you trying to accomplish here? What are you expecting to return to the connection by inserting from one table into another? If your goal is simply to insert records from one table to another, but modifying the value and personid columns, something like this would suffice:

insert into table2 (date, first_name, last_name, personId, value, value1)

select date, first_name, last_name, @personId,
[value] =
CASE
   WHEN [value] = 'food' THEN '3'
   ELSE NULL
END,
[value1] =
CASE
   WHEN [value1] = 'drink' THEN '2'
   ELSE NULL
END
FROM table1

However, before you do that, post some sample data from table1 and how you want it to look in table2.

 

[PWise]

insert into table2 (date, first_name, last_name, personId, value, value1)

select date, first_name, last_name, @personId,
CASE item
WHEN 'food' Then '3' END as value,
CASE item
WHEN 'drink' THEN '2' END as value1
from table1

 

[sinCity99]

Thanks, I've been changing this procedure so much that i didn't even realize that i don't need them as output anymore if i'm just going to insert them into a table.

I was trying to set up a trigger that calls a stored procedure that retrieves all information from a personId within table1 and inserting it to table2 because my template can only call information from table2 which was initially empty.

However, while i was inserting from table1 to table2, i encountered a column in table2 that cannot be null but i don't have anything to insert into it. For each row that i insert, can I simply insert ' ' into it?

 

[gmmastros]

You should probably try to find out why the column does not allow nulls. I mean... it must have been set up that way for a reason, right?

If it really is ok to not have any data, then... yeah. Insert an empty string. Since it's not a Null value, it should insert fine.

Also.... something else to consider. If this column that does not allow nulls can have empty strings instead, you may want to consider putting a default on the column. With a default, if you don't include the column in an insert, it will take on the value of the default.

Take a look at this example (using a temp table)

Create table #Test(Id int, EyeColor VarChar(20) Not NULL default(''))

Insert Into #Test (Id) Values (1)

Select * From #Test

Drop Table #Test

Notice that the EyeColor column has an empty string instead of null.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[NeilO123]

I am trying to copy data from one table into another, the message that I receive is that is done. but data is not copied?? This is my proc:
---
USE RUMS
GO
---Drop procedure if it exist
IF (SELECT OBJECT_ID('dbo.hits_transfer','P')) IS NOT NULL - BEGIN
DROP PROC dbo.hits_transfer
END
GO
CREATE PROCEDURE dbo.hits_transfer
AS
BEGIN
INSERT INTO dbo.tbl_AA_temp(appName, version, userID, ocd, mod, lu_date, ssnCount)
SELECT appName, version, userID, ocd, mod, lu_date, ssnCount
FROM dbo.tbl_hits_01
WHERE appName LIKE 'DIBwiz-Quality%'
END;
GO
---\
Command(s) completed successfully.
--
Any help is appreciated!!

 

[gmmastros]

The code you show will create a stored procedure. Did you actually run the stored procedure after you created it. With a line of code like...

Exec dbo.hits_transfer

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[NeilO123]

Thanks for pointing the obvious (I totally missed it).