Sql Comparing two tables: Complicated Query Question

How can the data in a relational table be parsed into a flat file table?
For instance; table1 has an id for each entry and an sid and name, table2 has an
sid and name1 name2 name3...456.
I have both tables in the same database and want to import the data from table1
into table2. The records in table1 with identical sid's should create only one
record in table2. Here is a sample of the tables, and what I have started
with>>
CREATE DATABASE SAMPLE002
GO
USE SAMPLE002
CREATE TABLE table1
(
sID int NOT NULL,
Name1 varchar(50) NULL,
Name2 varchar(50) NULL,
Name3 varchar(50) NULL,
Name4 varchar(50) NULL,
Name5 varchar(50) NULL,
Name6 varchar(50) NULL
)
GO
INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (1,
'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (2,
'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
GO
CREATE TABLE table2
(
pID int IDENTITY (1, 1) NOT NULL,
sID int NOT NULL,
Name varchar(50) NOT NULL
)
GO
INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
--View the tables as they are
select * from table1
select * from table2
--I tried this first but of course it duplicates the sid in table 2 and that
can't happen. I expect there needs to be a looping process on each sid but I am
not sure how to write that
insert into table1 (sid,Name1)
select sid,Name from table2
--Use this to clean out the mess made by the previous insert
delete from table1
where sid >2
THANK YOU !!!
=================================
Try this:
select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
from table2 as a
join table2 as b on b.sID = a.sID and b.Name < a.Name
join table2 as c on c.sID = a.sID and c.Name < b.Name
join table2 as d on d.sID = a.sID and d.Name < c.Name
join table2 as e on e.sID = a.sID and e.Name < d.Name
join table2 as f on f.sID = a.sID and f.Name < e.Name
This will give one row with the names in alphabetical order.
You need as many tables as the number of entries per sID in table2. If table2 does not always have six entries for each
sID, use OUTER JOINs
WANNABE wrote:
> How can the data in a relational table be parsed into a flat file table?
> For instance; table1 has an id for each entry and an sid and name, table2 has an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from table1
> into table2. The records in table1 with identical sid's should create only one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and that
> can't happen. I expect there needs to be a looping process on each sid but I am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================
>
|||After I posted, I realized that outer joins will not give you what you want. They will just give you all permutations
of the names.
I will have to give this some more thought. I have never run into this situation before, but someone else probably has.
Maybe they have a neat solution.
WANNABE wrote:
> There's much that I do not understand;
> Can I use the select statement on it's own to view what will be inserted into
> the table when I run the full query?
> Does this join table2 to itself 6 times?
> Table2 does not always have 6 entries per sid, and neither does table1, but
> should I use FULL OUTER?
> HOW is this suppose to work?
> ======================================
> "Ed Enstrom" <nospam@.invalid.net> wrote in message
> news:Ey0di.75$LW6.21@.newsfe12.lga...
> Try this:
> select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
> from table2 as a
> join table2 as b on b.sID = a.sID and b.Name < a.Name
> join table2 as c on c.sID = a.sID and c.Name < b.Name
> join table2 as d on d.sID = a.sID and d.Name < c.Name
> join table2 as e on e.sID = a.sID and e.Name < d.Name
> join table2 as f on f.sID = a.sID and f.Name < e.Name
> This will give one row with the names in alphabetical order.
> You need as many tables as the number of entries per sID in table2. If table2
> does not always have six entries for each
> sID, use OUTER JOINs
> WANNABE wrote:
>
|||I think you got your tables mixed up -

CREATE TABLE Wtable1
(
pID int primary key,
sID int NOT NULL,
Name varchar(8) NOT NULL
)
GO
INSERT INTO Wtable1 (pID,sID,Name) VALUES (1,3, 'Robert')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (2,3, 'Roger')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (3,3, 'Edgar')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (4,3, 'Mitch')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (5,4, 'Julie')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (6,4, 'Wendy')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (7,4, 'Roberto')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (8,5, 'Jean')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (9,5, 'Jill')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (10,5, 'Alice')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (11,5, 'Claire')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (12,5, 'Steve')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (13,5, 'Hugh')
CREATE TABLE Wtable2
(
sID int primary key,
Name1 varchar(8) NULL,
Name2 varchar(8) NULL,
Name3 varchar(8) NULL,
Name4 varchar(8) NULL,
Name5 varchar(8) NULL,
Name6 varchar(8) NULL
)
GO
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(1,
'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(2,
'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
The RAC utility was designed by a brillant developer (I know him) to
solve these types of problems in the easiest and lest painful way
possible. This is really a pivoting problem and Rac does this well.
Rac is a system of stored procedures and functions that run in
sql server (2k/2005). It has many parameters that allow you to
manipulate (pivot) data and get exactly the result you want. You can
browse the site and look at the online Help to check out each
parameter.
Just insert the Rac result into Wtable2:
insert into Wtable2
Exec Rac
@.transform='Max(Name) as [Name]',
@.rows='sID',
@.pvtcol='pID',
--@.ranklimit would be the maximum NameN in Wtable2
@.from='Wtable1',@.rank='Col',@.ranklimit='6',
@.defaults1='y',@.racheck='y',@.shell='n',
@.replacepvtcols=
'{case when [value]>~~ then [value] else null end for []}',
@.select='select 1*sID as sID,_pvtcols_
from rac '
select * from Wtable2:
sID Name1 Name2 Name3 Name4 Name5 Name6
-- -- -- -- -- -- --
1 Hal Jenny Hank Mary NULL NULL
2 Leroy Anne Curtis NULL NULL NULL
3 Robert Roger Edgar Mitch NULL NULL
4 Julie Wendy Roberto NULL NULL NULL
5 Jean Jill Alice Claire Steve Hugh
Rac inserted rows 3-5.
Rac @.
www.rac4sql.net
More @.
www.beyondsql.blogspot.com
|||Warning: code not run, hence may have typos.
with T1 as (
select sId, name1 as Name from table1 where name1 is not null
union -- not union ALL, so as to eliminate duplicates
select sId, name2 from table1 where name2 is not null
union
... etc. ...
) -- you could also use the UNPIVOT operator to create T1 (briefer code)
insert into table2
select * from T1
where not exists (select * from table2 T2
where T2.sId = T1.Sid and T2.name = T1.Name)
"WANNABE" <SameAsB4> wrote in message
news:uE3dc1FsHHA.5008@.TK2MSFTNGP05.phx.gbl...
> How can the data in a relational table be parsed into a flat file table?
> For instance; table1 has an id for each entry and an sid and name, table2
> has an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from
> table1
> into table2. The records in table1 with identical sid's should create
> only one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and
> that
> can't happen. I expect there needs to be a looping process on each sid
> but I am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================
>
|||These statements should work for you. It may not be the most elegant T-SQL code, but it works.
The first statement seeds table1 with the distinct sID values from table2,
with "N/A" value for all the name columns.
The next statement updates Name1 in table1 with the lowest value in table2
for that sID.
The next statement updates Name2 in table1 with the lowest value in table2
that is not equal to the Name1 value in table1.
The next statement updates Name3 in table1 with the lowest value in table2
that is not equal to both the Name1 and Name2 values in table1.
And so on.
You need as many UPDATE statements as the maximum number of names for any sID.
For those sIDs in table2 that have fewer rows than the number of UPDATE statements, the SELECT subquery will return NULL
and the ISNULL function will substitute "N/A" for the value to be updated.
insert into table1 select distinct sID, 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A'
from table2;
update table1
set Name1 =
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID);
update table1
set Name2 =
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1);
update table1
set Name3=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2);
update table1
set Name4=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3);
update table1
set Name5=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4);
update table1
set Name6=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4
and table2.Name <> table1.Name5);
Ed Enstrom wrote:[vbcol=seagreen]
> After I posted, I realized that outer joins will not give you what you
> want. They will just give you all permutations of the names.
> I will have to give this some more thought. I have never run into this
> situation before, but someone else probably has. Maybe they have a neat
> solution.
> WANNABE wrote:
|||Thanks Mark, I was able to make this code work but it parsed the data INTO the
relational table, and I am looking to parse the data INTO the FLAT file table.
================================================== ==
"Mark Yudkin" <DoNotContactMe@.boingboing.org> wrote in message
news:%23IOEPIMsHHA.3628@.TK2MSFTNGP02.phx.gbl...
Warning: code not run, hence may have typos.
with T1 as (
select sId, name1 as Name from table1 where name1 is not null
union -- not union ALL, so as to eliminate duplicates
select sId, name2 from table1 where name2 is not null
union
... etc. ...
) -- you could also use the UNPIVOT operator to create T1 (briefer code)
insert into table2
select * from T1
where not exists (select * from table2 T2
where T2.sId = T1.Sid and T2.name = T1.Name)
"WANNABE" <SameAsB4> wrote in message
news:uE3dc1FsHHA.5008@.TK2MSFTNGP05.phx.gbl...
> How can the data in a relational table be parsed into a flat file table?
> For instance; table1 has an id for each entry and an sid and name, table2
> has an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from
> table1
> into table2. The records in table1 with identical sid's should create
> only one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and
> that
> can't happen. I expect there needs to be a looping process on each sid
> but I am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================
>
|||Thanks allot Ed, but this replaces NULL values with N/A, and replaces all the
existing Name1...6 fields with N/A
==========================================
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:lOcdi.6$Nx2.2@.newsfe12.lga...
These statements should work for you. It may not be the most elegant T-SQL
code, but it works.
The first statement seeds table1 with the distinct sID values from table2,
with "N/A" value for all the name columns.
The next statement updates Name1 in table1 with the lowest value in table2
for that sID.
The next statement updates Name2 in table1 with the lowest value in table2
that is not equal to the Name1 value in table1.
The next statement updates Name3 in table1 with the lowest value in table2
that is not equal to both the Name1 and Name2 values in table1.
And so on.
You need as many UPDATE statements as the maximum number of names for any sID.
For those sIDs in table2 that have fewer rows than the number of UPDATE
statements, the SELECT subquery will return NULL
and the ISNULL function will substitute "N/A" for the value to be updated.
insert into table1 select distinct sID, 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A'
from table2;
update table1
set Name1 =
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID);
update table1
set Name2 =
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1);
update table1
set Name3=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2);
update table1
set Name4=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3);
update table1
set Name5=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4);
update table1
set Name6=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4
and table2.Name <> table1.Name5);
Ed Enstrom wrote:[vbcol=seagreen]
> After I posted, I realized that outer joins will not give you what you
> want. They will just give you all permutations of the names.
> I will have to give this some more thought. I have never run into this
> situation before, but someone else probably has. Maybe they have a neat
> solution.
> WANNABE wrote:
|||Thanks Susan, That looks KOOL, but as a learning experiment I would like to
understand how this can be done with a simple query. Can you please explain
what you meant, "I think you got your tables mixed up" DID I DO SOMETHING
WRONG?
LOL
========================================
"Steve Dassin" <rac4sqlnospam@.net> wrote in message
news:eZRxmOLsHHA.4932@.TK2MSFTNGP02.phx.gbl...
I think you got your tables mixed up -

>For instance; table1 has an id for each entry and an sid and name, table2
has an
>sid and name1 name2 name3...456.
That's backwards -

Sunday, March 11, 2012

Complicated Query Question

No comments:

Post a Comment

Sql Comparing two tables

Blog Archive

About Me