Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Inserting Using Results From Function??

Status
Not open for further replies.
jiggyg

jiggyg

Programmer
Oct 1, 2007
61
US
Hello!

I have an INSERT that I'm trying to write that I could use some input on from experts...

I run this function:
Code: 
select *
from getInvalidBmkAssignments(153)

And, as an example, it returns the following:
Record1
LocId: 159937
LocClientID: 153
ClientName: MyClient
L6: NULL
L5: NULL
L4: NULL
L3: Center
L2: 1st Floor
L1: Level 01
configuredUses: NULL
validConfigured: INVALID
currentUses: NULL
validCurrent: INVALID
confUseType: NULL
currentUseType: NULL

Record2
LocId: 159938
LocClientID: 153
ClientName: MyClient
L6: NULL
L5: NULL
L4: NULL
L3: Center
L2: 1st Floor
L1: Level 01
configuredUses: NULL
validConfigured: INVALID
currentUses: NULL
validCurrent: INVALID
confUseType: NULL
currentUseType: NULL


Now, I want to do an INSERT into another table for these records.

bmkAssignments table columns:
baID
baClientID
baPDID
baLocID
baConfiguredUse
baCurrentUse
baCurrentCapacity
baRefurnCapacity
baSupportCapacity

So, into the bmkAssignment table I want to make the following inserts/values:
baID: autogenerated, don't need to worry about
baClientID: 153 (static in this example & is also returned from function - 'LocClientID: 153')
baPDID: 16
baLocID: <value returned from function - LocId: 159937/159938>
baConfiguredUse: 208
baCurrentUse: 208
baCurrentCapacity: 0
baRefurnCapacity: 0
baSupportCapacity: 0


Obviously, I know how to do the 'static' numbers (baPDID, baConfiguredUses, etc); I'm just not sure on how to get the baLocID = to the LocID from the function....
Code: 
INSERT INTO bmkAssignments (baClientID, baPDID, baLocID, baConfiguredUse, baCurrentUse, baCurrentCapacity, baRefurnCapacity, baSupportCapacity)
VALUES ('153', '16', ????, '208', '208', '0', '0', '0')

Thanks in advance for your time and help once again!
-jiggyg
 
Sort by date Sort by votes
Code: 
insert into bmkAssignments (baClientID, baPDID, baLocID, baConfiguredUse, baCurrentUse, baCurrentCapacity, baRefurnCapacity, baSupportCapacity)
select '153', '16', LocId, '208', '208', '0', '0', '0' from getInvalidBmkAssignments(153)

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]
 
Upvote 0 Downvote
Hmmmm....This just got a bit more complicated. I have to also use another table to find those w/ LocStationType = 99 that are also in the result set returned from the function..

So, I have another table 'Locations' w/ these columns:
LocID
LocClientID
LocLinkID
LocName
LocDescription
LocParentID
LocHierarchyLevel
LocCostCenterID
LocHasChildren
LocOccupied
LocAllowMultOcc
LocStationType
LocDrawing
LocCreatedBy
LocCreatedDate
:
:


So, I want those records that are returned from the function where the Locations.LocID = getInvalidBmkAssignments.LocID AND locations.LocStationType = 99 --> THEN take those results and insert them into the bmkAssignments table as described in my first post...

Does that make sense, yet sound confusing enough? :)

Thanks in advance for your time and assistance and expertise!
-jiggyg
 
Upvote 0 Downvote
Well, I got my result set w/ this query:

Code: 
SELECT * FROM [MasterDetails].[dbo].getInvalidBmkAssignments(153)
WHERE getInvalidBmkAssignments.LocID IN 
(
	SELECT LocID 
	FROM [Production].[dbo].Locations 
	WHERE LocStationType = 99 
	AND LocClientID = 153
)

Now, back to how to insert this into the bmkAssignments table (that is in the [MasterDetails] database....
 
Upvote 0 Downvote
So, something like this:

Code: 
INSERT INTO [MasterDetails].[dbo].bmkAssignments(baClientID, baPDID, baLocID, baConfiguredUse, baCurrentUse, baCurrentCapacity, baRefurnCapacity, baSupportCapacity)
VALUES (fnLocClientID, '16', fnLocID, '208', '208', '0', '0', '0')
(
SELECT LocID as fnLocID, LocClientID as fnLocClientID FROM [MasterDetails].[dbo].getInvalidBmkAssignments(153)
WHERE getInvalidBmkAssignments.LocID IN 
(
	SELECT LocID 
	FROM [41Where-Production].[dbo].Locations 
	WHERE LocStationType = 99
)
)

But, without these errors :)

The name "fnLocClientID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.


Thanks
 
Upvote 0 Downvote
Did you know that there are 2 ways of writing insert into?

Ex:

Insert Into Table (Col1, Col2, Col3)
Values ('A', 'B', 'C')

Or

Insert Into Table(Col1, Col2, Col3)
Select ColA, Colb, ColC
From SomeOtherTable
Where blah blah blah

