How to insert using values from query results for one field of statement

Question

How to insert using values from query results for one field of statement

SQLUserC

SSCrazy

Points: 2345
More actions
October 13, 2009 at 3:30 pm

#212174

Hello,
I am trying to insert notes into a notes table for people meeting certain criteria that I specified using a query. The query yields a list of IDs. I want to write an insert statement that uses explicitly specified values for most of the fields in the notes table, but it should cycle through the IDs for the results of the aforementioned query so that the notes are associated with the people. There are a lot of people, so manually writing out the insert statements would be tedious.
Does anyone know how I can do this? I would appreciate any advice that anyone can give. Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Bradley Jacques Ten Centuries Points: 1301 More actions · Answer 1

Can you post the Query and a sample of the data pulled? I think I see what you are asking, have you tried a select into clause?

Thanks,

Bradley Jacques

SQLUserC SSCrazy Points: 2345 More actions · Answer 2

Hey,

Thanks for your reply. The query is of the form:

select personID from people

where height = 'X'

and weight = 'Y'

The result is just a list of IDs (e.g. 1, 5, 10, 20, 52, etc.).

The issue with select into is that, as I understand it, this creates a new table with the results, whereas I want to use the results to insert rows into an existing notes table.

atharmon SSC Rookie Points: 28 More actions · Answer 3

Can you give an example of the insert you're trying to do? Since I deal primarily with Oracle and am a backup on SQL Server my knowledge is limited in what SQL Server is capable of, but it looks like good ole' fashioned cursor processing will be needed.

--Tony

LutzM SSC Guru Points: 107049 More actions · Answer 4

cjohn5552 (10/13/2009)
Hey,
Thanks for your reply. The query is of the form:
select personID from people
where height = 'X'
and weight = 'Y'
The result is just a list of IDs (e.g. 1, 5, 10, 20, 52, etc.).
The issue with select into is that, as I understand it, this creates a new table with the results, whereas I want to use the results to insert rows into an existing notes table.

Something like this?

INSERT INTO target_table (target_column,target_fixcol1)

SELECT personID ,'fixVal1'

FROM people

WHERE height = 'X'

AND weight = 'Y'

Edit: @tony-2: When working with SQL Server a cursor should be the very last option to use, since it's also know as RBAR (row-by-agonizing-row), causing poor performance.

Basically, this statement is true for any relational database (including ORACLE). But it seems like the ORACLE community is addicted to cursors...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

atharmon SSC Rookie Points: 28 More actions · Answer 5

I do appreciate result set processing over row level processing, but performance would really depend on number or rows. And given that the Oracle guys are IBM guys I don't see them leaving the row level mindset... 🙂

LutzM SSC Guru Points: 107049 More actions · Answer 6

atharmon (10/13/2009)
I do appreciate result set processing over row level processing, but performance would really depend on number or rows.

Usually, such statement calls for some sample code to prove it... But I think the "cursor issue" is a discussion being held on many other threads before.

A few examples out of many: http://www.sqlservercentral.com/Forums/FindPost247716.aspx,

http://www.sqlservercentral.com/articles/Advanced+Querying/2785/,

and, of course, Barry's (unfortunately unfinished) series of articles There Must Be 15 Ways To Lose Your Cursors... [/url]. I think we shouldn't go deeper into the "cursor discussion" in this thread since it's a little bit off topic (at least from my point of view) and all solutions (either cursor or set based) will miss one thing: a more detailed description of what the OP actually is looking for. 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLUserC SSCrazy Points: 2345 More actions · Answer 7

lmu92 (10/13/2009)
cjohn5552 (10/13/2009)
Hey,
Thanks for your reply. The query is of the form:
select personID from people
where height = 'X'
and weight = 'Y'
The result is just a list of IDs (e.g. 1, 5, 10, 20, 52, etc.).
The issue with select into is that, as I understand it, this creates a new table with the results, whereas I want to use the results to insert rows into an existing notes table.
Something like this?
INSERT INTO target_table (target_column,target_fixcol1)
SELECT personID ,'fixVal1'
FROM people
WHERE height = 'X'
AND weight = 'Y'

Yes, this does the sort of thing I want to do. Thanks very much for your reply!