SQL server 2008 query move table data from one to another

Question

I have the following in coldfusion:

<cfquery name="queryInRange" datasource="Database1" >
        select STOCK
        from CAR
        WHERE (CAR_INV_DATE between '#dateStartDate#' and '#dateEndDate#')
    </cfquery>

    <cfoutput query="queryInRange">

        <cfquery name="INSStocks" datasource="Database1" > 
            if not exists (select strStockNumber from Aggregates WHERE strStockNumber='#STOCK#')
            BEGIN
                insert into Aggregates (strStockNumber)
                    VALUES('#STOCK#')
              END
        </cfquery>

    </cfoutput>

The idea here is to pull the field stock from one table, a vendor table, and make sure it has a record in the second table, which we then add some info to. I'm wondering, is there a way to do this in just sql, so there is cf loop?

score 3 · Accepted Answer · answered Feb 13 '17 at 14:10

3

Yes there is a way to do it with just sql. In fact, there are quite a few. Here is one.

 insert into table2
 (field1, field2, etc)
 select value1, value2, etc
 from otherTables
 etc
 except 
 select value1, value2, etc
 from table1

answered Feb 13 '17 at 14:10

Dan Bracuk

20,699
4
26
43

Does EXCEPT work the same way as the IF NOT EXISTS in my query? – Patrick Schomburg Feb 13 '17 at 14:56
1

Read [the documentation on Except](https://msdn.microsoft.com/en-us/library/ms188055.aspx) to understand how it works. – Leigh Feb 13 '17 at 15:25
Right, it says "Returns any distinct values from the query to the left of the EXCEPT operator that are not also returned from the right query. " I'm trying to understand how that is different from NOT EXISTS "Specifies a subquery to test for the existence of rows." – Patrick Schomburg Feb 13 '17 at 15:54
3

In theory, they do the same thing, ie return only records that are *not* matched. However there are some differences in how values are compared. You could answer a lot of these questions yourself by doing a search first :) A very brief search on "SQL Server EXCEPT vs NOT EXISTS" turns up results such as http://stackoverflow.com/questions/1662902/when-to-use-except-as-opposed-to-not-exists-in-transact-sql – Leigh Feb 13 '17 at 16:52
A MERGE statement might be what is needed. https://msdn.microsoft.com/en-us/library/bb510625.aspx – James A Mohler Feb 14 '17 at 04:02

SQL server 2008 query move table data from one to another

1 Answers1