Please, try to apply this advice to your query. If you continue to get stumped by it, let me know.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Thanks for the replies!

I ended up doing this w/ cursors, since some of the values I wanted to insert were pulled from columns, and some were hard-coded values...

I may have made this more difficult than I needed (I have a tendency to do that), but I got it to work!

Here's my code; hopefully it can help someone else in the future!

Code: 
--Declare two variables
DECLARE @LocID numeric(18)
DECLARE @LocClientID numeric (18)

--c1 cursor to fetch/store LocID
DECLARE c1 CURSOR
FOR
SELECT LocID FROM [MasterDetails].[dbo].getInvalidBmkAssignments(153)
WHERE getInvalidBmkAssignments.LocID IN 
(
	SELECT LocID 
	FROM [Production].[dbo].Locations 
	WHERE LocStationType = 99
)

OPEN c1

--store value in variable @LocID
FETCH NEXT FROM c1
INTO @LocID

--c2 cursor to fetch/store LocClientID
DECLARE c2 CURSOR
FOR
SELECT LocClientID FROM [MasterDetails].[dbo].getInvalidBmkAssignments(153)
WHERE getInvalidBmkAssignments.LocID IN 
(
	SELECT LocID 
	FROM [Production].[dbo].Locations 
	WHERE LocStationType = 99
)

OPEN c2

--store value in variable @LocClientID
FETCH NEXT FROM c2
INTO @LocClientID

--While there are more records, insert cursor values (@LocID & @LocClientID) & hard-coded values into the table
WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO [MasterDetails].[dbo].bmkAssignments(baClientID, baPDID, baLocID, baConfiguredUse, baCurrentUse, baCurrentCapacity,
		baRefurnCapacity, baSupportCapacity)
	VALUES (@LocClientID, '16', @LocID, '208', '208', '0', '0', '0')

        --Fetch next record
	FETCH NEXT FROM c1
	INTO @LocID

	FETCH NEXT FROM c2
	INTO @LocClientID
END

--Close and deallocate both cursors
CLOSE c1
DEALLOCATE c1

CLOSE c2
DEALLOCATE c2

-jiggyg
 
Upvote 0 Downvote
You said this works:

Code: 
SELECT * FROM [MasterDetails].[dbo].getInvalidBmkAssignments(153)
WHERE getInvalidBmkAssignments.LocID IN
(
    SELECT LocID
    FROM [Production].[dbo].Locations
    WHERE LocStationType = 99
    AND LocClientID = 153
)

Now, you want to insert data in to another table.
But this doesn't work:
[tt][blue]
INSERT INTO [MasterDetails].[dbo].bmkAssignments(baClientID, baPDID, baLocID, baConfiguredUse, baCurrentUse, baCurrentCapacity, baRefurnCapacity, baSupportCapacity)
VALUES (fnLocClientID, '16', fnLocID, '208', '208', '0', '0', '0')
(
SELECT LocID as fnLocID, LocClientID as fnLocClientID FROM [MasterDetails].[dbo].getInvalidBmkAssignments(153)
WHERE getInvalidBmkAssignments.LocID IN
(
SELECT LocID
FROM [41Where-Production].[dbo].Locations
WHERE LocStationType = 99
)
)
[/blue][/tt]

Try this instead.
Code: 
INSERT INTO [MasterDetails].[dbo].bmkAssignments(baClientID, baPDID, baLocID, baConfiguredUse, baCurrentUse, baCurrentCapacity, baRefurnCapacity, baSupportCapacity)
SELECT LocID as fnLocID, 
       '16'
       LocClientID as fnLocClientID,
       '208', '208', '0', '0', '0'
FROM   [MasterDetails].[dbo].getInvalidBmkAssignments(153)
WHERE getInvalidBmkAssignments.LocID IN
(
    SELECT LocID
    FROM [41Where-Production].[dbo].Locations
    WHERE LocStationType = 99
)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
You know who likes cursors, right?



Ahh.... nevermind.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]
 
