SQL - Find Statement That Insert Specific Values

Insert into ... values ( SELECT ... FROM ... )

Try:

INSERT INTO table1 ( column1 )
SELECT  col1
FROM    table2  

This is standard ANSI SQL and should work on any DBMS

It definitely works for:

• Oracle
• MS SQL Server
• MySQL
• Postgres
• SQLite v3
• Teradata
• DB2
• Sybase
• Vertica
• HSQLDB
• H2
• AWS RedShift
• SAP HANA
• Google Spanner

Find Specific Values using SQL Like Statement

Try this :

SELECT FILENAME FROM FILE_LIST WHERE FILENAME LIKE '%' || TO_CHAR(SYSDATE,'DD-MM-YYYY') || '%' AND STATUS = 'N';

SQL Server INSERT INTO with WHERE clause

I think you are trying to do an update statement (set amount = 12.33 for customer with ID = 145300)

UPDATE Payments
SET Amount = 12.33
WHERE CustomerID = '145300'

Else if you are trying to insert a new row then you have to use

IF NOT EXISTS(SELECT 1 FROM Payments WHERE CustomerID = '145300')
    INSERT INTO Payments(CustomerID,Amount)
    VALUES('145300',12.33)

Or if you want to combine both command (if customer exists do update else insert new row)

IF NOT EXISTS(SELECT 1 FROM Payments WHERE CustomerID = '145300')
    INSERT INTO Payments(CustomerID,Amount)
    VALUES('145300',12.33)
ELSE
    UPDATE Payments
    SET Amount = 12.33
    WHERE CustomerID = '145300'

inserting values into specific rows with SQL

An INSERT statement is only for creating completely new records, not for populating data into existing ones. What you need is an UPDATE statement, which updates an existing record:

UPDATE bike SET full_day = 10 WHERE bike_type = 'mens_hybrid';

---

(Note: below is an earlier version of this answer, from before it was clear to me what the original poster was trying to do. I'm leaving it here because multiple people who originally answered this question did not seem to notice the problem with writing INSERT ... VALUES (...) WHERE, which makes me think that this explanation might be useful to some people coming across this question.)

---

That statement doesn't make sense. An INSERT can't take a WHERE clause, because WHERE refers to existing records, and an INSERT creates new ones.

(To forestall potential confusion: there exists such a thing as INSERT INTO ... SELECT ..., where the records to insert are determined by a SELECT query rather than by a VALUES expression, and in that case the SELECT query can, of course, have a WHERE clause. But in no case does the WHERE clause belong to the INSERT statement directly.)

Insert into table select with specific column values

There are no "easy" shortcuts for what you are trying to do.

Reasons:

1. SQL Server has no functionality for positional column referencing.
2. As @Larnu said there are no "There is no * {replace column with this} or * {except this column}."
3. CreatedBy column will not always be last as when adding columns to the tables, they will be added to the last position, unless you force a specific position by re-creating the table.

Solutions:

1. Update after insert - as you have mentioned.
2. Use a temp table / table variable to temporarily store the data and update that before writing it to history table.

The choice depends on how many rows are typically upserted (updated / inserted) at one time and how big the history table is. Small upsert batches - use table variable. For very large batches table variables will probably slow down performance as you would be copying a lot of data around.

Get Insert query with already inserted data from a table based On certain Conditions

You can generate an insert query by building a string, for example:

select  'insert into dbo.YourTable (str_col, int_col) values (' +
        isnull('''' + strcol + '''', 'NULL') + ', ' +
        isnull(cast(int_col as varchar(max)), 'NULL') + ');'
from    SampleTable
where   id=10 

If it's a one-off job, you can use Tasks->Export Data and friends instead of manually crafting SQL inserts.

INSERT SELECT statement with only one column for SELECT

Your query will fail if the select returns more than one row. A more flexible option is to use the INSERT ... SELECT syntax and provide literal values in the additional columns:

INSERT INTO TableB (InternalEmplID, Month, Year)
SELECT InternalEmplIDFROM, 'July', '2020' TableA WHERE Name = 'John Smith'

Wrting Sql insert into statement using select statement

you're almost there: just use the SELECT variation for INSERT instread of VALUES, and include your data there as a constant:

insert into item1(date,name,qty) 
select <datevalue>, col1, col2 from item2;

If your date comes from another table, you can do this:

 insert into item1(date,name,qty) 
 select d.dcol1, i2.col1, i2.col2 
 from item2 i2 inner join table_containing_date_col d
 on <some join criteria>

EDIT: you have to ensure that the data types match i.e. your has to be parsable to a date if you are savign it to a date field (which you are, hopefully!). You don't give details of your database but it would be something like this for SQL Server

cast('your date in yyyymmdd format' as datetime) 

(yyyymmdd always works as it is recognisable ISO format)

or better still CONVERT

For MySql you have STR_TO_DATE, for Oracle TO_DATE etc.etc.

---

Related Topics