Comparing columns in two tables

Hi DBA's,

I need to figure out how to compare the column data in two distinct tables. I have two files that populate these two tables. Basically I am doing a file comparison here. Let me explain the process:

Table 1

Col 1 Col 2

ID Name

1 A
2 C,D
3 F

Table 2

Col

Name

E
F
D

Now if there is any data that is present in Table 2 that matches with the data in table 1 then I need to write the entire record of table 2 into a separate table OR file.

Here is what I think I need to do.

1. Take first record from Table 1 and scan Table 2 to see if the Name 'A' exists. If yes put/insert the record from Table 2 in a seprate table say table 3 and then go to the second record. If no match then go directly to the second record in table 1. Repeat the process till every record in table 1 is compared to the records in table 2.

2. Now the trick here is some Names have only last name. Others have last name and first name. So for Table 1, Name C,D should be a match to D in Table 2. I have to send this record to Table 3. How do I accomplish that? Should I spilt the Col2 into columns. How do I do that?

Please note that table 2 would have close to 5000 records.

Please advise.

Thanks in anticipation.Think you'll be having a lot of false matches on Smtih and Jones...

Maybe not Kaiser though...

Can you post the DDL of the Tables, and sample data..like

CREATE TABLE myTable99 (Col1 int, Col2, varchar(50), ect

For Sample Data, something like..

INSERT INTO myTable99 (Col1, Col2, ect)
SELECT 1, 'Brett Kaiser', ect UNION ALL
SELECT 2, 'Indiana Jones', ect UNION ALL
SELECT 3, 'Jones', ect UNION ALL
SELECT 4, 'Jeff Smith', ect UNION ALL

get the picture?

It's easier to help when we have the actual stuff...

Still think the matching will be a fudge though..

maybe you can match on exact, remove that population, then do the fudge on a smaller subset...sqlsql

Common Table Expression?

What is the SQL Server equivalent of DB2 common table expressions? For
example,

with gry(year,count) as(
select floor(sem/10),count(distinct ssn)
from grades
group by floor(sem/10)
)
select year,sum(count) Head_Count from gry
group by year
having year >= 1980;

N. Shamsundar
University of Houston"N. Shamsundar" <shamsundar_AT_uh.edu@.nospam.xyz> wrote in message
news:c4npes$3ecc$1@.masala.cc.uh.edu...
> What is the SQL Server equivalent of DB2 common table expressions? For
> example,
> with gry(year,count) as(
> select floor(sem/10),count(distinct ssn)
> from grades
> group by floor(sem/10)
> )
> select year,sum(count) Head_Count from gry
> group by year
> having year >= 1980;
> N. Shamsundar
> University of Houston

If you have a lot of queries which will reference the CTE, you could create
a view or table-valued function. If you only have a few queries, or if you
can't create a view or function for some reason, then a derived table is
probably the only other alternative. Your example seems to be relatively
simple, so I guess any of these options will work, but in more complex cases
a function might give you the most flexibility. CTEs will be in Yukon, by
the way.

Simon|||something like this:

*/ Untested! */
select
a.year,
a.sum(amount)

from
(select floor(sem/10) as year ,count(distinct ssn) as amount
from grades
group by floor(sem/10)
) as a

where
year >=1980

group by a.year

Comma seperated IDS in SELECT query with IN.

How can I use list of comma separated IDS in SELECT query with IN.
DECLARE @.TaskID varchar(200)
SET @.TaskID = '(30,32)'
SELECT DISTINCT UserID
FROM tbl_UserTask
WHERE TaskID IN @.TaskIDI do not want to use Dynamic SQL|||Look at Dejan's example
IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO
CREATE FUNCTION dbo.TsqlSplit
(@.List As varchar(8000))
RETURNS @.Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @.Item As varchar(8000), @.Pos As int
WHILE DATALENGTH(@.List)>0
BEGIN
SET @.Pos=CHARINDEX(',',@.List)
IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(',
'),8000)
END
RETURN
END
GO
/* Usage example */
SELECT t1.*
FROM TsqlSplit('10428,10429') AS t1
declare @.inList varchar(50)
set @.inList='10428,10429'
select od.* from [order details] od
INNER JOIN
(SELECT Item
FROM dbo.TsqlSplit(@.InList)) As t
ON od.orderid = t.Item
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1140690585.836794.90030@.g14g2000cwa.googlegroups.com...
>I do not want to use Dynamic SQL
>|||Look at this function by Dejan Sarka:
http://solidqualitylearning.com/blo.../10/22/200.aspx
ML
http://milambda.blogspot.com/|||Thanks Uri,
It solved my problem.|||http://www.aspfaq.com/2248
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1140690353.281661.41550@.v46g2000cwv.googlegroups.com...
> How can I use list of comma separated IDS in SELECT query with IN.
> DECLARE @.TaskID varchar(200)
> SET @.TaskID = '(30,32)'
> SELECT DISTINCT UserID
> FROM tbl_UserTask
> WHERE TaskID IN @.TaskID
>|||or
DECLARE @.TaskID varchar(200)
SET @.TaskID = '30,32'
SELECT DISTINCT UserID
FROM tbl_UserTask
WHERE ','+@.TaskID+',' like '%,'+cast(TaskID as varchar)+',%'
Madhivanan

Comma delimited list of a given field from records

I have a list that recieves a distinct field from SQL query. I want to
display this list as a comma delimited string of values, i.e. val1, val2,
val3, ...
The query looks something like this "SELECT DISTINCT val FROM TABLE"
However when I create a list associate it with a Dataset that represents the
query and then put that field inside a textbox inside the list region I get
all the values seperated by newlines. Like this,
Val1
Val2
Val3
...
This is clearly not the desired result. Is there an easy way to control the
delimiter between the records?
--
Thank you,
JohnHi,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to know whether it is
possible to deliver the result with the format of comma delimited in a row.
If I have misunderstood your concern, please feel free to point it out.
You may refer the sample below to build up your queries or stored procedures
use Northwind
GO
declare @.str varchar(8000)
set @.str=''
select @.str=@.str+convert(varchar,OrderID)+',' FROM Orders WHERE EmployeeID
= 3
set @.str=left(@.str,len(@.str)-1)
print @.str
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hey that would be great if I could use TSQL but my solution must be database
agnostic. If I could use TSQL I would right a SP and be done with.
What would be nice is a List aggregate function in Reporting Services that
does just what Sybase's SQL Anywhere does, i.e.
SELECT LIST(DISTINCT, valueToAggregate) FROM MyTable
So in Reporting Serices it would look like this.
=List(Fields!valueToAggregate.Value)
Can I extend Reporting Services Aggregate functions somehow so that I can
create my own list function?
--
Thank you,
John
"Michael Cheng [MSFT]" wrote:
> Hi,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood you would like to know whether it is
> possible to deliver the result with the format of comma delimited in a row.
> If I have misunderstood your concern, please feel free to point it out.
> You may refer the sample below to build up your queries or stored procedures
> use Northwind
> GO
> declare @.str varchar(8000)
> set @.str=''
> select @.str=@.str+convert(varchar,OrderID)+',' FROM Orders WHERE EmployeeID
> = 3
> set @.str=left(@.str,len(@.str)-1)
> print @.str
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||Check out using a matrix and see if that will do what you want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John A" <i-code4food@.newsgroups.nospam> wrote in message
news:C73D1939-D114-4257-B2C4-F8B57E525A61@.microsoft.com...
> Hey that would be great if I could use TSQL but my solution must be
> database
> agnostic. If I could use TSQL I would right a SP and be done with.
> What would be nice is a List aggregate function in Reporting Services that
> does just what Sybase's SQL Anywhere does, i.e.
> SELECT LIST(DISTINCT, valueToAggregate) FROM MyTable
> So in Reporting Serices it would look like this.
> =List(Fields!valueToAggregate.Value)
> Can I extend Reporting Services Aggregate functions somehow so that I can
> create my own list function?
> --
> Thank you,
> John
>
> "Michael Cheng [MSFT]" wrote:
>> Hi,
>> Welcome to use MSDN Managed Newsgroup!
>> From your descriptions, I understood you would like to know whether it is
>> possible to deliver the result with the format of comma delimited in a
>> row.
>> If I have misunderstood your concern, please feel free to point it out.
>> You may refer the sample below to build up your queries or stored
>> procedures
>> use Northwind
>> GO
>> declare @.str varchar(8000)
>> set @.str=''
>> select @.str=@.str+convert(varchar,OrderID)+',' FROM Orders WHERE
>> EmployeeID
>> = 3
>> set @.str=left(@.str,len(@.str)-1)
>> print @.str
>> Thank you for your patience and cooperation. If you have any questions or
>> concerns, don't hesitate to let me know. We are always here to be of
>> assistance!
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>>|||With 2005 you can:
http://msdn2.microsoft.com/en-us/library/ms165055.aspx
--
William Stacey [MVP]
"John A" <i-code4food@.newsgroups.nospam> wrote in message
news:C73D1939-D114-4257-B2C4-F8B57E525A61@.microsoft.com...
> Hey that would be great if I could use TSQL but my solution must be
> database
> agnostic. If I could use TSQL I would right a SP and be done with.
> What would be nice is a List aggregate function in Reporting Services that
> does just what Sybase's SQL Anywhere does, i.e.
> SELECT LIST(DISTINCT, valueToAggregate) FROM MyTable
> So in Reporting Serices it would look like this.
> =List(Fields!valueToAggregate.Value)
> Can I extend Reporting Services Aggregate functions somehow so that I can
> create my own list function?
> --
> Thank you,
> John
>
> "Michael Cheng [MSFT]" wrote:
>> Hi,
>> Welcome to use MSDN Managed Newsgroup!
>> From your descriptions, I understood you would like to know whether it is
>> possible to deliver the result with the format of comma delimited in a
>> row.
>> If I have misunderstood your concern, please feel free to point it out.
>> You may refer the sample below to build up your queries or stored
>> procedures
>> use Northwind
>> GO
>> declare @.str varchar(8000)
>> set @.str=''
>> select @.str=@.str+convert(varchar,OrderID)+',' FROM Orders WHERE
>> EmployeeID
>> = 3
>> set @.str=left(@.str,len(@.str)-1)
>> print @.str
>> Thank you for your patience and cooperation. If you have any questions or
>> concerns, don't hesitate to let me know. We are always here to be of
>> assistance!
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>>|||Thanks Bruce but I am really at a loss as to how this will help me after
investigating it can you throw me a clue?
--
Thank you,
John
"Bruce L-C [MVP]" wrote:
> Check out using a matrix and see if that will do what you want.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John A" <i-code4food@.newsgroups.nospam> wrote in message
> news:C73D1939-D114-4257-B2C4-F8B57E525A61@.microsoft.com...
> > Hey that would be great if I could use TSQL but my solution must be
> > database
> > agnostic. If I could use TSQL I would right a SP and be done with.
> >
> > What would be nice is a List aggregate function in Reporting Services that
> > does just what Sybase's SQL Anywhere does, i.e.
> > SELECT LIST(DISTINCT, valueToAggregate) FROM MyTable
> >
> > So in Reporting Serices it would look like this.
> > =List(Fields!valueToAggregate.Value)
> >
> > Can I extend Reporting Services Aggregate functions somehow so that I can
> > create my own list function?
> >
> > --
> > Thank you,
> > John
> >
> >
> > "Michael Cheng [MSFT]" wrote:
> >
> >> Hi,
> >>
> >> Welcome to use MSDN Managed Newsgroup!
> >>
> >> From your descriptions, I understood you would like to know whether it is
> >> possible to deliver the result with the format of comma delimited in a
> >> row.
> >> If I have misunderstood your concern, please feel free to point it out.
> >>
> >> You may refer the sample below to build up your queries or stored
> >> procedures
> >>
> >> use Northwind
> >> GO
> >> declare @.str varchar(8000)
> >> set @.str=''
> >> select @.str=@.str+convert(varchar,OrderID)+',' FROM Orders WHERE
> >> EmployeeID
> >> = 3
> >> set @.str=left(@.str,len(@.str)-1)
> >> print @.str
> >>
> >> Thank you for your patience and cooperation. If you have any questions or
> >> concerns, don't hesitate to let me know. We are always here to be of
> >> assistance!
> >>
> >>
> >> Sincerely yours,
> >>
> >> Michael Cheng
> >> Microsoft Online Partner Support
> >>
> >> When responding to posts, please "Reply to Group" via your newsreader so
> >> that others may learn and benefit from your issue.
> >> =====================================================> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >>
> >>
>
>|||Hey thanks, that's way cool. It appears that this modifies the actual SQL
Server instance and that would be great if we weren't using Oracle 85% of the
time. Not my choice BTW. I also found a way to create an aggregate function
for Oracle to use and I suppose I could create all kinds of aggregate
functions on all our server for each Database Vendor we support. However, I
don't think I need to tell you what is wrong with this picture.
What I really need the ability to do is create a new aggregate function that
Reporting Services uses. Or am I missing something here. When I create the
new method for SQL 2005 is it available for use as a Reporting Services
aggregate?
--
Thank you,
John
"William Stacey [MVP]" wrote:
> With 2005 you can:
> http://msdn2.microsoft.com/en-us/library/ms165055.aspx
> --
> William Stacey [MVP]
> "John A" <i-code4food@.newsgroups.nospam> wrote in message
> news:C73D1939-D114-4257-B2C4-F8B57E525A61@.microsoft.com...
> > Hey that would be great if I could use TSQL but my solution must be
> > database
> > agnostic. If I could use TSQL I would right a SP and be done with.
> >
> > What would be nice is a List aggregate function in Reporting Services that
> > does just what Sybase's SQL Anywhere does, i.e.
> > SELECT LIST(DISTINCT, valueToAggregate) FROM MyTable
> >
> > So in Reporting Serices it would look like this.
> > =List(Fields!valueToAggregate.Value)
> >
> > Can I extend Reporting Services Aggregate functions somehow so that I can
> > create my own list function?
> >
> > --
> > Thank you,
> > John
> >
> >
> > "Michael Cheng [MSFT]" wrote:
> >
> >> Hi,
> >>
> >> Welcome to use MSDN Managed Newsgroup!
> >>
> >> From your descriptions, I understood you would like to know whether it is
> >> possible to deliver the result with the format of comma delimited in a
> >> row.
> >> If I have misunderstood your concern, please feel free to point it out.
> >>
> >> You may refer the sample below to build up your queries or stored
> >> procedures
> >>
> >> use Northwind
> >> GO
> >> declare @.str varchar(8000)
> >> set @.str=''
> >> select @.str=@.str+convert(varchar,OrderID)+',' FROM Orders WHERE
> >> EmployeeID
> >> = 3
> >> set @.str=left(@.str,len(@.str)-1)
> >> print @.str
> >>
> >> Thank you for your patience and cooperation. If you have any questions or
> >> concerns, don't hesitate to let me know. We are always here to be of
> >> assistance!
> >>
> >>
> >> Sincerely yours,
> >>
> >> Michael Cheng
> >> Microsoft Online Partner Support
> >>
> >> When responding to posts, please "Reply to Group" via your newsreader so
> >> that others may learn and benefit from your issue.
> >> =====================================================> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >>
> >>
>
>|||Hi,
It is not possible to use custom function in the Reporting Services
dataset, but you may try data extension.
For SQL Server 2000 Reporting Services
Custom Dataset Data Extension for Microsoft Reporting Services
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=b8468
707-56ef-4864-ac51-d83fc3273fe5
For SQL Server 2005 Reporting Services
Using an External Dataset with Reporting Services
http://msdn2.microsoft.com/en-us/library/ms152917.aspx
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you Michael, even if this does not help me with this particular problem
and it well may, I it is a great tool to have in my RS bag of tricks.
I went through the sample and am having a problem with the TestDS.rdl report
on my machine. The error I am getting is -
An error has occured during the report processing.
Query execution failed for data set 'DataSet1'.
Request for the permission of type
System.Security.Permissions.FileIOPermission, mscorlib, Version=1.0.5000.0,
Culture=neutral,
PublicKeyToken=b77a5c561934e089 failed.
I gave the folder that contains the .XSD file read and execute permissions
for IWAM, IUSR and local\User account but I still get that error.
What can I do to resolve this problem?
--
Thank you,
John
"Michael Cheng [MSFT]" wrote:
> Hi,
> It is not possible to use custom function in the Reporting Services
> dataset, but you may try data extension.
> For SQL Server 2000 Reporting Services
> Custom Dataset Data Extension for Microsoft Reporting Services
> http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=b8468
> 707-56ef-4864-ac51-d83fc3273fe5
> For SQL Server 2005 Reporting Services
> Using an External Dataset with Reporting Services
> http://msdn2.microsoft.com/en-us/library/ms152917.aspx
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi John,
I think this KB might help you
How to grant permissions to a custom assembly that is referenced in a
report in Reporting Services
http://support.microsoft.com/kb/842419
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||If you can get the data into a temporary table on a SQL2005 server,
then you could use the new PIVOT function to get the list sideways! Or
just show it in a matrix.
You could certainly get the Oracle data into the SQL Server DB as a
Linked Server.
Whilst it uses T-SQL, your data source can still be any common DB
system.
Alternatively, you could do it in code using ADO.Net to read the data
in and then use your CLR language of choice to concatenate the values
together. Then use the result to populate a textbox if that's what
you're after.
Cheers
Chris
John A wrote:
> I have a list that recieves a distinct field from SQL query. I want
> to display this list as a comma delimited string of values, i.e.
> val1, val2, val3, ...
> The query looks something like this "SELECT DISTINCT val FROM TABLE"
> However when I create a list associate it with a Dataset that
> represents the query and then put that field inside a textbox inside
> the list region I get all the values seperated by newlines. Like this,
> Val1
> Val2
> Val3
> ...
> This is clearly not the desired result. Is there an easy way to
> control the delimiter between the records?