Upvote 0 Downvote
Who? Celko (
celko said:
>> I have SP, which has a cursor iterations. Need to call another SP for
every loop iteration of the cursor. <<

No. You need to learn to program in SQL. All you are doing is
mimicing a 1960's 3GL magnetic tape file system . In your pseudo
code, you even refer to fields instead of columns! You put the "sp_"
prefix on procedure names!

Don't you understand that SQL is a non-procedurdal language? You
should write only a few cursors in 20 years, not two in one
application.

Your whole approach to the problem is **fundamentally** wrong.
Click to expand...

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions
 
Upvote 0 Downvote
CURSORS ARE EVIL.

This is not a joke. You need to learn to think set-based and write your queries that way. NEVER use a cursor again until you check with an experienced expert and he tells you that it's the only way to perform the query.

All your cursoring can be reduced to this insert query:

Code: 
INSERT [MasterDetails].[dbo].bmkAssignments(baClientID, baPDID, baLocID, baConfiguredUse, baCurrentUse, baCurrentCapacity, baRefurnCapacity, baSupportCapacity)
SELECT A.LocClientID, '16', A.LocID, '208', '208', '0', '0', '0'
FROM
   [MasterDetails].[dbo].getInvalidBmkAssignments(153) A
   INNER JOIN [Production].[dbo].Locations L ON getInvalidBmkAssignments.LocID = L.LocID
WHERE
   LocStationType = 99
Do a SQL trace on your cursored version and on this and compare the total CPU, Reads, Writes, and Duration. I think you will be surprised just how badly the cursors perform.

By the way, you can get MORE than one value into a variable from a cursor at once. Why on earth would you use two cursors?

Code: 
FETCH NEXT FROM c1 INTO @LocID, @LocClientID
Of course, DON'T USE THAT.

Using cursors when set-based approaches are available is like heating 768 teaspoons of water, one at a time, and pouring them into a pan, instead of just heating the whole gallon in the pan at once. I'm sure you can imagine how much longer it would take you to do the teaspoons individually.

...obviously other people beat me to the punch and posted first, but I'm going to post anyway.
 
Upvote 0 Downvote
ESquared said:
...obviously other people beat me to the punch and posted first, but I'm going to post anyway.
Click to expand...

I'm glad you did. I had no idea that there are 768 teaspoons in a gallon. [bigsmile]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Thanks for all the info!

Why did I do it w/ cursors like I did??? Because I was working on this all morning and finally got a solution that worked, so I used it! That, and I'm still very green to SQL, so whenever I get something to work, I run w/ it...

Oh, but I did try to execute Esquared's solution (checking execution time, etc), but got an error w/ it:

The multi-part identifier "getInvalidBmkAssignments.LocID" could not be bound.

Again, thanks for all the info and suggestions! I'm trying to learn to be better/more efficient w/ my queries, and I'll be the first to tell you that I'm very green w/ it all!
-jiggyg
 
Upvote 0 Downvote
Try my query, dated: 26 Oct 07 14:59



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
change this getInvalidBmkAssignments.LocID
to A.LocID

That is the alias he gave the table. I think the table name spelled out may not be the correct name of the table.

"NOTHING is more important in a database than integrity." ESquared
 
Upvote 0 Downvote
Why did I do it w/ cursors like I did??? Because I was working on this all morning and finally got a solution that worked, so I used it!
Click to expand...

You know who else gets it to work and runs with it?

That's right, you know who:

unclerico.JPG


The fact that you're very green to SQL makes it even that more important that you do it the right way. It's very hard to get away from bad habits if they are enforced when you are first learning. It's taken me about 3 years to unlearn what our pal uncle rico taught me when I first started SQL.

As ESquared pointed out above, it's ok to use a cursor every now and then when it's appropriate. But when that's all you know how to do, you end up with procedures that look like this:

Cursors gone wild!!

Look at the tongue lashing our pal rico gets from all the other experts. Is this what you want your code to look like in 10 years?? That's right, the person that wrote the code in that link has been working with SQL for 10 years.....

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]
 
Upvote 0 Downvote
I got these two to insert correctly:

Code: 
INSERT [MasterDetails].[dbo].bmkAssignments(baClientID, baPDID, baLocID, baConfiguredUse, baCurrentUse, baCurrentCapacity, baRefurnCapacity, baSupportCapacity)
SELECT A.LocClientID, '16', A.LocID, '208', '208', '0', '0', '0'
FROM
   [MasterDetails].[dbo].getInvalidBmkAssignments(153) A
   INNER JOIN [Production].[dbo].Locations L ON A.LocID = L.LocID
WHERE
   LocStationType = 99

and
Code: 
INSERT INTO [MasterDetails].[dbo].bmkAssignments(baClientID, baPDID, baLocID, baConfiguredUse, baCurrentUse, baCurrentCapacity, baRefurnCapacity, baSupportCapacity)
SELECT LocClientID, 
       '16',
       LocID,
       '208', '208', '0', '0', '0'
FROM   [MasterDetails].[dbo].getInvalidBmkAssignments(153)
WHERE getInvalidBmkAssignments.LocID IN
(
    SELECT LocID
    FROM [Production].[dbo].Locations
    WHERE LocStationType = 99
)

And, am much more informed than I was a few hours ago!

Thanks,
-jiggyg
 
Upvote 0 Downvote
I love the 768 teaspoons analogy

"NOTHING is more important in a database than integrity." ESquared
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Cpreston
  • Locked
  • Question
Group results into one row 3
2
Replies
37
Views
2K
Chris Miller
Chris Miller
bharons
  • Locked
  • Question
Pick up value from combobox
Replies
0
Views
525
bharons
bharons
grnzbra
  • Locked
  • Question
Create Function Within IF statement. 1
Replies
4
Views
1K
mikrom
mikrom
briangriffin
  • Locked
  • Question
Function Returning Rowcounts
Replies
1
Views
243
briangriffin
briangriffin
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
501
Mike Lewis
Mike Lewis

Part and Inventory Search

Sponsor

Back
Top