472,338 Members | 1,868 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,338 software developers and data experts.

Appending data to a table but not duplicates

Hiya everyone,

I have two tables in SQL 2000. I would like to append the contents of
TableA to TableB.

Table A has around 1.1 Million Records.
Table B has around 1 Million Reocords.

Basically TableA has all of the data held in TableB plus 100,000
additional records. I would only like to import or append these new
additional records. I have a unique index already setup on Table B.

Any ideas pretty pretty please?

Paul.

Ps. (Have been messing around with DTS but get a unique violation error
- Which is kinda what I want I guess, but would like SQL to ignore the
error and only copy the new data - if only)

Jul 23 '05 #1
9 28702

<pa**@domainscanners.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
Hiya everyone,

I have two tables in SQL 2000. I would like to append the contents of
TableA to TableB.

Table A has around 1.1 Million Records.
Table B has around 1 Million Reocords.

Basically TableA has all of the data held in TableB plus 100,000
additional records. I would only like to import or append these new
additional records. I have a unique index already setup on Table B.

Any ideas pretty pretty please?

Paul.

Ps. (Have been messing around with DTS but get a unique violation error
- Which is kinda what I want I guess, but would like SQL to ignore the
error and only copy the new data - if only)


insert into dbo.TableB
(col1, col2, col3,...)
select col1, col2, col3...
from dbo.TableA a
where not exists (
select *
from dbo.TableB b
where a.keycol = b.keycol)

Simon
Jul 23 '05 #2
Simon,
how is your sql different from

insert into dbo.TableB
(col1, col2, col3,...)
select col1, col2, col3...
from dbo.TableA a
where keycol in (select keycol from dbo.tableb)
??
TIA
Rob

Jul 23 '05 #3
rcamarda (rc******@cablespeed.com) writes:
Simon,
how is your sql different from

insert into dbo.TableB
(col1, col2, col3,...)
select col1, col2, col3...
from dbo.TableA a
where keycol in (select keycol from dbo.tableb)


That's one hell of a difference - you are inserting the duplicates only. :-)

But, OK, put in the NOT, and your query is the same as Simon's. In SQL 6.5
there was a difference in performance, NOT IN usually executed slower. I
think that in SQL 2000, the optimizer rewrites the query internally.

Anyway, there is still an advantage with the style that Simon used.
Consider this query:

insert into dbo.TableB
(col1, col2, col3,...)
select col1, col2, col3...
from dbo.TableA a
where not exists (
select *
from dbo.TableB b
where a.keycol1 = b.keycol1
and a.keycol2 = B.keycol2)

That is not easily recast to NOT IN.

So NOT EXISTS is simply an operation you need to master.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
er. sorry. missed the not. I'm interesting in "exists" vs. in (select
.... )
so. are you saying that the advantage is when you are looking for
something that does not exist? Otherwise "..exists (select .." is the
same as "in (select ..." ?

Jul 23 '05 #5
rcamarda (rc******@cablespeed.com) writes:
er. sorry. missed the not. I'm interesting in "exists" vs. in (select
... )
so. are you saying that the advantage is when you are looking for
something that does not exist? Otherwise "..exists (select .." is the
same as "in (select ..." ?


Same thing there, EXISTS is the only that works when your condition
is more than a single column. There is also a gotcha there are NULL
values involved.

It's partly a matter of style, but I use (NOT) EXISTS far more often
then (NOT) IN. (With subqueries, that is. (NOT) IN a list of values
is another matter.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6
Yes - for a NOT IN condition, it's almost always better to use NOT
EXISTS. The main issue, as Erland pointed out, is the possibility of a
NULL in the subquery. Consider this simplified example:

IF 1 IN (1, 2, 3, NULL) PRINT 'True'

Obviously the condition is TRUE, but now consider this:

IF 1 NOT IN (2, 3, NULL) PRINT 'True'

Now we don't know if the condition is TRUE or not - the NULL has an
unknown value, so in principle it could be a 1, and therefore the whole
condition evaluates to UNKNOWN. So in the case of a correlated
subquery, any NULLs in the subquery mean that the whole query returns
no rows. Using NOT EXISTS avoids this trap.

Admittedly, you often use primary key columns in the correlation, so
there could never be a NULL in the subquery, but I think it's better to
have a 'safer' habit of using NOT EXISTS. And as Erland also mentioned,
there is some personal taste involved - I find that EXISTS/NOT EXISTS
expresses the intention of the query more clearly, especially when
someone is quickly looking through the code.

Simon

Jul 23 '05 #7
Thanks Guys!

Jul 23 '05 #8
Hi Guys,

Thanks very much for your answers to my questions. I ran the query you
supplied and it worked fine although I now have another problem and was
wondering if you would be able to helpo me out again?

Basically I now have a table containing all the data I need but the new
data has left a gap in the Identity column that im using.

Basically the original data's identity column went up to 1,000,000. I
was hoping that the new data that was appended would be inserted as
1,000,001 then 1,000,002 then 1,000,003 all the way up to 1,100,000.
However the new appended data went in as 1,254,324 then 1,254,325 etc.

Is there a command I can run to resnycronise my identity column? so
that the ID's run smoothly from 0 through to 1,1000,000?

Hope you can help me out again,

Paul.

Jul 23 '05 #9
(pa**@domainscanners.com) writes:
Basically the original data's identity column went up to 1,000,000. I
was hoping that the new data that was appended would be inserted as
1,000,001 then 1,000,002 then 1,000,003 all the way up to 1,100,000.
However the new appended data went in as 1,254,324 then 1,254,325 etc.

Is there a command I can run to resnycronise my identity column? so
that the ID's run smoothly from 0 through to 1,1000,000?


If you want contiguous ids, or at least control over them, don't
use the IDENTITY property. When you attempt to insert a row into
a table with the IDENTITY property, you consume one number, even if
the INSERT fails. This may seem stupid, but it is actually a feature,
because it speeds up concurrency. If the number would be reused in
case of failure, SQL Server would need to lock the number, and no
other process had been able to insert until the INSERT have completed.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Kevin Myers | last post by:
Hello, I'm an experienced application developer in some languages (including various SQL dialects), but have very little experience with MS...
1
by: oasd | last post by:
I'm having difficulty appending data. I have an import macro (using the Transferspreadsheet function) to import data from an excel spreadsheet...
2
by: Steve Stover | last post by:
I want to use the caching API in .net to store data. The data would be stored in a data table class that has approx. 10 columns and 71 rows. What...
2
by: Brian Mitchell | last post by:
Ok, I know this is an elementary question but I have a data grid that is bound to a data table and I can't seem to find a way to match the selected...
1
by: Vasilis X | last post by:
Here is the question : I have a data table, UnShorted, which has a data column EventTime (type : date time) and a data column Values (type :...
1
by: John | last post by:
Hi When using Table Adapter Configuration Wizard if 'Use SQL Statements' is selected as Command Type, the data table's name in dataset is...
1
by: laredotornado | last post by:
Hi, I have a data table on my page (buried amidst other images and extraneous text). I would like to spawn a new window that automatically...
4
by: indona | last post by:
hi, i have to enter data from a delimited file into sqlserver database table. i have been able to delimit the file and read the data into a data...
1
by: BaseballGraphs | last post by:
Hello, I am trying to divide one value from my data table with an associated ID by another value from my data table with a different ID for the...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
0
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.