January 21, 2011 at 5:40 am
I have a table that collects data from machines and it's laid out like so (11 columns):
ID#, OrderNumber, PartNumber, Date, MachineNumber, LargeBearingJournalRunout, Small BearingJournalRunout, BoreSize, BoreOvality, BorePerpBackface, RadialFlatnessBackface
sample data:
112345678944603604731/12/2011C935160 0.00360.00670.010.0570.1912.5863
All columns but ID# need to go into a table in the following format:
ID#,sUnitNumber,sUnitType,sLocation,dTimeStamp,sOperationType,sValue
data should look like:
1123456789_44603604734460360473C935160 1/12/2011LargeBearingJournalRunout 0.0036
2123456789_44603604734460360473C935160 1/12/2011SmallBearingJournalRunout 0.0067
3123456789_44603604734460360473C935160 1/12/2011BoreSize 0.01
4123456789_44603604734460360473C935160 1/12/2011BoreOvality 0.057
5123456789_44603604734460360473C935160 1/12/2011BorePerpBackface 0.19
6123456789_44603604734460360473C935160 1/12/2011RadialFlatnessBackface 12.5863
sUnitNumber = OrderNumber+PartNumber
sUnitType = PartNumber
sLocation = MachineNumber
dTimeStamp = Date
sOperationType = Column 6 or 7 or 8 or 9 or 10 or 11
Any help would be great! Thanks.
January 21, 2011 at 8:54 am
Can you be more specific on sOperationtype result? You say Column 6 or 7 or 8 or 9 or 10 or 11 but what decides the "OR"? in your sample data those columns have values so how do you want to decide which to display. Other wise this gets you most of the way i think
Something Like
Insert into "Table2" (
ID#,
sUnitNumber,
sUnitType,
sLocation,
dTimeStamp,
sOperationType,
sValue)
select
ID#,
cast(OrderNumber as varchar(50)) + '_' + cast(PartNumber as varchar(50)),
PartNumber as 'PartNumber',
MachineNumber as 'sLocation'
dtimeStamp,
--need this defined
sOperationType??,
sValue
from "Table1"
January 21, 2011 at 10:35 am
What you put is what I started with but I need to put all the columns so it wouldn't be an OR.
This is where I am at:
--//////////////////////////////////////////////////////////
declare @UnitNumber varchar(40)
declare @UnitType varchar(20)
declare @Location varchar(25)
declare @OperationType1 varchar(25)
declare @OperationType2 varchar(25)
declare @OperationType3 varchar(8)
declare @OperationType4 varchar(11)
declare @OperationType5 varchar(16)
declare @OperationType6 varchar(22)
declare @Value1 varchar(10)
declare @Value2 varchar(10)
declare @Value3 varchar(10)
declare @Value4 varchar(10)
declare @Value5 varchar(10)
declare @Value6 varchar(10)
set @OperationType1 = 'OPGM.P.OP50.LargeBearingJournalRunout'
set @OperationType2 = 'OPGM.P.OP50.SmallBearingJournalRunout'
set @OperationType3 = 'OPGM.P.OP50.BoreSize'
set @OperationType4 = 'OPGM.P.OP50.BoreOvality'
set @OperationType5 = 'OPGM.P.OP50.BorePerpBackface'
set @OperationType6 = 'OPGM.P.OP50.RadialFlatnessBackface'
select @UnitNumber = OrderNumber + '_' + PartNumber,
@UnitType = PartNumber,
@Location = 'GM.' + MachineNumber ,
@Value1 = LargeBearingJournalRunout,
@Value2 = SmallBearingJournalRunout,
@Value3 = BoreSize,
@Value4 = BoreOvality,
@Value5 = BorePerpBackface,
@Value6 = RadialFlatnessBackface
from OP50_AGDavis
insert into gearmachining
([sMeasurementType]
,[sUnitNumber]
,[sUnitType]
,[sLocation]
,[sOperationType]
,[dTimestamp]
,[bEvalOK]
,[sValue]
,[sDimension])
values ('REAL',@UnitNumber,@UnitType,@Location,@OperationType1,'2011-01-18',1,@Value1,'m')
--//////////////////////////////////////////////////////////
Then I thought, would I have to have 6 insert statements to cover the 6 values? Seemed like a lot so I wondered if there was a different or easier way?
January 21, 2011 at 10:47 am
Forgive me if I'm not understanding its a dark rainy Friday and I'm fighting the urge to sleep 😉
It sounds like you want to take each value for the different Journals and insert a row for that? So pivot your table and columns 6-11 into rows?
January 21, 2011 at 10:52 am
Yes, yes! Good job, essentially pivot the table (guess I couldn't think of that wording).
January 21, 2011 at 11:09 am
Try this Code. You'll need to add in the last few columns to pivot but if you understand the code it will take you just a few seconds to finish.
declare @table1 table (
ID int,
OrderNumber bigint,
PartNumber bigint,
Date datetime,
MachineNumber varchar(50),
LargeBearingJournalRunout numeric(18,4),
[Small BearingJournalRunout] numeric(18,4),
BoreSize numeric(18,4),
BoreOvality numeric(18,4),
BorePerpBackface numeric(18,4),
RadialFlatnessBackface numeric(18,4))
insert into @table1
values
(1 ,
123456789,
4460360473 ,
1/12/2011,
'C935160',
0.0036 ,
0.0067,
0.01 ,
0.057,
0.19 ,
12.5863)
select * from @table1
SELECT row_number() over(partition by ID order by ID) as ID,
cast(OrderNumber as varchar(50)) + '_' + cast(PartNumber as varchar(50)),
orig.PartNumber, MachineNumber as 'sLocation',
orig.Date,
unpvt.ColumnName, unpvt.Data
FROM @table1 orig
CROSS APPLY
(
SELECT 'LargeBearingJournalRunout', LargeBearingJournalRunout UNION ALL
SELECT 'Small BearingJournalRunout', [Small BearingJournalRunout]
) unpvt (ColumnName,Data)
You'll see i put your sample data into a table variable and select from it. then do the pivot to show you how each column becomes a row. Wrap that as in insert and you are good to go i think. Also note I adopted this from thread
http://qa.sqlservercentral.com/Forums/Topic998785-392-1.aspx. You can follow that thread to see people smarter than me who came up with this.
Edit: also I never asked what version of SQL you were running. This code was tested in SQL2k5
January 26, 2011 at 8:01 am
I am running 2005.
This worked great to pivot the data, I am losted how to do the insert.
This is what I have so far:
select
orig.idOP50AGD,
MeasurementType = 'REAL',
cast(orig.OrderNumber as varchar(9)) + '_' + cast(orig.PartNumber as varchar(10)) as UnitNumber,
orig.PartNumber as UnitType,
orig.MachineNumber as Location,
unpvt.OperationType,
orig.Date as aTimeStamp,
unpvt.aValue
from OP50_AGDavis orig
cross apply
(
SELECT 'OPGM.P.OP50.LargeBearingJournalRunout', LargeBearingJournalRunout UNION ALL
SELECT 'OPGM.P.OP50.SmallBearingJournalRunout', SmallBearingJournalRunout UNION ALL
SELECT 'OPGM.P.OP50.BoreSize', BoreSize
) unpvt (OperationType,aValue)
This gives me the data and the format I need it in but I don't know how wrap it all into an insert code.
insert into gearmachining
([sMeasurementType]
,[sUnitNumber]
,[sUnitType]
,[sLocation]
,[sOperationType]
,[dTimestamp]
,[sValue])
values
([MeasurementType]
,[UnitNumber]
,[UnitType]
,[Location]
,[OperationType]
,[aTimestamp]
,[aValue])
I get an error:
The name "MeasurementType" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Please help. Thank you.
January 26, 2011 at 8:19 am
I'm not sure why you are using the Insert into ...Values .... You are not inserting specific values, but instead want to pass in the results from your select statement to the Insert. Below is an example using my queries from above. I have created another table variable "gearmachining" which should be your destination table and I do an insert into followed by the select statement that will put the data in.
declare @table1 table (
ID int,
OrderNumber bigint,
PartNumber bigint,
Date datetime,
MachineNumber varchar(50),
LargeBearingJournalRunout numeric(18,4),
[Small BearingJournalRunout] numeric(18,4),
BoreSize numeric(18,4),
BoreOvality numeric(18,4),
BorePerpBackface numeric(18,4),
RadialFlatnessBackface numeric(18,4))
insert into @table1
values
(1 ,
123456789,
4460360473 ,
1/12/2011,
'C935160',
0.0036 ,
0.0067,
0.01 ,
0.057,
0.19 ,
12.5863)
--select * from @table1
declare @gearmachining table (
sMeasurementType varchar(50),
sUnitNumber varchar(50),
sUnitType varchar(50),
sLocation varchar(50),
sOperationType varchar(50),
dTimestamp datetime,
svalue numeric(18,4))
insert into @gearmachining
([sMeasurementType]
,[sUnitNumber]
,[sUnitType]
,[sLocation]
,[sOperationType]
,[dTimestamp]
,[sValue])
SELECT --row_number() over(partition by ID order by ID) as ID,
'Real' as MeasurementType,
cast(OrderNumber as varchar(50)) + '_' + cast(PartNumber as varchar(50)) as 'UnitNumber',
orig.PartNumber as UnitType,
MachineNumber as 'sLocation',
unpvt.sOperationType,
orig.Date,
unpvt.Data
FROM @table1 orig
CROSS APPLY
(
SELECT 'LargeBearingJournalRunout', LargeBearingJournalRunout UNION ALL
SELECT 'Small BearingJournalRunout', [Small BearingJournalRunout] UNION ALL
SELECT 'BoreSize', BoreSize
) unpvt (sOperationType,Data)
select * from @gearmachining
Link to Microsoft insert statement http://msdn.microsoft.com/en-us/library/ms174335(v=SQL.90).aspx
January 26, 2011 at 9:26 am
YOU ARE AWESOME! I have only used the Insert function simply that's why I thought you had to assign values.
I changed it so it inserts into my existing table and works great!
Thanks again for all your help and links.
January 26, 2011 at 11:53 am
I'm glad I was able to help and hope you learned from the experience. Best of luck to you.
January 26, 2011 at 12:25 pm
Yes I did learn.
But I have another question now. The gearmachining table I am inserting into has an index on it to create a unique key with
UnitNumber, OperationType, Location, TimeSTamp. So I run the query for the first time and it adds to table gearmachining,
then I add more rows to the original table, run the query again and get the following error:
"Cannot insert duplicate key row in object 'dbo.gearmachining' with unique index 'UK_GearMaching'."
The new data when these 4 fields are combined (UnitNumber, OperationType, Location, TimeSTamp) are unique from the
previous data.
So then it terminates. What do I do now?
Do I have to determine if the data already exits in gearmachining? I thought that was the point of the indexes.
Thanks again.
January 26, 2011 at 2:59 pm
Correct if you are going to run the insert statement multiple times you need to add a where clause filter to only add in new records. There are many ways to do this. If you run the import once a day then you could put a filter on the select to say where sdate is eaqual to todays date for example. Run this every day and you only get new records. If you have more details on how what you are trying to do I can try to offer a few ways to get there.
Your other question about indexes the Purpose of the index is really to improve query performance by have a way to lookup data rows rather than looking at the entire table to find a value. Having indexes can also enforce uniqueness in your data which you have run into. I've been working with SQL server for almost a decade in different roles, but Indexes is still something I learn something new about pretty often. Check out this series of articles and see if it helps.
http://qa.sqlservercentral.com/articles/Indexing/68439/
This man knows what he is talking about 😀
February 3, 2011 at 11:11 am
So this works great! But now I need to add the constraint, so I started with a simple table
insert into gearmachining
(sMeasurementType,
sUnitNumber,
sUnitType,
sLocation,
sOperationType,
dTimestamp,
sValue,
table_id)
select
MeasurementType = 'REAL',
cast(orig.OrderNumber as varchar(9)) + '_' + cast(orig.PartNumber as varchar(10)) as UnitNumber,
orig.PartNumber as UnitType,
'GM.' + orig.MachineNumber as Location,
unpvt.OperationType,
orig.Date as aTimeStamp,
unpvt.aValue,
idOP11AGD
from OP11_AGDavis orig
cross apply
(
SELECT 'OPGM.' + orig.Abb + '.OP11.Point1', Point1
) unpvt (OperationType,aValue)
I tried:
WHERE table_id <> orig.idOP11AGD - error
Really drawing a blank on this and it should be simple right?
Basically the unique key in table OP11_AGDavis is idOP11AGD
so if that plus the partnumber and ordernumber are not in the
gearmachining table then insert? Do I wrap the above insert
code into a where statement?
Thanks.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply