Need to modify query to insert multiple rows

I have a table in my database with coaches, and I've configured it to add multiple rows of the sports table into ONE users records.

I'm trying to configure the database so that I can have multiple coaches be inserted at once.

For example, my current query is as follows:

insert into sports (coach, player1, player2, player3, player4)
select 'maddon', player1, player2, player3, player4 game from sports where coach in (select id from coach_names where coach in 'pinella','zimmer');

Open in new window


What I want to do is something that will produce the following result:

insert into sports (coach, player1, player2, player3, player4)
select ('maddon','hernandez','larkin'), player1, player2, player3, player4 game from sports where coach in (select id from coach_names where coach in 'pinella','zimmer');

Open in new window


I know the syntax of the last statement is off - just trying to illustrated what I want to accomplish. I'd prefer to do this with just one query.

Any assistance is welcome.
TampaJayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
I am not sure what game in ..  player4 game from sports.. is for?

With one query, It seems no way to do this unless you are using VALUES(...) not Select...From WHERE.

Below is what I came up with:
insert into #sports2(coach, player1, player2, player3, player4 )  
Select 'maddon', player1, player2, player3, player4 from #sports Where coach ='pinella'
GO
insert into #sports2(coach, player1, player2, player3, player4 )
Select 'hernandez', player1, player2, player3, player4 from #sports Where coach ='zimmer'
GO
insert into #sports2(coach, player1, player2, player3, player4 )
Select 'hernandez', player1, player2, player3, player4 from #sports Where coach ='jeff'

Open in new window


Using the following temp tables:
create table #sports (coach varchar(20)
	, player1 varchar(10)
	, player2 varchar(10)
        , player3 varchar(10)
	, player4 varchar(10));

create table #sports2 (coach varchar(20)
	, player1 varchar(10)
	, player2 varchar(10)
	, player3 varchar(10)
	, player4 varchar(10));


insert into #sports(coach, player1, player2, player3, player4 ) values 
('pinella', 'p1', 'p2', 'p3', 'p4'),
('zimmer', 'p1z', 'p2z', 'p3z', 'p4z'),
('jeff', 'p1j', 'p2j', 'p3j', 'p4j');
select * from #sports;
select * from #sports2;

Open in new window


#sports
coach     player1     player2     player3     player4
pinella      p1                 p2                     p3               p4
zimmer      p1z      p2z      p3z      p4z
jeff      p1j      p2j      p3j      p4j
0
TampaJayAuthor Commented:
The 'game' is a typo. I don't see a place to edit my original comment or I would delete it.

Also, I'm trying to avoid multiple select statements - because when using real data I might have 1000 'coaches' to be cloned from another 1000 coaches.
0
Mike EghtebasDatabase and Application DeveloperCommented:
You need a stored procedure to do this.

You are basically changing (updating) coach names. Isn't it why you are doing this?

If so, have a table named EditCoaches with two fields CoachNow CoachFinal; and then based on this table you can do one of the follwings:

- Update coaches' name in the same table, or
- Based on EditCoaches table, copy and transform data from your sports table to another table.

Mike
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.