SQL Insert parameter value and value from another table

insert into sql
sql insert multiple rows from select
sql insert multiple rows from another table
insert into sql table from another table
sql insert with join
how to insert multiple rows using select statement in sql server
oracle insert into select * from another table
how to insert data into table using function in sql server

I'm unable to find a solution online for my question. If it is even possible, how do I write an SQL Insert statement that uses parameter values as well as selecting a value from another table.

Example:

"INSERT INTO Users (user_name, user_csn, user_adid, user_contact, user_adminpriviledge, user_datestart, user_active, user_team)
                 VALUES (@username, @usercsn, @useradid, @usercontact, @userauth, @userstart, @useractive, @userteam = (SELECT team_id FROM teaminfo WHERE team_name = '" & ddlAddTeam.SelectedValue & "'))"

I understand that the example is wrong, just trying my best to represent what I'm looking for in code.

Also another question would be regarding aliasing and datareaders. I seem to be unable to do "reader("column_name")" for aliased column names?

Example:

query = "SELECT u.*, t.team_name FROM Users u
    JOIN teaminfo t ON u.user_team = t.team_id WHERE user_csn = '" & GV.userCSN & "'"

I tried to use

reader("u.user_name")

but failed as well.

You need other syntax of insert operation: INSERT INTO ... SELECT ... FROM ...:

INSERT INTO Users (user_name, user_csn, user_adid, user_contact, user_adminpriviledge, user_datestart, user_active, user_team) 
SELECT @username, @usercsn, @useradid, @usercontact, @userauth, @userstart, @useractive, team_id --<--not here's your column
FROM teaminfo 
WHERE team_name = @param

Also, it looks like it's .NET (C# or VB code), so you you are prone to SQL injection concatenating you string with parameters!

In my SQL I already put @param in proper place, then with SqlCommand you are probably using, you have to call method Addon SqlCommand.Paramteres collection, and then supplly it with value of ddlAddTeam.SelectedValue.

Try this code:

Using connection = New SqlConnection("connString")
    Using com = New SqlCommand
        com.Connection = connection
        com.CommandText = "INSERT INTO Users (user_name, user_csn, user_adid, user_contact, user_adminpriviledge, user_datestart, user_active, user_team) 
                           Select @username, @usercsn, @useradid, @usercontact, @userauth, @userstart, @useractive, team_id --<--Not here's your column
                           From teaminfo
                           Where team_name = @param"
        com.Parameters.Add("@param", SqlDbType.VarChar).Value = ddlAddTeam.SelectedValue
        connection.Open()
    End Using
End Using

And for column alises: in data reader you use column aliases without table name (u before the dot in ou example). Try to give aliases to all your columns to avoid such problems.

INSERT INTO SELECT statement overview and examples, Create a SQL table on the fly while inserting records with appropriate If we have specified all column values as per table column orders, we do not need to specify column names. Suppose we want to insert data from another table. Example 7: INSERT INTO SELECT statement with a Table variable. A table-valued parameter column must have either non-default values in all rows or default values in all rows. It is not possible to have default values in some rows but not others. Therefore, in table-valued parameter bindings, the only status values allowed for table-valued parameter rowset column data are DBSTATUS_S_ISNULL and DBSTATUS_S_OK.

The data source for an INSERT statement can be a SELECT statement—see the <dml_table_source> part of the statement definition at the linked page—and a SELECT statement can include parameters in the select list. Here's a simple example:

declare @Target table (Id bigint, Datum char(1));
declare @Source table (Id bigint);
declare @Datum char(1) = 'X';
insert @Source values (1);

insert @Target
select
    Id = S.Id,       -- Value from another table
    Datum = @Datum   -- Parameter
from
    @Source S;

There are more examples at the page linked above; scroll down to the "Inserting Data From Other Tables" section header.

Also, if you're going to build a query in (C#?) code as you've shown in your example, you should really pass any arguments as parameters rather than trying to build them directly into the query text. Read up on SQL injection attacks to see why.

Advanced SQL: Insert output of the parameterized table-valued , To insert values in specific columns of a table, you need to provide A table-​valued function is a special T-SQL code that accepts parameter/parameters want to extract data from the servers stored in a different data center. Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints. SQL Server does not maintain statistics on table-valued parameters. Table-valued parameters are read-only in Transact-SQL code. You cannot update the column values in the rows of a table-valued parameter and you cannot insert or delete rows.

Your INSERT query should be like

"INSERT INTO Users (user_name, user_csn, user_adid, user_contact, user_adminpriviledge, user_datestart, user_active, user_team)
                 VALUES (@username, @usercsn, @useradid, @usercontact, @userauth, @userstart, @useractive,  (SELECT team_id FROM teaminfo WHERE team_name = @userteam ))"

Second when fetching from reader it should be like :

reader("user_name") // I am not sure about this. You can put break point and open the object in watch window

SQL Server INSERT INTO SELECT By Practical Examples, To insert data from other tables into a table, you use the following SQL Server It must return the values that are corresponding to the columns specified in the Sometimes, you just need to insert some rows from another table into a table. SQL Server does not maintain statistics on columns of table-valued parameters. Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

SQL: INSERT Statement, This SQL tutorial explains how to use the SQL INSERT statement with syntax, examples, Parameters or Arguments and so on. source_tables: Used when inserting records from another table. When inserting records into a table using the SQL INSERT statement, you must provide a value for every NOT NULL column. In the previous chapter, you learned how to Create, Rename, Alter and Delete Table using C# SQL. In this chapter, I am going to explain how you can insert rows in SQL table using various methods. I have created a Product Table in ComputerShop database in the previous chapter. However, you can use your own database and table to run the query.

Table Value Constructor (Transact-SQL), When specified in a table value constructor, DEFAULT is allowed only in an INSERT statement. expression. Is a constant, a variable, or an  SQL Server Table Valued Parameter is a new parameter type which has been introduced with the arrival of SQL Server 2008. We can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

SQL insert into statement, Column of the table. value1,value2,value3,value n, Values against each column. You can use another syntax to insert data. Here,  I've seen that the table I'm attempting to INSERT into does typecasting, i.e. a value of '0.02' will successfully insert to a column of type decimal(7,4). My strings will almost certainly have commas and apostrophe's that shouldn't be seen as delimiters.

Comments
  • Are you using it in some code? Like C#?
  • Using it in VB.NET
  • What about the JOIN portion? I saw similar examples to what you provided but didn't know how it worked. Would the Select portion not mean that the parameter values are obtained from teaminfo table?
  • @LimYX I told, that you should add aliases to your columns to avoid errors (it's optional, if you are sure column names ar unique, then, as I said, use only column name, without table name!).
  • This particular error appears, '"The parameterized query '(@username varchar(7), @usercsn nvarchar(10),@useradid nvarchar(' expects the parameter '@param', which was not supplied"'. Using it as how you stated in your updated answer and the way i did it 'cmdInsert.Parameters.AddWithValue("@param", ddlAddTeam.SelectedValue)' both didnt seem to work
  • Because you have lot more parameters! You have to add everyone of it using Parameters.Add (AddWithValue isn't recommended, since it make SQL "guess" the type).
  • Ah ok got it, thanks for the help and clarification! Also if you don't mind me asking as well, what about in the case of an update? Do I follow similarly like "Update Users SET user_name = @username, user_team = (SELECT team_id FROM teaminfo WHERE team_name = @teamname) WHERE (condition)"