comparing tables

Hi,

I have two tables containing (ahem) lists of mp3 tunes. One is the "master" list (table name "mp3_master") - everything I've got on my home pc. The other (table name "mp3") details everything I've got on my work pc.

Both tables have an id field, an "artist" field and a "title" field.

I'd like to simply compare the two tables and return a list of any artist/title combinations that are in the mp3 table (ie: that I have at work) but not in the mp3_master table (ie: that I haven't yet taken home).standard sql solution:

select id, artist, title from mp3
except
select id, artist, title from mp3_master

microsoft sql/server syntax has minus instead of except, i believe

if that don't work, do it the old-fashioned way:

select id, artist, title from mp3
where id not in (select id from mp3_master)

rudy
http://rudy.ca/|||Except is a function used in Analysis Services - MDX

The query

select id, artist, title from mp3
where id not in (select id from mp3_master)

will work fine, however it is a slow way, performance wise. Once a match is found the main query will not stop scanning the sub-query. A faster query is to use the NOT EXISTS

select id, artist, title from mp3
where not exists (select * from mp3_master mp3.id = mp3_master.id)

Once a match is found the search stops|||Hmm - maybe I should have added that the id numbers in these tables don't match up.

select id, artist, title from mp3
where title not in (select title from mp3_master)

I used that. As long as I've not got the same tune by different artists, I should be ok.

select id, artist, title from mp3
where not exists (select * from mp3_master mp3.id = mp3_master.id)

I get a "syntax error near "." on that - is that some sort of shorthand for a join?

Cheers anyway :)|||i don't have sql/server to test on, so i'm guessing, but like i said, i think the operator is MINUS

select artist, title from mp3
minus
select artist, title from mp3_master

if the id numbers don't match up, you don't want to match on id number in the subselect, neither a's way nor mine

rudy|||Hi,

The 'minus' clause certainly won't work in Query Analyzer. There is no syntax even simlar in SQL Server.

The 'not in' or 'where not exists' are the correct syntax.

You had some typos in the SQL that failed with the syntax error. It should be:

select id, artist, title from mp3
where not exists (select * from mp3_master where mp3_master.mp3.id = mp3_master.id)

Hope this helps.

- Andy Abel|||thanks andy, it wouldn't be the first time sql/server didn't support standard sql ;)

spudhead, since you can't match on ids, try this --

select id, artist, title from mp3
where not exists
( select 1 from mp3_master
where artist = mp3.artist
and title = mp3.title )

rudy|||I love reading these forums, to help people and also to read SQLServer bashing. I'm a big fan of SQLServer, so when I read a jab or a bash I have to find out if what is said is true.

it wouldn't be the first time sql/server didn't support standard sql

I tried to find out the ANSI standards for SQL and the only thing I could find on MINUS is:
The MINUS keyword is not ANSI-compliant, the implementation of the MINUS operator is implemented in Oracle.
So bully for Oracle|||yeah, i love these syntax discussions too

actually, the ansi standard operator is EXCEPT

oracle's support of MINUS is non-standard

:cool:

rudy

Comparing strings in MDX

Hello gurus,

Is there an equivalent to the TSQL LIKE '%mystring%' function in MDX?

e.g. to filter a Product dimension to only those products containing 'IPOD' or whatever?

Thanks

Not in straight MDX, but it is possible to use stored procedure to do that. There is a open source project to build library of sprocs that few of us participate, and it has the implementation of Like function.

Check it out here:

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=StringFilters

HTH,

