SQL syntax using C#

Question

I'm trying to insert values into some table with this code using C# and SQL:

string query1 = "select PlayerID from Table1";
string query2 = "select GameID from Table2";

string q = "insert into Table3 (PlayerID, GameID) values(" + query1 + "," + query2 + ")";

Is it possible?

Edit:

I have 3 tables:

playersTbl - with auto increment id (primary key)
gameTbl - with auto increment id (primary key)
JoinTbl

When the game is starting, the user can register some players to the same game, so in the same game lets say there are 4 players.

When I'm adding those 4 players the playersTbl will look like:

ID | first name|
----------------
1  | p1        |
2  | p2        |
3  | p3        |
4  | p4        |

and the gameTbl will look like:

ID | Date | Hour|
-----------------
1  |      |     |

So what I'm trying to do is the Table3 will look like:

PlayerID | GameID|
------------------
1        | 1     |
2        | 1     |
3        | 1     |
4        | 1     |

Now when I'm starting a new game with one player

I'm adding the player to the playersTbl, and the game to the gameTbl, but now the third table will look like

 PlayerID | GameID|
    ------------------
    1        | 1     |
    2        | 1     |
    3        | 1     |
    4        | 1     |
    5        | 2     |

to be more clear, I don't want that players which are registered in the second game will be in the first game..

Avoid concatenating strings into SQL - this can open you to SQL Injection — Oded
– Oded, Commented Mar 10, 2013 at 15:29

John Woo · Accepted Answer · 2013-03-10 15:33:57Z

2

use INSERT INTO..SELECT statement,

INSERT INTO Table3(PlayerID, GameID)
SELECT  a.PlayerID, b.GameID
FROM    Table1 a CROSS JOIN Table2 b

what the query does is it produces cartesian product from both tables.

Consider The following example,

Table1

PlayerID
========
1
2
3

Table2

GameID
=======
10
11
12

when the query below is executed,

SELECT  a.PlayerID, b.GameID
FROM    Table1 a CROSS JOIN Table2 b

it produces the following result,

PlayerID    GameID
=========   ======
1           10
1           11
1           12
2           10
2           11
2           12
3           10
3           11
3           12

edited Mar 10, 2013 at 15:33

answered Mar 10, 2013 at 15:27

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

2 Comments

Elior Over a year ago

yeah but i'm trying not to make a cartesian product.. i'll edited my question so you can understand what i'm trying to do, but anyway thanks for the question

John Woo Over a year ago

@Elior please do. Looking at your queries, it fetches all ID from both tables.

Cellcon · Accepted Answer · 2013-03-10 15:54:19Z

0

You should be able to utilize the INSERT INTO SELECT method, like this:

INSERT INTO Table3 (PlayerID, GameID) SELECT Table1.ID, Table2.ID FROM Table1, Table2

edited Mar 10, 2013 at 15:54

answered Mar 10, 2013 at 15:47

Cellcon

1,2952 gold badges12 silver badges30 bronze badges

Collectives™ on Stack Overflow

SQL syntax using C#

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related