SQL statement - INSERT INTO and SELECT

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • dhek
    #1

    SQL statement - INSERT INTO and SELECT

    Hi,

    I have a very simple issue: for simplicity lets say I have 2 tables, A and
    B.
    - Table A contains 5 fields. Amongst these there is a 'id'-field which
    is but a reference to table B.
    - Table B contains 2 fields: 'id' and 'text'

    In order to post data to table A I thus (from a known text value that should
    match 1 value in B.text) have to get the value of B.text before performing
    the UPDATE/INSERT statement.

    How is this possible?

    I would have thought something like

    INSERT INTO A (val1, val2, val3, ID, val4)
    VALUES ('x1','x2','x3' , SELECT id FROM B WHERE [SOME TEXT VALUE] = B.text,
    'x4')

    however this is not possible, so I'm lost - not experienced in the arts of
    SQL:-)


    Hope someone can help.

    Best Regards,
    Daniel


    Tags: None
    • Utahduck@hotmail.com
      #2
      Re: SQL statement - INSERT INTO and SELECT

      On Feb 22, 8:57 am, "dhek" <d...@REMOVEvip .cybercity.dkwr ote:
      Hi,
      >
      I have a very simple issue: for simplicity lets say I have 2 tables, A and
      B.
      - Table A contains 5 fields. Amongst these there is a 'id'-field which
      is but a reference to table B.
      - Table B contains 2 fields: 'id' and 'text'
      >
      In order to post data to table A I thus (from a known text value that should
      match 1 value in B.text) have to get the value of B.text before performing
      the UPDATE/INSERT statement.
      >
      How is this possible?
      >
      I would have thought something like
      >
      INSERT INTO A (val1, val2, val3, ID, val4)
      VALUES ('x1','x2','x3' , SELECT id FROM B WHERE [SOME TEXT VALUE] = B.text,
      'x4')
      >
      however this is not possible, so I'm lost - not experienced in the arts of
      SQL:-)
      >
      Hope someone can help.
      >
      Best Regards,
      Daniel
      Try something more like this:

      INSERT INTO ATable(val1, val2, val3, ID, val4)
      SELECT 'x1', 'x2', 'x3', b.id, 'x4'
      FROM BTable b
      WHERE b.Text = ['Your Text Here']

        Comment

        • dhek
          #3
          Re: SQL statement - INSERT INTO and SELECT

          >I have a very simple issue: for simplicity lets say I have 2 tables, A
          >and
          >B.
          > - Table A contains 5 fields. Amongst these there is a 'id'-field
          >which
          >is but a reference to table B.
          > - Table B contains 2 fields: 'id' and 'text'
          >>
          >In order to post data to table A I thus (from a known text value that
          >should
          >match 1 value in B.text) have to get the value of B.text before
          >performing
          >the UPDATE/INSERT statement.
          >>
          >How is this possible?
          >>
          >I would have thought something like
          >>
          >INSERT INTO A (val1, val2, val3, ID, val4)
          >VALUES ('x1','x2','x3' , SELECT id FROM B WHERE [SOME TEXT VALUE] =
          >B.text,
          >'x4')
          >>
          >however this is not possible, so I'm lost - not experienced in the arts
          >of
          >SQL:-)
          >>
          >Hope someone can help.
          >>
          >Best Regards,
          >Daniel
          >
          Try something more like this:
          >
          INSERT INTO ATable(val1, val2, val3, ID, val4)
          SELECT 'x1', 'x2', 'x3', b.id, 'x4'
          FROM BTable b
          WHERE b.Text = ['Your Text Here']
          But this is not possible since table B only contains 2 fields (id, and text)
          or am I misunderstandig u?


            Comment

            • Ed Murphy
              #4
              Re: SQL statement - INSERT INTO and SELECT

              dhek wrote:
              >>I have a very simple issue: for simplicity lets say I have 2 tables, A
              >>and
              >>B.
              >> - Table A contains 5 fields. Amongst these there is a 'id'-field
              >>which
              >>is but a reference to table B.
              >> - Table B contains 2 fields: 'id' and 'text'
              >>>
              >>In order to post data to table A I thus (from a known text value that
              >>should
              >>match 1 value in B.text) have to get the value of B.text before
              >>performing
              >>the UPDATE/INSERT statement.
              >>>
              >>How is this possible?
              >>>
              >>I would have thought something like
              >>>
              >>INSERT INTO A (val1, val2, val3, ID, val4)
              >>VALUES ('x1','x2','x3' , SELECT id FROM B WHERE [SOME TEXT VALUE] =
              >>B.text,
              >>'x4')
              >>>
              >>however this is not possible, so I'm lost - not experienced in the arts
              >>of
              >>SQL:-)
              >>>
              >>Hope someone can help.
              >>>
              >>Best Regards,
              >>Daniel
              >Try something more like this:
              >>
              >INSERT INTO ATable(val1, val2, val3, ID, val4)
              >SELECT 'x1', 'x2', 'x3', b.id, 'x4'
              >FROM BTable b
              >WHERE b.Text = ['Your Text Here']
              >
              But this is not possible since table B only contains 2 fields (id, and text)
              or am I misunderstandig u?
              The SELECT portion only gets one of its five values (b.id) from
              table B; it gets the other four from the values provided directly
              on the SELECT line (which, in practice, might instead be input
              parameters to a stored procedure).

              Consider this hypothetical alternative:

              INSERT INTO ATable(val1, val2, val3, ID, val4)
              SELECT c.x1, c.x2, c.x3, b.id, c.x4
              FROM BTable b
              JOIN Ctable c on b.id = c.id
              WHERE b.Text = ['Your Text Here']

              Obviously x1 through x4 aren't taken from table B in this case. In
              Utahduck's example, x1 through x4 aren't taken from /any/ table.

                Comment

                • dhek
                  #5
                  Re: SQL statement - INSERT INTO and SELECT

                  "Ed Murphy" <emurphy42@soca l.rr.comwrote in message
                  news:45ddcea3$0 $27110$4c368faf @roadrunner.com ...
                  dhek wrote:
                  >
                  >>>I have a very simple issue: for simplicity lets say I have 2 tables, A
                  >>>and
                  >>>B.
                  >>> - Table A contains 5 fields. Amongst these there is a 'id'-field
                  >>>which
                  >>>is but a reference to table B.
                  >>> - Table B contains 2 fields: 'id' and 'text'
                  >>>>
                  >>>In order to post data to table A I thus (from a known text value that
                  >>>should
                  >>>match 1 value in B.text) have to get the value of B.text before
                  >>>performing
                  >>>the UPDATE/INSERT statement.
                  >>>>
                  >>>How is this possible?
                  >>>>
                  >>>I would have thought something like
                  >>>>
                  >>>INSERT INTO A (val1, val2, val3, ID, val4)
                  >>>VALUES ('x1','x2','x3' , SELECT id FROM B WHERE [SOME TEXT VALUE] =
                  >>>B.text,
                  >>>'x4')
                  >>>>
                  >>>however this is not possible, so I'm lost - not experienced in the arts
                  >>>of
                  >>>SQL:-)
                  >>>>
                  >>>Hope someone can help.
                  >>>>
                  >>>Best Regards,
                  >>>Daniel
                  >>Try something more like this:
                  >>>
                  >>INSERT INTO ATable(val1, val2, val3, ID, val4)
                  >>SELECT 'x1', 'x2', 'x3', b.id, 'x4'
                  >>FROM BTable b
                  >>WHERE b.Text = ['Your Text Here']
                  >>
                  >But this is not possible since table B only contains 2 fields (id, and
                  >text) or am I misunderstandig u?
                  >
                  The SELECT portion only gets one of its five values (b.id) from
                  table B; it gets the other four from the values provided directly
                  on the SELECT line (which, in practice, might instead be input
                  parameters to a stored procedure).
                  >
                  Consider this hypothetical alternative:
                  >
                  INSERT INTO ATable(val1, val2, val3, ID, val4)
                  SELECT c.x1, c.x2, c.x3, b.id, c.x4
                  FROM BTable b
                  JOIN Ctable c on b.id = c.id
                  WHERE b.Text = ['Your Text Here']
                  >
                  Obviously x1 through x4 aren't taken from table B in this case. In
                  Utahduck's example, x1 through x4 aren't taken from /any/ table.
                  He maaaaan, I totally get it now and it works like a bloody charm. If I
                  could, I would award u guyz 1000000 points each - I really appreciate it -
                  thanks a lot.

                  Best Regards,
                  Daniel


                    Comment

                    • Utahduck@hotmail.com
                      #6
                      Re: SQL statement - INSERT INTO and SELECT

                      On Feb 22, 10:06 am, Ed Murphy <emurph...@soca l.rr.comwrote:
                      dhek wrote:
                      >I have a very simple issue: for simplicity lets say I have 2 tables, A
                      >and
                      >B.
                      > - Table A contains 5 fields. Amongst these there is a 'id'-field
                      >which
                      >is but a reference to table B.
                      > - Table B contains 2 fields: 'id' and 'text'
                      >
                      >In order to post data to table A I thus (from a known text value that
                      >should
                      >match 1 value in B.text) have to get the value of B.text before
                      >performing
                      >the UPDATE/INSERT statement.
                      >
                      >How is this possible?
                      >
                      >I would have thought something like
                      >
                      >INSERT INTO A (val1, val2, val3, ID, val4)
                      >VALUES ('x1','x2','x3' , SELECT id FROM B WHERE [SOME TEXT VALUE] =
                      >B.text,
                      >'x4')
                      >
                      >however this is not possible, so I'm lost - not experienced in the arts
                      >of
                      >SQL:-)
                      >
                      >Hope someone can help.
                      >
                      >Best Regards,
                      >Daniel
                      Try something more like this:
                      >
                      INSERT INTO ATable(val1, val2, val3, ID, val4)
                      SELECT 'x1', 'x2', 'x3', b.id, 'x4'
                      FROM BTable b
                      WHERE b.Text = ['Your Text Here']
                      >
                      But this is not possible since table B only contains 2 fields (id, and text)
                      or am I misunderstandig u?
                      >
                      The SELECT portion only gets one of its five values (b.id) from
                      table B; it gets the other four from the values provided directly
                      on the SELECT line (which, in practice, might instead be input
                      parameters to a stored procedure).
                      >
                      Consider this hypothetical alternative:
                      >
                      INSERT INTO ATable(val1, val2, val3, ID, val4)
                      SELECT c.x1, c.x2, c.x3, b.id, c.x4
                      FROM BTable b
                      JOIN Ctable c on b.id = c.id
                      WHERE b.Text = ['Your Text Here']
                      >
                      Obviously x1 through x4 aren't taken from table B in this case. In
                      Utahduck's example, x1 through x4 aren't taken from /any/ table.
                      This is correct. You don't need to "select" from any table. You can
                      even do things like:

                      SELECT GetDate() -- Get the date... no tables involved at all
                      SELECT 'I got this from ATable', * FROM ATable -- I do this quite
                      often when merging several tables into one so I know the source
                      SELECT 2+2 -- Just in case you forget what that comes to. :D
                      SELECT 'Hello World!' -- I do this quite often as a form of
                      troubleshooting , thought it more closely resembles SELECT 'Finished
                      Step #7'

                      Hope that helps!

                        Comment

                        • dhek
                          #7
                          Re: SQL statement - INSERT INTO and SELECT

                          <Utahduck@hotma il.comwrote in message
                          news:1172172266 .057991.105090@ a75g2000cwd.goo glegroups.com.. .
                          On Feb 22, 10:06 am, Ed Murphy <emurph...@soca l.rr.comwrote:
                          >dhek wrote:
                          >>I have a very simple issue: for simplicity lets say I have 2 tables,
                          >>A
                          >>and
                          >>B.
                          >> - Table A contains 5 fields. Amongst these there is a 'id'-field
                          >>which
                          >>is but a reference to table B.
                          >> - Table B contains 2 fields: 'id' and 'text'
                          >>
                          >>In order to post data to table A I thus (from a known text value that
                          >>should
                          >>match 1 value in B.text) have to get the value of B.text before
                          >>performing
                          >>the UPDATE/INSERT statement.
                          >>
                          >>How is this possible?
                          >>
                          >>I would have thought something like
                          >>
                          >>INSERT INTO A (val1, val2, val3, ID, val4)
                          >>VALUES ('x1','x2','x3' , SELECT id FROM B WHERE [SOME TEXT VALUE] =
                          >>B.text,
                          >>'x4')
                          >>
                          >>however this is not possible, so I'm lost - not experienced in the
                          >>arts
                          >>of
                          >>SQL:-)
                          >>
                          >>Hope someone can help.
                          >>
                          >>Best Regards,
                          >>Daniel
                          >Try something more like this:
                          >>
                          >INSERT INTO ATable(val1, val2, val3, ID, val4)
                          >SELECT 'x1', 'x2', 'x3', b.id, 'x4'
                          >FROM BTable b
                          >WHERE b.Text = ['Your Text Here']
                          >>
                          But this is not possible since table B only contains 2 fields (id, and
                          text)
                          or am I misunderstandig u?
                          >>
                          >The SELECT portion only gets one of its five values (b.id) from
                          >table B; it gets the other four from the values provided directly
                          >on the SELECT line (which, in practice, might instead be input
                          >parameters to a stored procedure).
                          >>
                          >Consider this hypothetical alternative:
                          >>
                          >INSERT INTO ATable(val1, val2, val3, ID, val4)
                          >SELECT c.x1, c.x2, c.x3, b.id, c.x4
                          >FROM BTable b
                          > JOIN Ctable c on b.id = c.id
                          >WHERE b.Text = ['Your Text Here']
                          >>
                          >Obviously x1 through x4 aren't taken from table B in this case. In
                          >Utahduck's example, x1 through x4 aren't taken from /any/ table.
                          >
                          This is correct. You don't need to "select" from any table. You can
                          even do things like:
                          >
                          SELECT GetDate() -- Get the date... no tables involved at all
                          SELECT 'I got this from ATable', * FROM ATable -- I do this quite
                          often when merging several tables into one so I know the source
                          SELECT 2+2 -- Just in case you forget what that comes to. :D
                          SELECT 'Hello World!' -- I do this quite often as a form of
                          troubleshooting , thought it more closely resembles SELECT 'Finished
                          Step #7'
                          >
                          Hope that helps!
                          It all help indeed of my lacking understanding of what is possible and what
                          is not. This clearifies a great deal and makes my life much easier. I'm no
                          longer a troubled man:-)

                          Thanks again for all your help - I really appreciate it.

                          Best Regards,
                          Daniel


                            Comment

                            Previous template Next
                            Working...