Mosha (http://www.mosha.com/msolap)

|||

Yes, there are VBA functions.

You can use InStr() as an equivalent of T-SQL like.

IMHO, using of self written stored procedure in this case is like using a cannon aganst sparrows.

|||

Mosha,

How nice to hear from you. I was at a talk by Chris Webb last Saturday and he was singing your praises. As I have been fumbling with MDX for the last couple of months I bought your Fast Track book this week because I feel I have been trying to run before I am fully clear on the basics.

The sprocs you mention look like a very useful extension to MDX, though I note that they are not recommended for a production environment at the moment. Vladimir's suggestion of using InStr seems to work fine in this case.

Thanks

|||Thanks for this Vladimir. That works just fine - I wasn't aware that these VBA functions were available.

Comparing strings in MDX

Hello gurus,

Is there an equivalent to the TSQL LIKE '%mystring%' function in MDX?

e.g. to filter a Product dimension to only those products containing 'IPOD' or whatever?

Thanks

Not in straight MDX, but it is possible to use stored procedure to do that. There is a open source project to build library of sprocs that few of us participate, and it has the implementation of Like function.

Check it out here:

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=StringFilters

HTH,

Mosha (http://www.mosha.com/msolap)

|||

Yes, there are VBA functions.

You can use InStr() as an equivalent of T-SQL like.

IMHO, using of self written stored procedure in this case is like using a cannon aganst sparrows.

|||

Mosha,

How nice to hear from you. I was at a talk by Chris Webb last Saturday and he was singing your praises. As I have been fumbling with MDX for the last couple of months I bought your Fast Track book this week because I feel I have been trying to run before I am fully clear on the basics.

The sprocs you mention look like a very useful extension to MDX, though I note that they are not recommended for a production environment at the moment. Vladimir's suggestion of using InStr seems to work fine in this case.

Thanks

|||Thanks for this Vladimir. That works just fine - I wasn't aware that these VBA functions were available.sqlsql

Comparing datetime fields results in slow query performance

Hi,
I am currently deduping a data warehouse containing around 3 million
records. Most of my dedupe scripts run in 3 or 4 minutes but the
scripts which compare datetime fields take up to 3 hours. I have a
non-clustered index on the date column. Can anybody offer any advice on
how I might improve query times please?
Thanks,
Charlie.which version of SQL Server? (2000 or 2005)
what the index plan says?
what type of comparison do you do?
what the index tunning wizard says against your query?
comparing date/time is slower then comparing integer.
<chairleg@.gmail.com> wrote in message
news:1137577249.061546.244550@.g43g2000cwa.googlegroups.com...
> Hi,
> I am currently deduping a data warehouse containing around 3 million
> records. Most of my dedupe scripts run in 3 or 4 minutes but the
> scripts which compare datetime fields take up to 3 hours. I have a
> non-clustered index on the date column. Can anybody offer any advice on
> how I might improve query times please?
> Thanks,
> Charlie.
>|||In addition to the previous post, my guess is you use the columns inside an
expression, so they are not Searchable ARGuments (SARGs) anymore. Try to
rewrite the queries to have the indexed datetime columns without an
expression in the Where clause. For example:
create table a
(a datetime)
create index aa on a(a)
insert into a values ('2006-01-16')
insert into a values ('2006-01-17')
go
select a from a
where datediff(dd,a,getdate()) < 2 -- this query should do an index scan
select a from a
where dateadd(dd,-1,convert(char(10),getdate(),112)) = a -- this query
should do an index seek
However, take care you get correct results - don't forget that you always
have time part in the datetime data. You should check if the Between
operator would be useful for you.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
<chairleg@.gmail.com> wrote in message
news:1137577249.061546.244550@.g43g2000cwa.googlegroups.com...
> Hi,
> I am currently deduping a data warehouse containing around 3 million
> records. Most of my dedupe scripts run in 3 or 4 minutes but the
> scripts which compare datetime fields take up to 3 hours. I have a
> non-clustered index on the date column. Can anybody offer any advice on
> how I might improve query times please?
> Thanks,
> Charlie.
>

Comparing datetime fields results in slow query performance

Hi,
I am currently deduping a data warehouse containing around 3 million
records. Most of my dedupe scripts run in 3 or 4 minutes but the
scripts which compare datetime fields take up to 3 hours. I have a
non-clustered index on the date column. Can anybody offer any advice on
how I might improve query times please?
Thanks,
Charlie.
which version of SQL Server? (2000 or 2005)
what the index plan says?
what type of comparison do you do?
what the index tunning wizard says against your query?
comparing date/time is slower then comparing integer.
<chairleg@.gmail.com> wrote in message
news:1137577249.061546.244550@.g43g2000cwa.googlegr oups.com...
> Hi,
> I am currently deduping a data warehouse containing around 3 million
> records. Most of my dedupe scripts run in 3 or 4 minutes but the
> scripts which compare datetime fields take up to 3 hours. I have a
> non-clustered index on the date column. Can anybody offer any advice on
> how I might improve query times please?
> Thanks,
> Charlie.
>
|||In addition to the previous post, my guess is you use the columns inside an
expression, so they are not Searchable ARGuments (SARGs) anymore. Try to
rewrite the queries to have the indexed datetime columns without an
expression in the Where clause. For example:
create table a
(a datetime)
create index aa on a(a)
insert into a values ('2006-01-16')
insert into a values ('2006-01-17')
go
select a from a
where datediff(dd,a,getdate()) < 2 -- this query should do an index scan
select a from a
where dateadd(dd,-1,convert(char(10),getdate(),112)) = a -- this query
should do an index seek
However, take care you get correct results - don't forget that you always
have time part in the datetime data. You should check if the Between
operator would be useful for you.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
<chairleg@.gmail.com> wrote in message
news:1137577249.061546.244550@.g43g2000cwa.googlegr oups.com...
> Hi,
> I am currently deduping a data warehouse containing around 3 million
> records. Most of my dedupe scripts run in 3 or 4 minutes but the
> scripts which compare datetime fields take up to 3 hours. I have a
> non-clustered index on the date column. Can anybody offer any advice on
> how I might improve query times please?
> Thanks,
> Charlie.
>
sqlsql

Comparing 2 Columns Containing Null Values

Hi All.

I'm having some issues with what seems to be a simple update statement but is giving me grief when one or both of the columns I'm comparing are null. My statement (simplified) is as follows:-

UPDATE
TAB_A
SET
TAB_A.TRADCODE = TAB_B.TRADCODE
FROM
TADS_STAGE.DBO.UNCLBRAMDEPT TAB_B
JOIN
TADS.DBO.UNCLBRAMDEPT TAB_A
ON
TAB_B.BRANCODE = TAB_A.BRANCODE
AND
TAB_B.MERDCODE = TAB_A.MERDCODE
AND
(
TAB_B.TRADCODE <> TAB_A.TRADCODE
)

If either of the TRADCODE fields (or both) are null then the comparison fails to return the row to update. I've tried setting the ANSI_NULLS setting to off, this has no effect, presumably because it will only work when comparing a column to a variable or evaluating if the column is null itself.

I've considered using ISNULL, but if one of the columns happens to contain the value that I specify as the replacement value then the comparison will result true and not include the row.

I'd be grateful for any pointers!

Thanks in advance

Sorted it :)

I've used the ISNULL(exp,'') function, as I'm not bothered about updating nothing ('') to null and vice versa.

Just got to go back and alter all 60 scripts now

Nick

|||

Nick Colebourn wrote:

I've considered using ISNULL, but if one of the columns happens to contain the value that I specify as the replacement value then the comparison will result true and not include the row.

In this type of situation you would replace your ISNULL with some impossible calue that would never come up. Here are some other ideas:

1. You only want to update Tab_A with Tab_B's value if they're different. Why not just update it all the time?

Cons: Slower Update, More Locks, More overhead if set to full recovery mode.

UPDATE
TAB_A
SET
TAB_A.TRADCODE = TAB_B.TRADCODE
FROM
TADS_STAGE.DBO.UNCLBRAMDEPT TAB_B
JOIN
TADS.DBO.UNCLBRAMDEPT TAB_A
ON
TAB_B.BRANCODE = TAB_A.BRANCODE
AND
TAB_B.MERDCODE = TAB_A.MERDCODE

2. Make your JOIN even more complicated

UPDATE
TAB_A
SET
TAB_A.TRADCODE = TAB_B.TRADCODE
FROM
TADS_STAGE.DBO.UNCLBRAMDEPT TAB_B
JOIN
TADS.DBO.UNCLBRAMDEPT TAB_A
ON
TAB_B.BRANCODE = TAB_A.BRANCODE
AND
TAB_B.MERDCODE = TAB_A.MERDCODE
AND
(
(TAB_B.TRADCODE IS NULL AND TAB_A.TRADCODE IS NOT NULL)

OR (TAB_B.TRADCODE IS NOT NULL AND TAB_A.TRADCODE IS NULL)

OR TAB_B.TRADCODE <> TAB_A.TRADCODE
)

|||

Hi Jared,

Thanks for your reply. I think I'm going to take a multi faceted approach to this, some of my tables are only a few thousand rows, so I'll probably just do the full update, and some are 20 million rows or more, so I'll spend the time writing the intricate where clauses or experimenting with the ISNULL function.

Cheers for taking the time to respond.

Nick

compare child records - t-sql

I have four records, each containing a date.
LineNo ShipDate RefLine
--
1 1/30/05 <null>
2 1/1/05 1
3 1/15/05 1
I want to compare the date in line 1 to 2, then 2 to 3
Basically, the lines belong to the same order. What is going on is
when we want to change a ship date for an order line, we make a copy of
the original line (line 1 in this case), close the copied line out
(line 2 or 3) and reference the original line in it using a
user-defined field (labeled RefLine). The user then goes back to the
original line and changes the ship date. We are trying to track how
many times an orderline is pushed out, and calculate the days between.
The reason we cant just make a new line and set the date there has to
do with links in the original that can't be easily changed. basically
I want my query to look like:
LineNo ShipDate RefLine DaysMoved
---
1 1/30/05 <null> 15
2 1/1/05 1 <null> (this was org ship date)
3 1/15/05 1 15
---
Total 30
I can get the group total just by getting the difference between the
current and min dates, but in my report if I sum the over all using
this method I get 60 days because there are two detail lines.
Anyone help is appreciated...I figured it out. Here is the query I used:
SELECT
co.CUSTOMER_ID
, co.ORDER_DATE
, c.NAME AS 'CUSTOMER_NAME'
, cl.CUST_ORDER_ID AS 'ORDER_ID'
, cl.LINE_NO
, cl.ORDER_QTY
, cl.PART_ID
, m.LINE_NO AS 'MOVE_LINE_NO'
, m.DESIRED_SHIP_DATE
, m.REASON_CODE
, m.REASON_CODE_DESCRIPTION
, (SELECT TOP 1 DESIRED_SHIP_DATE FROM RV_MOVED_SHIP_DATES WHERE
order_id = cl.CUST_ORDER_ID AND
line_no > m.line_no AND
COPIED_FROM_LN = cl.LINE_NO AND
m.DESIRED_SHIP_DATE <
DESIRED_SHIP_DATE) AS 'NEXT_SHIP_DATE'
, cl.DESIRED_SHIP_DATE AS 'CURRENT_DATE'
, m.COPIED_FROM_LN
FROM
dbo.CUSTOMER_ORDER co
INNER JOIN
dbo.CUST_ORDER_LINE cl ON co.ID = cl.CUST_ORDER_ID
INNER JOIN
dbo.CUSTOMER c ON co.CUSTOMER_ID = c.ID
INNER JOIN
dbo.RV_MOVED_SHIP_DATES m ON cl.LINE_NO =
m.COPIED_FROM_LN AND
cl.CUST_ORDER_ID = m.ORDER_ID
WHERE
co.ORDER_DATE BETWEEN @.START_DATE AND @.END_DATE
ORDER BY
co.CUSTOMER_ID, cl.CUST_ORDER_ID, cl.LINE_NO, m.LINE_NO
Stephen wrote:
> I have four records, each containing a date.
> LineNo ShipDate RefLine
> --
> 1 1/30/05 <null>
> 2 1/1/05 1
> 3 1/15/05 1
> I want to compare the date in line 1 to 2, then 2 to 3
> Basically, the lines belong to the same order. What is going on is
> when we want to change a ship date for an order line, we make a copy of
> the original line (line 1 in this case), close the copied line out
> (line 2 or 3) and reference the original line in it using a
> user-defined field (labeled RefLine). The user then goes back to the
> original line and changes the ship date. We are trying to track how
> many times an orderline is pushed out, and calculate the days between.
> The reason we cant just make a new line and set the date there has to
> do with links in the original that can't be easily changed. basically
> I want my query to look like:
> LineNo ShipDate RefLine DaysMoved
> ---
> 1 1/30/05 <null> 15
> 2 1/1/05 1 <null> (this was org ship date)
> 3 1/15/05 1 15
> ---
> Total 30
> I can get the group total just by getting the difference between the
> current and min dates, but in my report if I sum the over all using
> this method I get 60 days because there are two detail lines.
> Anyone help is appreciated...

command scripts

I have created some stored procedures (SP) containing DBCC commands for
system maintenance and status checks. Does anybody know where to get sample
windows command scripts that will wrap all of my SPs, create logs, and also
where the windows scheduler could run at specific times. The idea is to
automate the maintenance instead of running individual SPs or DBCCs in Query
Analyzer.
Thanks...Schedule them using SQLServerAgent - Look up the service features in Books
online.
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:3AC72F6E-B0A3-4238-9021-DA964E4A8C06@.microsoft.com...
>I have created some stored procedures (SP) containing DBCC commands for
> system maintenance and status checks. Does anybody know where to get
> sample
> windows command scripts that will wrap all of my SPs, create logs, and
> also
> where the windows scheduler could run at specific times. The idea is to
> automate the maintenance instead of running individual SPs or DBCCs in
> Query
> Analyzer.
> Thanks...|||Create a job and use the SQL Server Agent for scheduling it.
Maybe you can check this link
http://www.sql-server-performance.c...erver_agent.asp
Hope this helps.|||Thanks..
"Omnibuzz" wrote:

> Create a job and use the SQL Server Agent for scheduling it.
> Maybe you can check this link
> http://www.sql-server-performance.c...erver_agent.asp
>
> Hope this helps.

comma deliminited coloumn

I have two tables. One table (Table 1) has a column containing comma
delaminated email addresses. The other table (Table 2) has a column
with just one email address in it. I need to perform a query that
joins the comma delaminated table (Table 1) to Table 2, when the single
email address in Table 2, is contained in the list of email addresses
in (Table 1).

I hope this isn't too cryptic, and I know comma delaminated lists are
bad, but I can't do anything about that.

I need a select statement that can perform this task. Anyone have any
suggestions?--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

Set the WHERE clause like this:

WHERE table1.columnA like '%' + table2.columnA + '%'

BTW, it's "delimited" not "delaminated." Delaminated means to remove
the lamination (thin layers of some material) from a surface.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAYMHoechKqOuFEgEQLfUgCgiFts2PFVLGIgVtIozCwnTc AinqsAoOIg
GOmdcAyO5uy641wMCQWwt4Sj
=3bUQ
--END PGP SIGNATURE--

cholmqui@.gmail.com wrote:
> I have two tables. One table (Table 1) has a column containing comma
> delaminated email addresses. The other table (Table 2) has a column
> with just one email address in it. I need to perform a query that
> joins the comma delaminated table (Table 1) to Table 2, when the single
> email address in Table 2, is contained in the list of email addresses
> in (Table 1).
> I hope this isn't too cryptic, and I know comma delaminated lists are
> bad, but I can't do anything about that.
> I need a select statement that can perform this task. Anyone have any
> suggestions?|||Thank you for the reply.

still doesn't work...

Sorry about the lamination, I kinda flew through the spell checker|||(cholmqui@.gmail.com) writes:
> Thank you for the reply.
> still doesn't work...

MGFosters looks good to me at a glance. Maybe you could be more specfic
to what does not work? Even better, provide CREATE TABLE statments and
INSERT statements with sample data. Then you can get a tested solution.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||This design seems to suck. You need some procedural routines to scrub
data, You need to look at Melisa data's website.

Combining XML Files using SSIS or T-SQL etc.

How can I combine all my xml files so it can be processed by SSIS in order
that I may create a table containing the imported xml files?Loop?
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:187DB17A-FA31-4236-844A-7068BE9C072E@.microsoft.com...
> How can I combine all my xml files so it can be processed by SSIS in order
> that I may create a table containing the imported xml files?
>|||What is Loop?
Could you please explain more in detail.
Thank you in advance for your assistance.
"Peter W. DeBetta" wrote:

> Loop?
> --
> Peter DeBetta, MVP - SQL Server
> http://sqlblog.com
> --
> "Terry" <Terry@.discussions.microsoft.com> wrote in message
> news:187DB17A-FA31-4236-844A-7068BE9C072E@.microsoft.com...
>
>|||SSIS now has a Control Flow Item called Foreach Loop Container. You use this
to loop through all the xml files in a specified directory. You will also
need to create a Data Flow Task in the Foreach Loop Container. The samples
that come with SQL Server 2005 have an example of using this container
control, and you can find more info in BOL.
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:5EAFCB63-AC58-4592-A7DF-786AF5CABBBF@.microsoft.com...
> What is Loop?
> Could you please explain more in detail.
> Thank you in advance for your assistance.
> "Peter W. DeBetta" wrote:
>