SQL Server query - how to insert selected values into another table

Question

Here's what I am trying to do.

This is the select statement

select Id 
from tblUsersPokemons 
where UserId = 1 and PokemonPlace = 'bag'

Now I want to insert those returned Ids into another table like this:

foreach all returned Ids
   insert into tblNpcBattleUsersPokemons values (Id, 1)

How can I do that ?

RBarryYoung · Accepted Answer · 2012-12-10 22:28:48Z

10

Like this:

insert into tblNpcBattleUsersPokemons
select Id, 1 from tblUsersPokemons where UserId=1 and PokemonPlace='bag'

answered Dec 10, 2012 at 22:28

RBarryYoung

57.1k15 gold badges99 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JohnnyJaxs Over a year ago

Ok what about if i want to show(return selected data from a stored procedure) and also insert the selected data somewhere else. The Insert INTO () SELECT () FROM () it works but i need also to retrieve the collection too. Thanks

RBarryYoung Over a year ago

Depends what version of SQL Server you have. The later versions have the OUTPUT clause which should work. Otherwise, you would need to use two SQL statements (which isn't normally a problem);

retired · Accepted Answer · 2012-12-11 09:27:40Z

6

This can be done in a single sql call

insert into tblNpcBattleUsersPokemons (id, [whatever the name of the other column is])
    select Id, 1 from tblUsersPokemons where UserId=1 and PokemonPlace='bag'

I've used the longhand here because it does not make any assumption about the ordering of columns in the destination table, which can change over time and invalidate your insert statement.

edited Dec 11, 2012 at 9:27

answered Dec 10, 2012 at 22:29

retired

6,8902 gold badges32 silver badges47 bronze badges

1 Comment

ckpepper02 Over a year ago

+1 this worked for me. Thanks for posting an alternate answer!!

BStateham · Accepted Answer · 2012-12-10 22:29:46Z

1

You can insert the set retrieved by a SELECT into another existing table using the INSERT...SELECT syntax.

For example:

INSERT INTO tblNpcBattleUsersPokemons (Id, Val)  -- not sure what the second column name was 
SELECT Id FROM tblUsersPokemons WHERE UserID = 1 and PokemonPlace='bag';

answered Dec 10, 2012 at 22:29

BStateham

1,62910 silver badges15 bronze badges

Collectives™ on Stack Overflow

SQL Server query - how to insert selected values into another table

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related