March 8, 2012 at 11:28 pm
Table_A consists of two columns: 'Id' and 'Name'
-> Table_A only contains distinct values.
-> For example: Id Name
1 Jerry
2 Mary
3 Angel
Table_B contains the column 'Name'
-> The column 'Name' in Table_B has the above values repeated.
-> For example: Name
Jerry
Mary
Angel
Jerry
Angel
Mary
Now I need to compare 'Table_A.Name' with 'Table_B.Name' and then
- Select the 'Id'
- Insert the value of Id to a new 'Table_C' column 'Id'
Do I need to use an array or what?
March 8, 2012 at 11:38 pm
You mean this?
create table #tab
(
id int identity(1, 1) not null,
name varchar(10)
)
create table #tab1
(
name varchar(10)
)
insert into #tab values ('A')
insert into #tab values ('B')
insert into #tab values ('C')
insert into #tab values ('D')
insert into #tab values ('E')
insert into #tab values ('F')
insert into #tab values ('G')
insert into #tab1 values ('A')
insert into #tab1 values ('B')
insert into #tab1 values ('C')
insert into #tab1 values ('D')
insert into #tab1 values ('E')
insert into #tab1 values ('F')
insert into #tab1 values ('G')
insert into #tab1 values ('A')
insert into #tab1 values ('B')
insert into #tab1 values ('C')
insert into #tab1 values ('D')
insert into #tab1 values ('E')
insert into #tab1 values ('F')
insert into #tab1 values ('G')
insert into #tab1 values ('A')
insert into #tab1 values ('B')
insert into #tab1 values ('C')
insert into #tab1 values ('D')
insert into #tab1 values ('E')
insert into #tab1 values ('F')
insert into #tab1 values ('G')
select distinct t.id, t.name
from #tab t
join #tab1 t1
on t.name = t1.name
drop table #tab
drop table #tab1
March 9, 2012 at 1:28 am
Thanks for the response. I just stared using T-SQL and SQL Server, so this is all new to me. I was given a task to normalize data and produce a report.
Original Table was imported from excel file:
CREATE TABLE [dbo].[WorkOrderDetailedReports](
[Id] [float] NULL,
[Status] [nvarchar](255) NULL,
[Description] [nvarchar](255) NULL,
[Category] [nvarchar](255) NULL,
[Workgroup Name] [nvarchar](255) NULL,
[F6] [nvarchar](255) NULL,
[Person Name] [nvarchar](255) NULL,
[Created (Date&Time)] [datetime] NULL,
[Actual Finish (Date&Time)] [datetime] NULL,
[WO Classification] [nvarchar](255) NULL,
[Closure Code] [nvarchar](255) NULL
)
So to normalize data I created new tables:
CREATE TABLE [dbo].[WorkOrder](
[WorkOrderID] [int] NOT NULL,
[WorkOrderDescription] [nvarchar](max) NULL,
[WorkOrderCreatedDate] [datetime] NULL,
[WorkOrderFinishDate] [datetime] NULL,
[CategoryID] [int] NULL,
[PersonID] [int] NULL,
[StatusID] [int] NULL,
[WorkGroupID] [int] NULL,
[WorkOrderClassificationID] [int] NULL,
[ClosureCodeID] [int] NULL,
CONSTRAINT [PK_Work_Order] PRIMARY KEY CLUSTERED
(
[WorkOrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
And for the remaining columns of the original imported table, I created tables like the one below:
CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] [nvarchar](50) NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Now I need to compare the CategoryName to the original tables Category column (which value/s are = CategoryName values, but are repeated where as the Category table only stored the distinct values).
Then Select CategoryID and insert it into the table WorkOrder.CategoryID where WorkOrderID = Id(from the original table)
March 9, 2012 at 1:36 am
The idea is still same. Join (probably left join) your original table with the category table with condition
on originaltable.category = categorytable.categoryname
you will get id of matching category name. Do you need the query?
March 9, 2012 at 1:39 am
This is what I did:
insert into w.CategoryID
select distinct c.CategoryID, c.CategoryName
from Category c
join WorkOrder w join WorkOrderDetailedReports wod
on c.CategoryName = wod.Category
on w.WorkOrderID = wod.Id
What would you suggest?
March 9, 2012 at 1:52 am
CREATE TABLE [dbo].[WorkOrderDetailedReports](
[Id] [float] NULL,
[Status] [nvarchar](255) NULL,
[Description] [nvarchar](255) NULL,
[Category] [nvarchar](255) NULL,
[Workgroup Name] [nvarchar](255) NULL,
[F6] [nvarchar](255) NULL,
[Person Name] [nvarchar](255) NULL,
[Created (Date&Time)] [datetime] NULL,
[Actual Finish (Date&Time)] [datetime] NULL,
[WO Classification] [nvarchar](255) NULL,
[Closure Code] [nvarchar](255) NULL
)
CREATE TABLE [dbo].[WorkOrder](
[WorkOrderID] [int] NOT NULL,
[WorkOrderDescription] [nvarchar](max) NULL,
[WorkOrderCreatedDate] [datetime] NULL,
[WorkOrderFinishDate] [datetime] NULL,
[CategoryID] [int] NULL,
[PersonID] [int] NULL,
[StatusID] [int] NULL,
[WorkGroupID] [int] NULL,
[WorkOrderClassificationID] [int] NULL,
[ClosureCodeID] [int] NULL,
CONSTRAINT [PK_Work_Order] PRIMARY KEY CLUSTERED
(
[WorkOrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] [nvarchar](50) NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
insert into [WorkOrderDetailedReports] values (1, 'A', 'AA', 'A', 'AAA', 'AAAA', 'AAAAA', getdate(), getdate(), 'A', 'A')
insert into [WorkOrderDetailedReports] values (2, 'B', 'BB', 'B', 'BBB', 'BBBB', 'BBBBB', getdate(), getdate(), 'B', 'B')
insert into [WorkOrderDetailedReports] values (3, 'C', 'CC', 'C', 'CCC', 'CCCC', 'CCCCC', getdate(), getdate(), 'C', 'C')
insert into [WorkOrderDetailedReports] values (4, 'A', 'AA', 'A', 'AAA', 'AAAA', 'AAAAA', getdate(), getdate(), 'A', 'A')
insert into [Category] values ('A')
insert into [Category] values ('B')
insert into [Category] values ('C')
insert into [Category] values ('D')
insert into [WorkOrder]
(
[WorkOrderID],
[WorkOrderDescription],
[WorkOrderCreatedDate],
[WorkOrderFinishDate],
[CategoryID],
[PersonID],
[StatusID],
[WorkGroupID],
[WorkOrderClassificationID],
[ClosureCodeID]
)
select
a.ID,
a.[Description],
a.[Created (Date&Time)],
a.[Actual Finish (Date&Time)],
b.[CategoryID],
1,
1,
1,
1,
1
from [WorkOrderDetailedReports] a
join [Category] b
on a.category = b.categoryname
select * from [WorkOrder]
drop table [Category]
drop table [WorkOrder]
drop table [WorkOrderDetailedReports]
March 9, 2012 at 2:28 am
Thanks your method works! Just needed to truncate WorkOrder table and resubmit query.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply