SQL statement - INSERT INTO and SELECT

#1

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

#2

On Feb 22, 8:57 am, "dhek" <d... (AT) REMOVEvip (DOT) cybercity.dk> wrote:

Quote:

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

#3

Quote:

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?

#4

dhek wrote:

Quote:

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.

#5

"Ed Murphy" <emurphy42 (AT) socal (DOT) rr.com> wrote

Quote:

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

#6

On Feb 22, 10:06 am, Ed Murphy <emurph... (AT) socal (DOT) rr.com> wrote:

Quote:

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.

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!

#7

<Utahduck (AT) hotmail (DOT) com> wrote

Quote:

On Feb 22, 10:06 am, Ed Murphy <emurph... (AT) socal (DOT) rr.com> wrote:
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.

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SQL statement - INSERT INTO and SELECT

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

SQL statement - INSERT INTO and SELECT - 02-22-2007 , 09:57 AM

Re: SQL statement - INSERT INTO and SELECT - 02-22-2007 , 10:10 AM

Re: SQL statement - INSERT INTO and SELECT - 02-22-2007 , 10:40 AM

Re: SQL statement - INSERT INTO and SELECT - 02-22-2007 , 11:06 AM

Re: SQL statement - INSERT INTO and SELECT - 02-22-2007 , 12:03 PM

Re: SQL statement - INSERT INTO and SELECT - 02-22-2007 , 01:24 PM

Re: SQL statement - INSERT INTO and SELECT - 02-23-2007 , 12:38 AM