SQL Server Insert into with Where Clause

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'

SQL Server : Insert into Where Condition is True

This is valid on sql server.

INSERT INTO Table1  (BatchStart, BatchId, FermNum,SiteId)  
select '2016-11-11',7280,1,6 
where 7280 > (select top 1 batch_id 
                 FROM Table1 
                 ORDER BY BatchStart DESC)

insert into values with where clause

IF NOT EXISTS(SELECT 1 FROM [MyDB].[dbo].[Accounts] WHERE MyID = @MyID)
    INSERT INTO [MyDB].[dbo].[Accounts]
        (MyID, Phone, MyDate, Agent, Charge, Vendor)
        VALUES 
        (@MyID, @Phone, @MyDATE, @Agent, @Charge, @Vendor)

Insert Into Table using Where Clause

How about something like this?

IF EXISTS (SELECT ID FROM Table2)
   Update Table2
   SET Table2.CURRENT_TIMESTAMP = Table1.CURRENT_TIMESTAMP  --However you want to update
   FROM Table2 tab2
   JOIN Table1 tab1 on Table1.ID = Table2.PTID
ELSE
   INSERT INTO Table2(ID, startTime)
   SELECT ID, CURRENT_TIMESTAMP
   FROM Table1

SQL INSERT INTO statement with WHERE Statement

Why don't you just use IF-clause?

IF (AssetTagNumber = @TagNo)
BEGIN
    INSERT INTO AssetComponents(ComponentID, ComponentDescription)
    VALUES (@ComponentType, @CompDescr)
END

For statements with WHERE script should look similar to:

INSERT INTO AssetComponents(ComponentID, ComponentDescription)
SELECT @ComponentType, @CompDescr
FROM <table>
WHERE (AssetTagNumber = @TagNo)

Insert into with select statement where value is not null

You can filter the rows where table2.var2 is null in a WHERE clause.

INSERT INTO table1
            (<list of target columns>)
            SELECT table2.var1,
                   table2.var2,
                   1,
                   getdate(),
                   1,
                   getdate(),
                   0
                   FROM table2
                   WHERE table2.var2 IS NOT NULL;

You should also explicitly list the targeted columns in any INSERT so that statements don't break if the number or order of columns change in the target table.

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

Using the WITH clause in an INSERT statement

You will need to place the INSERT INTO right after the CTE. So the code will be:

;WITH alias (y,z)
AS
(
    SELECT y,z FROM tableb
)
INSERT INTO tablea(a,b)
SELECT y, z 
FROM alias

See SQL Fiddle with Demo

---

Related Topics