Menu
I need to insert values into a table. But my condition is that I need to select Min(date) from another table and this value should be inserted into another table.
My query
Insert into tempTable values
('Value1','Value2','Value3',(select min(val_dt) from anotherTable),'Y',getdate())
If I use this query I am facing error.
Guide me how to use select query inside the insert query.
690
You can use a select-statement within an INSERT statement to insert zero, one, or more rows into a table from the result table of the select-statement. The select-statement embedded in the INSERT statement is no different from the select-statement you use to retrieve data.
With the help of same structure table. If we want to insert in a table whose structure is same as another table then in the following example it has been demonstrated that how we can have conditional insert i.e. how we can use WHERE clause with INSERT INTO statement.
Right click on the database > Tasks > Generate Scripts. Next. Select "Select specific database objects" and check the table you want scripted, Next. Click Advanced > in the list of options, scroll down to the bottom and look for the "Types of data to script" and change it to "Data Only" > OK.
The primary difference is that SELECT INTO MyTable will create a new table called MyTable with the results, while INSERT INTO requires that MyTable already exists. You would use SELECT INTO only in the case where the table didn't exist and you wanted to create it based on the results of your query.
Instead of using VALUES() in the INSERT statement, use a SELECT to add the row values:
INSERT INTO tempTable
SELECT 'Value1', 'Value2', 'Value3', MIN(val_dt), 'Y', GETDATE()
FROM anotherTable
And the SELECT statement can be as convoluted as you want, meaning WHEREs and the like can be included.
160
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
