Doing inserts within a select statement?

Question

Doing inserts within a select statement?

erikpoker

Ten Centuries

Points: 1135
More actions
January 4, 2012 at 3:36 am

#262353

Hello,
Im selecting values from a table a:
SELECT a.col1, a.col2, a.col3, a.foreignkey-value
FROM mytable a
WHERE something=something
If the foreign-key-value is null i would like to insert it into the parent table on the fly. I thought i could use a function for this, but apparantly this is not the case since functions wont accept inserts and functions cant call a sp.
One way would be to first select all null values and then do insert of them. And then do my select. But i cant do it this way. Why you might ask? Well its really a bit complicated to explain, so if you could please just try and help me to find a way to do it within the select query i would really appriciate it.
Thanks for your help!

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You can't insert within a select. You can have a select within an insert (INSERT ... SELECT), or select the output of an insert (via the OUTPUT clause), but a select statement is just that - select. If you want to make any modifications to the data you'll need an insert, update or delete (or merge)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

erikpoker Ten Centuries Points: 1135 More actions · Answer 2

I just heard from a collegue that this can in fact be done. With the help of openrowset. Dunno the details yet, but i'll post back if i find out.

sami.sqldba Ten Centuries Points: 1248 More actions · Answer 3

Hi

I dont know how much this gonna benifite you just try if you this can make thing quite ok

DECLARE @FKVALUE INT

SET @FKVALUE = 1

INSERT INTO TABLE2(COLUMN1,COLUMN2)

SELECT COLUMN1,ISNULL(COLUMN2,@FKVALUE) FROM TABLE1

Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com

Jeff Moden SSC Guru Points: 1003943 More actions · Answer 4

erikpoker (1/4/2012)
Hello,
Im selecting values from a table a:
SELECT a.col1, a.col2, a.col3, a.foreignkey-value
FROM mytable a
WHERE something=something
If the foreign-key-value is null i would like to insert it into the parent table on the fly. I thought i could use a function for this, but apparantly this is not the case since functions wont accept inserts and functions cant call a sp.
One way would be to first select all null values and then do insert of them. And then do my select. But i cant do it this way. Why you might ask? Well its really a bit complicated to explain, so if you could please just try and help me to find a way to do it within the select query i would really appriciate it.
Thanks for your help!

Perhaps the MERGE statement would do what you ask.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

erikpoker Ten Centuries Points: 1135 More actions · Answer 5

Thanks for your replies.

A merge is not gonna work for me. What i'm looking for is a way to use a function and insert something somewhere within the function.

And that is possible. I got a solution working just now. I have defined a function "getForeignKey" and this function will take the a.foreignKey-value as an input, check if it is null and if it is null it will call a procedure "InsertMissingMember" to insert the value and return it to the select.

To be able to call the procedure i had to use openrowset.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 6

erikpoker (1/4/2012)
Thanks for your replies.
A merge is not gonna work for me. What i'm looking for is a way to use a function and insert something somewhere within the function.
And that is possible. I got a solution working just now. I have defined a function "getForeignKey" and this function will take the a.foreignKey-value as an input, check if it is null and if it is null it will call a procedure "InsertMissingMember" to insert the value and return it to the select.
To be able to call the procedure i had to use openrowset.

If a foreign key in a row of your table is NULL, how can you tell what value it's likely to be?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

erikpoker Ten Centuries Points: 1135 More actions · Answer 7

ChrisM@home (1/4/2012)
If a foreign key in a row of your table is NULL, how can you tell what value it's likely to be?

Well i simplified the first query a bit.

The foreign key is selected using a subquery of the table-values. Like this:

SELECT a.col1, (select key from parenttable where something = a.col2 and somethingelse = a.col3) as foreignkey, a.col5 FROM table a

So if the foreign key is null, well then i would just like to insert a.col2 and a.col3 into the parent-table and get an autogenerated primarykey back.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

erikpoker (1/4/2012)
So if the foreign key is null, well then i would just like to insert a.col2 and a.col3 into the parent-table and get an autogenerated primarykey back.

That part is easy enough, but it requires an INSERT statement with the OUTPUT clause, it can't be done automatically as part of a select statement.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

erikpoker Ten Centuries Points: 1135 More actions · Answer 9

Yes it can. Like i just described. Using a scalar-function inside the select like this:

SELECT

a.col1,

getForeignKey(a.col2, a.col3) as foreignKey,

a.col4,

a.col5

FROM table a

And within the getForeignKey function i can use openrowset to call a procedure to insert the value for me 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Potentially resulting in duplicate key errors, DTC transaction problems, inconsistent behaviour and other such fun.

If you want to go that way, it's your database and your application, but that is strongly not recommended for a number of reasons. SQL depends on functions not having side effects for decision during both query optimisation and query execution.

There's a reason that option was not suggested.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323507 More actions · Answer 11

erikpoker (1/4/2012)
Yes it can. Like i just described. Using a scalar-function inside the select like this:
SELECT
a.col1,
getForeignKey(a.col2, a.col3) as foreignKey,
a.col4,
a.col5
FROM table a
And within the getForeignKey function i can use openrowset to call a procedure to insert the value for me 🙂

a FUNCTION cannot insert data in SQL server, it is a specialized kind of procedure which is limited to returning data, and cannot use openrowset, insert data, or dynamic sql, among many other limitations.

a procedure can, though, but you cannot use a procedure inline like you are using for an example;

you'll want to use a procedure to do both the isnert into the maste rlist table that will generate the foerign key, and will take at least two distinct commands (best to use the OUTPUT functionality, as previously described.

if the value definitely already exists, you can use the getForeign function, no problem, but it cannot add new values (as a function)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

erikpoker Ten Centuries Points: 1135 More actions · Answer 12

Yes i know its not the "vanilla" way to go. But unfortunalty i am left with no other option as far as i can see... 🙁

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

erikpoker (1/4/2012)
Yes i know its not the "vanilla" way to go. But unfortunalty i am left with no other option as far as i can see... 🙁

It's far more than not 'vanilla', it's downright dangerous. As I said, your database, your application, so if you want to do it fine, I certainly would not consider doing something like that on any system I'm responsible for)

Since you haven't given us any idea why the 'one select statement' constraint exists, we can't help you with alternative suggestions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

GilaMonster (1/4/2012)
erikpoker (1/4/2012)
Yes i know its not the "vanilla" way to go. But unfortunalty i am left with no other option as far as i can see... 🙁
It's far more than not 'vanilla', it's downright dangerous. As I said, your database, your application, so if you want to do it fine, I certainly would not consider doing something like that on any system I'm responsible for)
Since you haven't given us any idea why the 'one select statement' constraint exists, we can't help you with alternative suggestions.

+1 trillion