Insert - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Insert

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-20 : 02:15:51

hello,
im working on this piece of code, with error : Incorrect syntax near the keyword 'exec'.
im trying to insert data from other SP into table @Data which was declared b4,



-- Stage the source data
DECLARE	@Data TABLE
	(
		RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
		MaxItems INT,
		CurrentItems INT DEFAULT 0,
		FaceValue INT,
		BestUnder INT DEFAULT 0,
		BestOver INT DEFAULT 1,
		ItemStorageID varchar(10)
	)

-- Aggregate the source data
INSERT		@Data
		(
			MaxItems,
			FaceValue,
			ItemStorageID
		)
SELECT		COUNT(*),
		Qty,
		ItemStorageID
FROM		(
	--this SP will return 2 exactly 2 columns Qty and itemStorageId, i dunno if it allow exec in this part :-
	exec SP_StageData			
		@JobID ='OT01C06020',
		@DocRefID ='DC01C06027',
		@Shipper='jab1my',
		@ItemID='39J1667-H86700',
		@CustomLotNo ='212432'
		) AS d
GROUP BY	Qty, ItemStorageID
ORDER BY	Qty DESC

~~~Focus on problem, not solution~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-20 : 02:19:42

Nope. Not allowed.

1. create a temp table and insert the result of the stored procedure SP_StageData into the temp table.
2. select from the temp table into the table variable @Data

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-20 : 02:33:07

ohh.... thanks :(

~~~Focus on problem, not solution~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-20 : 02:51:54

Btw, is there anyway we could send set of data from Sp to some function to be processed?

~~~Focus on problem, not solution~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-20 : 03:02:57

No. Not that i know off.

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-20 : 04:02:50

khtan.. would u mind to demo how to insert result of SP_StageData into temp table? :(
i dunno whetehr to use

Select col1, col2
from
(select * from sometable) as Tbl
Where ...

or

SELECT col1, col2
INTO ##MyTemp
FROM tblItemDetail d

and also, where should i put this ?
exec SP_StageData
@JobID ='OT01C06020',
@DocRefID ='DC01C06027',
@Shipper='jab1my',
@ItemID='39J1667-H86700',
@CustomLotNo ='212432'

~~~Focus on problem, not solution~~~

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-20 : 04:15:02

To insert output of SP to temp table, you will have to create temp table which exactly match the columns of the SP output and then insert SP output to temp table like below:

Insert #temp
exec SP_StageData 
@JobID ='OT01C06020',
@DocRefID ='DC01C06027',
@Shipper='jab1my',
@ItemID='39J1667-H86700',
@CustomLotNo ='212432'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-20 : 04:18:09

Why do you still persist in using ItemStorageID?
We have had this discussion here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79505
and here is the code only http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80857

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-20 : 04:21:41

Use this technique as suggested to you many times earlier

-- Aggregate the source data
INSERT		@Data
		(
			MaxItems,
			FaceValue
		)
SELECT		COUNT(*),
		OriginQty
		tblItemDetail
WHERE		ItemID = 'flower'
GROUP BY	OriginQty
ORDER BY	OriginQty DESC

If you insist in having a stored procedure fetching the records for you, you will have to change all occurencies of @Stage to #Stage.

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-20 : 04:45:05

hi peso.
sorry if this double entry post, coz i thought maybe someone could solve the exec part..

1.I insisted in using itemstorageid coz this unique id i need to return back so that the actual data in tblitemdetail could be populate back by it's unique id..

2.i need to use the result from the SP_StageData because i have another filtering b4 the autopick process..

3. Here is basically the flow.. or maybe anyone has better idea or faster.. :-

a) Start with Original data from tblitemDetail
b) In the SP_Stagedata : Filter and sort by FIFO/LIFO/FEFo and maybe will change accordingly
c) Result from SP_Stagedata : choose best FIRST combination @WantedValue, in my term PickQty
d) Returm list of itemstorageid that has Best matching @Wantedvalue/PickQty to be used later in displaying the actual data from the tblItemDetail for user

hopefully u will have the idea..

~~~Focus on problem, not solution~~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-20 : 04:49:38

1) Replace @Stage with #Stage
2) INSERT #Stage EXEC MySP

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-20 : 05:07:42

umm?? which part? the one harsh posted?
or maybe u mean change @Data to #Data?

~~~Focus on problem, not solution~~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-20 : 05:20:55

Yes, @Data to #Data.

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-20 : 06:05:16

how about this one : unrecognized object

-- Stage the source data
DECLARE #Data TABLE
(
RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
MaxItems INT,
CurrentItems INT DEFAULT 0,
FaceValue INT,
BestUnder INT DEFAULT 0,
BestOver INT DEFAULT 1,
ItemStorageID varchar(10)
)

~~~Focus on problem, not solution~~~

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-20 : 06:13:49

quote:
Originally posted by maya_zakry

how about this one : unrecognized object

-- Stage the source data
CREATE TABLE #Data (
RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
MaxItems INT,
CurrentItems INT DEFAULT 0,
FaceValue INT,
BestUnder INT DEFAULT 0,
BestOver INT DEFAULT 1,
ItemStorageID varchar(10)
)

~~~Focus on problem, not solution~~~

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-20 : 06:21:24

Thanks Harsh.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-20 : 07:00:14

YOU DON'T NEED ITEMSTORAGEID!
When the algorithm returns the sum, just JOIN to ItemStorageID...

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-20 : 10:26:57

yep yep.. that itemstorageid has been removed actually.. i pasted the old one.. so tomorrow i ll run this.. change declare @data to create #data.. okay got it.. thanks harsh n peso.. will see if i got any stoopid prob again :(

~~~Focus on problem, not solution~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-20 : 21:23:57

hello,
please help.. im stuck again.. :(

1.Peso, im not clear on how can i get back the itemstorageid, if i remove it from the code? Say, after successfully run the code, it return :-

items FaceValue
41 24
1 16

how can i join back to the original table so that i know which itemstorageid has qty=24? do u mean that i need to find 41 records from original table that has total Qty=24 and 1 record with total Qty=16? IF this what u mean, how to perform the select query? if i join the table like this, how to limit the select query to select only 41 records that has total Qty=24?


SELECT	MaxItems  AS TotalPallet,
	FaceValue AS PickQty,
	d.ItemStorageID
	FROM	#Data
        LEFT JOIN tblItemDetail d 
        on FaceValue=d.OriginQty-d.PickQty-d.HoldQty-d.QcQty+d.AdjQty

2. is this correct when i want to send in the Qty = col1-col2-col3 so i need to gruop by all the columns involves in the calcualation:


SELECT
Count(*),
I.OriginQty - I.PickQty - I.BookQty - I.HoldQty - I.QCQty + I.AdjQty as Qty	
FROM tablblabla
GROUP BY I.OriginQty, I.PickQty, I.BookQty , I.HoldQty,  I.QCQty , I.AdjQty 
ORDER BY I.OriginQty DESC

Thanks in advance

~~~Focus on problem, not solution~~~

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources