Hi there!.
I'm having a simple problem. I have a table with like 20 fields , this table
is always growing since it's a price table acumulator for a set of products
(with their features) , this is loaded from a text file.
When a new text file arrives , I upload it to a temp table prior to import
i to the real table.
What I need to do is to only insert in the main table only the NEW fields,
so basically any record that differs in any of the 20 fields from the one
inside the database.
It's a stupid thing when you think of it, but I cannot seem to find the solu
tion.
Any help will be appreciated!!!
VictorSorry.
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Victor Daicich" wrote:
> Hi there!.
> I'm having a simple problem. I have a table with like 20 fields , this tab
le
> is always growing since it's a price table acumulator for a set of product
s
> (with their features) , this is loaded from a text file.
> When a new text file arrives , I upload it to a temp table prior to import
> i to the real table.
> What I need to do is to only insert in the main table only the NEW fields,
> so basically any record that differs in any of the 20 fields from the one
> inside the database.
> It's a stupid thing when you think of it, but I cannot seem to find the so
lution.
> Any help will be appreciated!!!
> Victor
>
>|||As Alejandro said, the shortest path to a solution is to provide DDL, sample
data and expected results. Based on what you have given though, it sounds
like an INSERT INTO with a SELECT statement with a NOT EXISTS and one
*HUMONGOUS* WHERE clause. It's just a big WHERE clause with about 20 AND's
in it. Here's a sample that selects only the unique rows from table #b
(that don't currently exist in #a). I cut it down to just two columns, but
you can expand the SELECT subquery in the NOT EXISTS predicate to include as
many columns as you like:
CREATE TABLE #a (color1 VARCHAR(16),
color2 VARCHAR(16))
CREATE TABLE #b (color1 VARCHAR(16),
color2 VARCHAR(16))
INSERT INTO #a (color1, color2)
SELECT 'blue', 'red'
UNION SELECT 'red', 'green'
UNION SELECT 'black', 'yellow'
UNION SELECT 'black', 'blue'
INSERT INTO #b (color1, color2)
SELECT 'blue', 'green'
UNION SELECT 'black', 'yellow'
UNION SELECT 'yellow', 'purple'
UNION SELECT 'black', 'blue'
SELECT b.*
FROM #b b
WHERE NOT EXISTS
(
SELECT 1
FROM #a a
WHERE a.color1 = b.color1
AND a.color2 = b.color2
)
DROP TABLE #a
DROP TABLE #b
"Victor Daicich" <victordaicich@.hotmail.com> wrote in message
news:12613abe6ea18c85d1b76245e10@.msnews.microsoft.com...
> Hi there!.
> I'm having a simple problem. I have a table with like 20 fields , this
> table is always growing since it's a price table acumulator for a set of
> products (with their features) , this is loaded from a text file.
> When a new text file arrives , I upload it to a temp table prior to import
> i to the real table.
> What I need to do is to only insert in the main table only the NEW fields,
> so basically any record that differs in any of the 20 fields from the one
> inside the database.
> It's a stupid thing when you think of it, but I cannot seem to find the
> solution.
> Any help will be appreciated!!!
> Victor
>|||Hello Mike C#,
That's right Mike. It's like you said, a big where clause is what I need
then.
Thanks guys you've been very helpful with this issue. I can go on now and
finish it.
Thanks again,
Victor
> As Alejandro said, the shortest path to a solution is to provide DDL,
> sample data and expected results. Based on what you have given
> though, it sounds like an INSERT INTO with a SELECT statement with a
> NOT EXISTS and one *HUMONGOUS* WHERE clause. It's just a big WHERE
> clause with about 20 AND's in it. Here's a sample that selects only
> the unique rows from table #b (that don't currently exist in #a). I
> cut it down to just two columns, but you can expand the SELECT
> subquery in the NOT EXISTS predicate to include as many columns as you
> like:
> CREATE TABLE #a (color1 VARCHAR(16),
> color2 VARCHAR(16))
> CREATE TABLE #b (color1 VARCHAR(16),
> color2 VARCHAR(16))
> INSERT INTO #a (color1, color2)
> SELECT 'blue', 'red'
> UNION SELECT 'red', 'green'
> UNION SELECT 'black', 'yellow'
> UNION SELECT 'black', 'blue'
> INSERT INTO #b (color1, color2)
> SELECT 'blue', 'green'
> UNION SELECT 'black', 'yellow'
> UNION SELECT 'yellow', 'purple'
> UNION SELECT 'black', 'blue'
> SELECT b.*
> FROM #b b
> WHERE NOT EXISTS
> (
> SELECT 1
> FROM #a a
> WHERE a.color1 = b.color1
> AND a.color2 = b.color2
> )
> DROP TABLE #a
> DROP TABLE #b
> "Victor Daicich" <victordaicich@.hotmail.com> wrote in message
> news:12613abe6ea18c85d1b76245e10@.msnews.microsoft.com...
>|||If you are using 2005, then you have the "except" operator.
select... from A
except
Select ... from B
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Victor Daicich" wrote:
> Hello Mike C#,
> That's right Mike. It's like you said, a big where clause is what I need
> then.
> Thanks guys you've been very helpful with this issue. I can go on now and
> finish it.
> Thanks again,
> Victor
>
>
>|||Good point, I assumed SQL 2000 (I always do when the OP doesn't mention the
platform) :)
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:EFA7D756-F8CA-4F4B-A614-81A78981ADEA@.microsoft.com...
> If you are using 2005, then you have the "except" operator.
> select... from A
> except
> Select ... from B
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Victor Daicich" wrote:
>|||Oracle provides INTERSECT and MINUS operators (9i, maybe earlier).
SQL Server 2005 provides INTERSECT and EXCEPT.
Is there an ANSII SQL equivilant that anyone is aware of?
"Mike C#" <xyz@.xyz.com> wrote in message
news:OeBlwT$jGHA.4660@.TK2MSFTNGP05.phx.gbl...
> Good point, I assumed SQL 2000 (I always do when the OP doesn't mention
the
> platform) :)
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:EFA7D756-F8CA-4F4B-A614-81A78981ADEA@.microsoft.com...
need
and
you
>|||"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eUyihMJkGHA.3512@.TK2MSFTNGP03.phx.gbl...
> Oracle provides INTERSECT and MINUS operators (9i, maybe earlier).
> SQL Server 2005 provides INTERSECT and EXCEPT.
> Is there an ANSII SQL equivilant that anyone is aware of?
ANSI SQL:1999 defines INTERSECT and EXCEPT. Unfortunately SQL 2000 is only
compliant up to ANSI SQL:1992.|||INTERSECT, UNION and EXCEPT are in ANSI SQL. MINUS is not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eUyihMJkGHA.3512@.TK2MSFTNGP03.phx.gbl...
> Oracle provides INTERSECT and MINUS operators (9i, maybe earlier).
> SQL Server 2005 provides INTERSECT and EXCEPT.
> Is there an ANSII SQL equivilant that anyone is aware of?
>|||Good to know. Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:exwUSUJkGHA.3844@.TK2MSFTNGP02.phx.gbl...
> INTERSECT, UNION and EXCEPT are in ANSI SQL. MINUS is not.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:eUyihMJkGHA.3512@.TK2MSFTNGP03.phx.gbl...
