I want to copy the User ID to another table while at the same time inserting data to it
SqlCommand cmd = new SqlCommand(@"INSERT INTO Attendance (User_ID, Month, Year, Status) SELECT User_ID FROM Users WitH Values(@Month, @Year, @Status)", con); cmd.Parameters.AddWithValue(@"Month", cmbMonth.Text); cmd.Parameters.AddWithValue(@"Year", cmbYear.Text); cmd.Parameters.AddWithValue(@"Status", "Pending"); The INSERT command comes in two flavors:
(1) either you have all your values available, as literals or SQL Server variables - in that case, you can use the INSERT .. VALUES() approach:
DECLARE @User_ID INT; SELECT @User_ID = User_ID FROM dbo.Users WHERE (some condition here) -- most likely INSERT INTO dbo.Attendance (User_ID, [Month], [Year], Status) VALUES (@User_ID, @Month, @Year, @Status) Note: I would recommend to always explicitly specify the list of column to insert data into - that way, you won't have any nasty surprises if suddenly your table has an extra column, or if your tables has an IDENTITY or computed column. Yes - it's a tiny bit more work - once - but then you have your INSERT statement as solid as it can be and you won't have to constantly fiddle around with it if your table changes.
(2) if you don't have all your values as literals and/or variables, but instead you want to rely on another table, multiple tables, or views, to provide the values, then you can use the INSERT ... SELECT ... approach:
INSERT INTO dbo.Attendance (User_ID, [Month], [Year], Status) SELECT User_ID, @Month, @Year, @Status FROM dbo.Users Here, you must define exactly as many items in the SELECT as your INSERT expects - and those can be columns from the table(s) (or view(s)), or those can be literals or variables. Again: explicitly provide the list of columns to insert into - see above.
You can use one or the other - but you cannot mix the two - you cannot use VALUES(...) and then have a SELECT query in the middle of your list of values - pick one of the two - stick with it. And the WITH VALUES(....) construct is totally not valid T-SQL code at all...
For more details and further in-depth coverage, see the official MSDN SQL Server Books Online documentation on INSERT - a great resource for all questions related to SQL Server!
I am not sure if I fully understand your question, however, if you are simply looking to insert your user_id into another table at the same time as inserting a record into a different table, you could add an INSERT TRIGGER to the table that receives the data first that adds the record to any additional table(s).
For instance, assume you have a Table1 that should also insert data into Table two. You could accomplish this by adding this trigger to Table1:
CREATE TRIGGER trg_Table1_ins ON dbo.Table1 AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.Table2 ( [user_id] ) SELECT [user_id] FROM inserted; END GO Again, this is based on my understanding--or lack thereof--your question.
Insert INTO Attendance (User_ID, Month, Year, Status) Select User_ID, @Month, @Year, @Status from Users?