join or subselect: identical

Showing posts with label identical. Show all posts

Monday, March 26, 2012

Joins on UPDATE

I need some help understanding what's happening on a join when updating
tables. Assume 2 tables with identical structures:

UPDATE
Table1
SET
Field1 = Table2.Field1,
Field2 = Table2.Field2
FROM
Table2
WHERE
Table1.Field3 = Table2.Field3
AND Table1.Field4 = Table2.Field4

Indexes exist on Field3 and Field4 on both tables. So why does SQL
Server choose a hash join?

Thanks in advance.(andrewbb@.gmail.com) writes:
> I need some help understanding what's happening on a join when updating
> tables. Assume 2 tables with identical structures:
> UPDATE
> Table1
> SET
> Field1 = Table2.Field1,
> Field2 = Table2.Field2
> FROM
> Table2
> WHERE
> Table1.Field3 = Table2.Field3
> AND Table1.Field4 = Table2.Field4
>
> Indexes exist on Field3 and Field4 on both tables. So why does SQL
> Server choose a hash join?

Are those indexes on (Field3, Field4) or indexes on the individual
fields?

In any case, one of the tables will have to be scanned. Say that would
be Table2. Now for each row, we should look for a matching row in Table1.
Now, assume that only a few rows match. In this case, using a nested loop
and look up the row in Table1 is a good idea.

But what if all rows match? In this case, the pages in Table1 would be
accessed many times, and that would be expensive. Better then to scan
Table1 once. If there is a clustered index on (Field3, Field4), SQL
Server should be able to do a merge join, and scan both tables in
parallel. But if the index is non-clustered, then it's not of much
use, so instead SQL Server builds the hash table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Fields 3 and 4 are individual indexes (not clustered) and the unique
key for the record.

There is exactly a one to one relationship between the two tables, so
how should I structure this to update quickly?|||(andrewbb@.gmail.com) writes:
> Fields 3 and 4 are individual indexes (not clustered) and the unique
> key for the record.
> There is exactly a one to one relationship between the two tables, so
> how should I structure this to update quickly?

You should have a clustered index on (field3, field4).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Friday, March 9, 2012

join two tables returning unlike data

How do you return unlike data between two identical tables.
IE : Table1 , column1,column2,colum3,column4
Table2 , column1,column2,colum3,column4
I need the rows that are not in Table2.
Thanks for the Help
pixel696SELECT a.* FROM myTableA a LEFT JOIN myTableB b
WHERE a.myKey = b.myKey WHERE b.myKey IS NULL

is one way...|||brett, i think you put one too many where clauses. the first one should be replaced with on.

JOIN to get rows in one table that are not in the other

I've got two identical tables: sw_scan and software.
Now I want to get all rows from sw-scan that don't exist in table software.

With my statement I get all of the rows from table 1 with NULL for all table 2 values.. :confused:

SELECT *
FROM sw_scan
LEFT JOIN software USING(Manufacturer,Product,Version,Computer)
WHERE software.manufacturer IS NULL;

