October 13, 2009 at 3:30 pm
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.
October 13, 2009 at 3:41 pm
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
October 13, 2009 at 4:14 pm
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.
October 13, 2009 at 4:32 pm
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
October 13, 2009 at 4:47 pm
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...
October 13, 2009 at 5:52 pm
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... 🙂
October 14, 2009 at 1:07 pm
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://qa.sqlservercentral.com/Forums/FindPost247716.aspx,
http://qa.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. 😉
October 24, 2009 at 10:28 am
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!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply