Inserting Using Results From Function??

[jiggyg]

Hello!

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

I run this function:

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....

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

 

[kaht]

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]

 

[jiggyg]

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

 

[jiggyg]

Well, I got my result set w/ this query:

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....

 

[jiggyg]

So, something like this:

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

 

[gmmastros]

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

 

[jiggyg]

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!

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

 

[gmmastros]

You said this works:

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.

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

 

[kaht]

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]

 

[SQLDenis]

Who? Celko (

http://www.youtube.com/watch?v=o6nMtgij2OA)

>> 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.

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

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:

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?

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.

 

[gmmastros]

...obviously other people beat me to the punch and posted first, but I'm going to post anyway.

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

-George

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

 

[SQLDenis]

the problem with the teaspoons is that you also have to keep the onse you already processed still heated (compare it to a lock in SQL)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[jiggyg]

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

 

[gmmastros]

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

 

[SQLSister]

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

 

[kaht]

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!

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

That's right, you know who:

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]

 

[jiggyg]

I got these two to insert correctly:

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

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

 

[SQLSister]

I love the 768 teaspoons analogy

"NOTHING is more important in a database than integrity." ESquared

 

[SQLDenis]

I love the 768 teaspoons analogy

Stop that, aren't you supposed to be cranky?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions