SQL Server Query - How to Insert Selected Values ​​into Another Table

Question

Here is what I am trying to do.

This is a select statement

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

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

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

How can i do this?

Answer 1

Like this:

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

Answer 2

This can be done with 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 used longhand here because it makes no assumptions about the ordering of the columns in the destination table, which may change over time and are invalid for the insert statement.

Answer 3

You can insert a collection obtained with SELECT into another existing table using the INSERT ... SELECT syntax.

For instance:

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