As Eddy said, you need to normalise this data. You also need to analyse and manipulate it at bit.
Separate things out into information about a specific, physical machine and information about the type of machine.
For example, 'Made in' is going to apply to the Machine Name and Model No. and probably the Supplier that you have in brackets after the machine name. Those things would apply to those machines whichever company owns them. But Serial No. and Location will refer to the specific instances of machines owned by your company.
If this data is in a spreadsheet then move the columns around to make it more obvious.
Second thing to note, as you have pointed out, is that S.No is not unique - Nor is Machine Id. Only you will know what those are meant to represent, but when faced with data like this I just make sure it's recorded, but ignore it for the purposes of building my table linkages.
The implication is that the line with IMPIANTO in it is also 06,CC01, CHip Collector (FIDA), we don't know where it is made nor do we know where it is located. Fill in those gaps as much as you can. It makes it easier to load the data and it also helps to identify re-used words.
Now really start looking at the text itself. Start with Location - for every machine in the "Old Factory" or the "New Factory" you are storing 11 characters. Pull that information out into it's own table so that for each row you only need to store an integer - a foreign key - that "points" to information in a Location table. This also gives you the opportunity to store other information about each Location - an address, GPS, site contact number etc. For example:
create Table Location
(
LocId int identity(1,1),
LocName varchar(255),
)
insert into Location values
('Old Factory'),
('New Factory'),
('Outside')Notice that each location has a Unique identifier and I've allowed SQL to generate that for me.
Next set of obvious duplication is in the bits in brackets. They look to be Supplier names. You might also want to store other information about a supplier, so let's pull them out into a separate table
create Table Supplier
(
SupplierId int identity(1,1),
Supplier varchar(255)
)
insert into Supplier values
('CECCATO'),
('Ingersoll Rand'),
('SCM'),
('FIDA'),
('MACMAZZA'),
('SAC'),
('CENTAURO'),
('MASTERWOOD'),
('STROMAB'),
('GABBIANI')Now I started looking at the Machines themselves - in terms of Products supplied by one of those Suppliers
create Table Machine
(
MachId int identity(1,1),
MachineName varchar(255),
ModelNo varchar(255),
MadeIn varchar(255),
Supplier int,
Category char(2)
)
insert into Machine values
('Air Compressor','CSB 25/10','Italy' ,1, 'AC'),
('Piston Air Compressor', '462 TV 02' ,'Italy' ,NULL,'AC'),
('Rotary Screw Head Compressor', 'SSR-EP 100' ,'Italy' ,2,'AC'),
('Multi Boring Machine', 'MULTITECH PLUS' ,'Italy' ,3,'BM'),
('Chip Collector', 'SILO' ,'Italy' ,4,'CC'),
('Chip Collector', 'IMPIANTO' ,NULL ,4,'CC'),
('Chip Collector', 'TRC 1001' ,'Italy' ,4,'CC'),
('Chip Collector', 'TRC 711' ,NULL ,4,'CC'),
('Rip Saw', 'SI 450 N' ,'Italy' ,3,'CM'),
('Automatic Panel Sizing Center','TS100' ,'Italy' ,5,'CM'),
('Rip Saw', 'CS 4' ,'Italy' ,6,'CM'),
('Rip Saw', 'SI-350 N' ,'Italy' ,3,'CM'),
('Band Saw', 'R-800' ,'Italy' ,7,'CM'),
('Band Saw', 'CENTAURO-C' ,'Italy' ,7,'CM'),
('Multi Rip Saw', 'M-3' ,'Italy' ,3,'CM'),
('Multi Rip Saw', 'M-2' ,'Italy' ,3,'CM'),
('Chain Cutter', 'OMR 350' ,'Italy' ,8,'CM'),
('Under Side Cross Cut Saw', 'RS-110' ,'Italy' ,9,'CM'),
('Double side Cutter', NULL ,'Italy' ,10,'CM')Notice that last column Category If you look at your original data, the non-unique Machine Id # seems to follow a pattern, and that pattern appears to be linked to the Machine Type - so it's worth capturing in case we need to start generating asset numbers later.
Note also that I didn't bother pulling the "Made In" column out - it's something you might like to consider if your actual data has more than one country listed
We've just about got everything now, except for the machines your company owns:
Create Table AssetRegister
(
UniqueId int identity(1,1),
SNo int,
MachineId varchar(4),
MachId int,
SerialNo varchar(255),
Location int
)
insert into AssetRegister values
(01, 'AC01' ,1, 'CAI 136509' ,1),
(02, 'AC02' ,1, 'CAI 136510' ,1),
(03, 'AC03' ,2, 'H1029PKZ' ,1),
(04, 'AC04' ,3, 'CK 8225U08163' ,2),
(05, 'BM05' ,4, NULL ,2),
(06, 'CC01' ,5, 'SCF 165/04' ,1),
(06, 'CC01' ,6, 'F 1013/04' ,NULL),
(07, 'CC02' ,7, NULL ,2),
(07, 'CC02' ,8, NULL ,NULL),
(08, 'CM02' ,9, 'AB/167284' ,1),
(09, 'CM04' ,10, '589' ,1),
(10, 'CM06' ,11, NULL ,1),
(11, 'CM07' ,12, 'AB/153028' ,1),
(12, 'CM08' ,13, '6080' ,1),
(13, 'CM09' ,14, NULL ,1),
(14, 'CM10' ,15, 'AB/167282' ,1),
(15, 'CM11' ,16, 'AB.42150' ,1),
(16, 'CM12' ,17, '35109102' ,1),
(17, 'CM13' ,18, '91002' ,3),
(18, 'CM14' ,19, NULL ,1) Notice that I've recorded S.No and Machine Id #but I've also assigned each machine it's own unique identifier - this is what I would put the primary key on.
I then ran a query to make sure I could reconstruct your original data:
SELECT AR.SNo, AR.MachineId,
M.MachineName + ISNULL(' (' + S.Supplier + ')',''),
M.ModelNo, ISNULL(AR.SerialNo, 'Not Available'),
ISNULL(M.MadeIn,'Unknown'), ISNULL(L.LocName,'Not Available')
FROM AssetRegister AR
INNER JOIN Machine M ON AR.MachId = M.MachId
LEFT OUTER JOIN Supplier S ON M.Supplier = S.SupplierId
LEFT OUTER JOIN Location L ON AR.Location = L.LocId
Some more words on the "how" I did all this.
I actually often use Excel with things like this. I can write macros, formulas or just use Edit,Replace to manipulate the data into the table structure - I can then generate the SQL in the spreadsheet or use a bulk import.
Other methods I've used are to load all of the data into a working table, which I then examine and manipulate. E.g to populate the Location table
Insert into Location select distinct Location from work And then update your working table
UPDATE W SET Location=LocId
FROM Work W
INNER JOIN Location L on W.Location=L.LocName
UPDATE Work SET Location=NULL WHERE Location='Not Available' Keep these in a script so that you can re-run it and build the script up in stages.
Once you've got all that in place then consider setting up the foreign keys properly, adding any indexes, etc.
Might not be the ideal solution but should get you started.
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
However, if I'd posted it as an article I would be the first one to "report" it as wrong type ... should be a tip 