oh, and (Manufacturer, Product, Version, Computer) is primary key in both tables ..well, that should work, the only thing i can suggests is don't use USING
SELECT *
FROM sw_scan
LEFT
JOIN software
on sw_scan.Manufacturer
= software.Manufacturer
and sw_scan.Product
= software.Product
and sw_scan.Version
= software.Version
and sw_scan.Computer
= software.Computer
WHERE software.Manufacturer IS NULL|||hmm.. thanks..
but I already tried lots of things.. to be sure.. and this was one of them...
sth really weird is going on here.. :(|||well, the table 2 columns should all be null, because that's what you're looking for, right?

by the way, what database is this?|||They should be NULL... but the resultset should only contain the rows that have NULL at the software.Manufacturer field after the JOIN.
I used exactly the smae statements to create and fill the tables, only changing the table name, and afterwards I deleted 10 rows from software...
So my resultset should have ten rows. Instead it outputs all 1000 rows filling all fields from table software with NULL.

It's a MySQL 4 db|||do you get all 990 rows when you change LEFT to INNER?|||When I make it an INNER JOIN, I get an empty set..
And if I also get rid of the WHERE clause I get the 990 rows...|||i'm running out of ideas

try REPAIRing the database|||Solved !

After I dropped the tables.. recreated them (with exactly the same statement) and filled them again, it worked..

thnx for the help Rudy!

Monday, February 20, 2012

join many MDF into one

Hi,
I have a MSDE2000 installation.
There I have 120 MDF files \ databases with alltogether 10GB.
All databases have an identical structure (same tables etc.)
Is there a way to join all databases into one big database?
I would then attach this big database to an SQL2000 Server and run some
queries against it.
thank youSteffen
Are those tables which have the same stucture also contain the data?
"Steffen Meier" <mature4711@.hotmail.com> wrote in message
news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I have a MSDE2000 installation.
> There I have 120 MDF files \ databases with alltogether 10GB.
> All databases have an identical structure (same tables etc.)
> Is there a way to join all databases into one big database?
> I would then attach this big database to an SQL2000 Server and run some
> queries against it.
> thank you
>|||"Steffen Meier" <mature4711@.hotmail.com> wrote in message
news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I have a MSDE2000 installation.
> There I have 120 MDF files \ databases with alltogether 10GB.
> All databases have an identical structure (same tables etc.)
> Is there a way to join all databases into one big database?
> I would then attach this big database to an SQL2000 Server and run some
> queries against it.
> thank you
>
If you mean like detach them and attach them no.
Unfortunately you'll probably have to script together something to move the
data.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi Uri,
Yes, these tables also contain the data.
Actually the structure of all the 120 databases is 100% identical
These databases contain logfile-data from ISA-Server 2004 - one new database
per day is created...
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:eTC3OBdVHHA.4796@.TK2MSFTNGP05.phx.gbl...
> Steffen
> Are those tables which have the same stucture also contain the data?
>
> "Steffen Meier" <mature4711@.hotmail.com> wrote in message
> news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> I have a MSDE2000 installation.
>> There I have 120 MDF files \ databases with alltogether 10GB.
>> All databases have an identical structure (same tables etc.)
>> Is there a way to join all databases into one big database?
>> I would then attach this big database to an SQL2000 Server and run some
>> queries against it.
>> thank you
>>
>|||I was hoping that there is a way to bulk-export the data of all 120
databases to a textfile and then import it into a new database... has
MSDE2000 some command for bulk export to textfile ? (or any other format?)
thx
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schrieb im
Newsbeitrag news:es1wPFdVHHA.600@.TK2MSFTNGP05.phx.gbl...
>
> "Steffen Meier" <mature4711@.hotmail.com> wrote in message
> news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> I have a MSDE2000 installation.
>> There I have 120 MDF files \ databases with alltogether 10GB.
>> All databases have an identical structure (same tables etc.)
>> Is there a way to join all databases into one big database?
>> I would then attach this big database to an SQL2000 Server and run some
>> queries against it.
>> thank you
>>
> If you mean like detach them and attach them no.
> Unfortunately you'll probably have to script together something to move
> the data.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>|||You're on the right track. I would write a VB.NET application that
programmatically used SqlBulkCopy to import the data into the common
database. I expect it would take a long afternoon to write.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Steffen Meier" <mature4711@.hotmail.com> wrote in message
news:%235RkfbeVHHA.1000@.TK2MSFTNGP05.phx.gbl...
>I was hoping that there is a way to bulk-export the data of all 120
>databases to a textfile and then import it into a new database... has
>MSDE2000 some command for bulk export to textfile ? (or any other format?)
> thx
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schrieb im
> Newsbeitrag news:es1wPFdVHHA.600@.TK2MSFTNGP05.phx.gbl...
>>
>> "Steffen Meier" <mature4711@.hotmail.com> wrote in message
>> news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> I have a MSDE2000 installation.
>> There I have 120 MDF files \ databases with alltogether 10GB.
>> All databases have an identical structure (same tables etc.)
>> Is there a way to join all databases into one big database?
>> I would then attach this big database to an SQL2000 Server and run some
>> queries against it.
>> thank you
>>
>> If you mean like detach them and attach them no.
>> Unfortunately you'll probably have to script together something to move
>> the data.
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting
>> sql (at) greenms.com http://www.greenms.com
>|||"Steffen Meier" <mature4711@.hotmail.com> wrote in message
news:%235RkfbeVHHA.1000@.TK2MSFTNGP05.phx.gbl...
>I was hoping that there is a way to bulk-export the data of all 120
>databases to a textfile and then import it into a new database... has
>MSDE2000 some command for bulk export to textfile ? (or any other format?)
>
Look into BCP
> thx
>
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||DTS is the way to go.
--DatabaseAdmins.com
Remote DBA Services
"Greg D. Moore (Strider)" wrote:
> "Steffen Meier" <mature4711@.hotmail.com> wrote in message
> news:%235RkfbeVHHA.1000@.TK2MSFTNGP05.phx.gbl...
> >I was hoping that there is a way to bulk-export the data of all 120
> >databases to a textfile and then import it into a new database... has
> >MSDE2000 some command for bulk export to textfile ? (or any other format?)
> >
> Look into BCP
>
> > thx
> >
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>|||yeah it's called psuedodynamic sql
select 'insert into Destination.Dbo.Mytable Select * FROM ' + name +
'.dbo.Source'
from master.dbo.sysdatabases
if you've got to get a different table name in each database; then do a join
to sysobjects
"Steffen Meier" <mature4711@.hotmail.com> wrote in message
news:%235RkfbeVHHA.1000@.TK2MSFTNGP05.phx.gbl...
> I was hoping that there is a way to bulk-export the data of all 120
> databases to a textfile and then import it into a new database... has
> MSDE2000 some command for bulk export to textfile ? (or any other format?)
> thx
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schrieb im
> Newsbeitrag news:es1wPFdVHHA.600@.TK2MSFTNGP05.phx.gbl...
> >
> >
> > "Steffen Meier" <mature4711@.hotmail.com> wrote in message
> > news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
> >> Hi,
> >>
> >> I have a MSDE2000 installation.
> >> There I have 120 MDF files \ databases with alltogether 10GB.
> >> All databases have an identical structure (same tables etc.)
> >>
> >> Is there a way to join all databases into one big database?
> >> I would then attach this big database to an SQL2000 Server and run some
> >> queries against it.
> >>
> >> thank you
> >>
> >>
> >
> > If you mean like detach them and attach them no.
> >
> > Unfortunately you'll probably have to script together something to move
> > the data.
> >
> >
> >
> > --
> > Greg Moore
> > SQL Server DBA Consulting
> > sql (at) greenms.com http://www.greenms.com
> >
>

Monday, March 26, 2012

Joins on UPDATE

Friday, March 9, 2012

join two tables returning unlike data

JOIN to get rows in one table that are not in the other

Monday, February 20, 2012

join many MDF into one

join many MDF into one

join or subselect

Blog Archive

About Me