Select a list of fields from one table and use it in a Insert into statement

Question

0.00/5 (No votes)

See more:

Hi
I currently have a table called Rectype
this holds field values.

based on a field called recordtype is how the fields that need to be inserted are determined.

Is it possible for me to do a select into statement using a select statement

e.g. Insert into Table A (select fieldvalue from RecType order by Seq) values(Value1,Value2,Value3)?

table rectype shows fieldnames for Table A

So in rectype i have the following values for example
RECType Fieldname Seq
A Field1 1
A Field2 2
A Field3 3
B Field2 1
B Field3 2

Etc..

Table A (Format)
Field1 field2 field3

So instead of me needing to manually check the fields that Rec type has when i run my insert statement i want to use a select statement so that sql will select the correct values

Posted 8-May-14 22:27pm

isi19

Updated 8-May-14 22:55pm

v2

Add a Solution

3 solutions

Solution 1

Definitely possible see the links. Can find more if you google it.

http://www.w3schools.com/sql/sql_insert_into_select.asp[^]

http://www.techonthenet.com/sql/insert.php[^]

Posted 8-May-14 22:30pm

ArunRajendra

Comments

isi19 9-May-14 4:54am

table rectype shows fieldnames for Table A

So in rectype i have the following values for example
RECType Fieldname Seq
A Field1 1
A Field2 2
A Field3 3
B Field2 1
B Field3 2

Etc..

Table A (Format)
Field1 field2 field3

So instead of me needing to manually check the fields that Rec type has when i run my insert statement i want to use a select statement so that sql will select the correct values

CHill60 9-May-14 4:57am

Just spotted your comment - does the example in my solution help?

Solution 3

eg:

SQL

INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country='india';

Posted 8-May-14 22:56pm

Kan07

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2014-05-08T22:54:00

Further to Solution 1 I thought a couple of examples might help ...

Scenario 1:

All of the columns I need for TableA are in TableB
- I only need some of the rows
- but TableB has more columns than TableA
- And they have different names!
Example of Insert:

INSERT INTO TableA (SELECT Col1, Col3, Col7 FROM TableB WHERE RecType = 2)

Scenario 2:

Similar to 1 above but one column in TableA needs to be set to a specific value depending on a value in column RecType ...

SQL

INSERT INTO TableA (SELECT Col1, Col3,
                        CASE WHEN RecType IN (1,3,4) THEN 'Morgen'
                             WHEN RecType = 5 THEN 'Tag'
                             ELSE 'Abend' END
                    FROM TableB)

Scenario 3:

Some columns I need are TableB AND TableC
- but I already have data in TableA and I just want to update the rows

SQL

UPDATE A        -- note this is the table alias here not the table name
SET AColumnX = B.ColX, AColumnY = C.ColY
FROM TableA AS A
INNER JOIN TableB AS B ON A.PKey = B.FKtoA  -- this is totally made up for the example!
INNER JOIN TableC AS C ON B.PKey = C.FKtoA  -- as is this!

There are lots of different scenarios that could arise I just thought that a taster might help.