Insert data into one table column with multiple values of another table column

Question

Post reply

Insert data into one table column with multiple values of another table column

MrCapuchino

Mr or Mrs. 500

Points: 535
More actions
July 16, 2010 at 7:38 pm

#221357

Hello,
I have a problem that I just cant resolve.
What I want to do is this:
I want to insert to a table one value but with multiple values from another table column resulting in a multiple insertion with the first value remaining the same and the second value depending of the number of values of that column.
I know it sounds confusing but here is an example:
Table1
Name | Age
Table2
| age2 |
5
6
7
8
So the insertion Im looking for would result in something like this, suppose I'm going to insert in the NAME column 'JIM'
I want the result to be like this:
Table 1
|Name|Age
JIM 5
JIM 6
JIM 7
JIM 8
Of course I don't want to do it in 4 different queries, take in count I don't even know the AGE2 values.
So I want to automaticall insert JIM and create the different rows depending of the number of values of AGE2.
Any ideas??
Thanks.

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

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

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

This should do it... (untested)

INSERT INTO TABLE1

(Name, Age)

SELECT 'JIM' AS Name,

Age2 AS Age

FROM Table2

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MrCapuchino Mr or Mrs. 500 Points: 535 More actions · Answer 2

Hi,

I came to the conclusion that I could insert first the name values and then copy the values of one column to the other, the thing is that they are two different tables

How can I do that, because doing a query like

UPDATE table1 SET age = (SELECT age2 from TABLE2) will give me an obvious error saying that it is not a single value.

Any ideas?

Thanks

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

juanpablosiller (7/16/2010)
Hi,
I came to the conclusion that I could insert first the name values and then copy the values of one column to the other, the thing is that they are two different tables
How can I do that, because doing a query like
UPDATE table1 SET age = (SELECT age2 from TABLE2) will give me an obvious error saying that it is not a single value.
Any ideas?
Thanks

Yep... post the code you used for inserting the name values in to the table because we need to undo that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MrCapuchino Mr or Mrs. 500 Points: 535 More actions · Answer 4

Ok.. here is the thing

this is my situation

For instance,

One user registers, his name goes to one table several times like this

table 1

user | categories

name

then in another table i have the same number of categories as the name values inserted

table 2

categories

cat1

cat2

cat3

cat4

cat5

cat6

I want to UPDATE table one and insert each category to the name and the result would be

table 1

name | category

name cat1

name cat2

name cat3

name cat4

name cat5

name cat6

Is there a way to do that?

Thanks

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 5

How are you controlling the number of times the name goes into the table?

The method I showed you works. What's wrong with using that?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MrCapuchino Mr or Mrs. 500 Points: 535 More actions · Answer 6

Ok,

Maybe I'm not understanding here is the thing:

my table1 already has this values

name | lastname

jim

my table2 already has these values

nickname | lastname2

anything johnson

other bauer

other2 hurley

What I need to do is make a query to copu johnson, bauer, and hurley to the table1.lastname column.

so the first table results in

name1 | lastname

jim johnson

jim bauer

jim hurley

This is what I've done so far, it works but only copies the lastname2 "johnson" to all rows resulting in

name1 | lastname

jim johnson

this is my query so far

UPDATE table1

SET table1.lastname = table2.lastname2 from table2

where name = 'jim'

MrCapuchino Mr or Mrs. 500 Points: 535 More actions · Answer 7

Hello,

After talking with a professional programmer he told me that I was doing everything the wrong way,

What I want to do is best done with FOREIGN KEYS, I did not know how to use them but now I'm understanding how they work and I totally need them to do what I want in my application.

That's why all the answers from other people did not worked because they thought I already was managing foreign keys.

Thanks everyone for the help!