August 27, 2003 at 5:20 am
i want to insert dimension id's from my SQL DB into facttables of the same SQL DB..for this purpose i am first using a transform data task to transfer data from an oracle DB to the SQL DB..then i am using Execute SQL to
alter table and add a column..
after this i am using DDQ task to insert values from a dimension table of the SQL DB to the fact table of the same SQL DB.
it all works well but when i insert values into the table by the statement
insert into tablename (company_code) values(select company_code from company)..
this doesnt seem to work..
can anyopne plz plz guide me about this..
or do u think there is another better alternative for adding dimension id's ..if so can i have an example as to how tht is possible?
i would be grateful..
August 27, 2003 at 9:43 am
I dont know if I'm taking this to litteraly but your insert statemtnt in not corect.
You should have it as
INSERT INTO TABLENAME (COL,...)
SELECT....FROM...WHERE...
Also a suggestion if you can stay away from DDQ, and change your code to SQL go on that route. DDQ are usually only for SLOWLY CHANGING DIMENSIONS because DDQ are really slow.
August 27, 2003 at 2:42 pm
hi,
i am using Execute SQL task to alter table...
if i use
alter table test add company_name varchar(8) ( select company_name from junk)
it does not give any problem but inserts a column with the name company_name with all null values...it does not insert company names from junk
this is one way i tried to add diemsion id's into fact tables..
the other method i am using is that
i use a Execute SQL task to first add a column then i use another Execute SQL task to insert values from the dimension table..
the insert statement i am using is..
insert into test(member_name) select member_name from members
or
insert into test(member_name) select member_name from members where member_code=2 but in both cases it says ..
column does not allow nulls insert fails
altough the source col does not hve null values..
what should i do about this..
besides this is it possible tht in one Execute SQL task..i can
apply alter table to add a column and in the same time insert values into that column..if so how is this possible..
August 27, 2003 at 10:47 pm
could anyone plz guide me i have recieved no replies to my post lately.i would be very grateful..
August 28, 2003 at 7:06 am
Hi,
Try running these two queries. I bet that you have at least one null value in your members table.
select * from members where member_name is null
select member_name from members where member_code=2 and member_name is null
You could also try adding the isnull function to test the insert.
insert into test(member_name) select isnull(member_name,'NA') from members where member_name is not null
or
insert into test(member_name)
select isnull(member_name,'NA') from members where member_code=2
and member_name is not null
Hope that helps.
August 28, 2003 at 7:10 am
The only thing I can think of is in your members table, the member name column has a NULL and in your new table member name column is set to not allowing nulls.
I would first try on your members table
select * from members where member_name is null
August 28, 2003 at 7:25 am
well actually i have no nulls in the member_name col as the member_name col is defined /resticted by not null condition..
August 28, 2003 at 9:40 am
Check if your target table allows NULL's. If not let it allow NULL's and check the data when it is inserted into the table.
August 28, 2003 at 9:43 am
Is your ID column an IDENTITY column?
August 28, 2003 at 5:49 pm
sairah,
It looks to me like you're having syntax issues. 🙂
To Alter a table:
alter table test add company_name varchar(8)
GO
To Insert to Table:
insert tablename (company_code)
select company_code from junk
1) Use the "GO" keyword after the table modification.
2) In the insert don't use "Values" or parenthesis
Signature is NULL
August 29, 2003 at 7:46 am
The SQL statement to do this is actually long and tedious, but here is an example I use to do what you want to do:
SELECT intDateID, ISNULL(idsOfficeID, 0) "OfficeID", ISNULL(idsProjectID, 0) "ProjectID",
ISNULL(idsTechnicianID, 0) "TechnicianID", ISNULL(idsLocationID, 0) "LocationID",
ISNULL(idsWorkTypeID, 0) "WorkTypeID", ISNULL(idsCancelTypeID, 0) "CancelTypeID",
ISNULL(idsRSStoreID, 0) "RSStoreID", 0 "SalesEngineerID", wom.strWorkOrderNo,
wom.bytCompleted, wom.bytCompletedOnTime, wom.bytCancelled, wom.intTurnAround,
wom.intTimeOnSite, wom.bytApptMade, wom.bytApptOnTime, wom.bytQCd, wom.bytFieldQCd,
wom.bytQCPass, wom.bytFieldQCPass, wom.curSales, wom.curLaborSales, wom.curMaterialSales,
wom.curTechPay, wom.curDiscount
FROM CCCtblWorkOrderMain wom
JOIN dwCorp.dbo.tblCompleteDate cd
ON wom.dtmCompleted=cd.dtmCalendar
LEFT JOIN dwCorp.dbo.tblOffice o
ON wom.strOffice = o.strOfficeCode
LEFT JOIN dwCorp.dbo.tblCustomer c
ON wom.strProjNo=c.strProjectCode
LEFT JOIN dwCorp.dbo.tblTechnician t
ON wom.strLeadTech=t.strTechCode
LEFT JOIN dwCorp.dbo.tblState s
ON wom.strEUState=s.strState
AND wom.strEUZip=s.strZipCode
LEFT JOIN dwCorp.dbo.tblBusiness b
ON wom.strWorkType=b.strWorkTypeCode
LEFT JOIN dwCorp.dbo.tblRadioShackStore rss
ON wom.strRetailerNo=rss.strStoreNo
LEFT JOIN dwCorp.dbo.tblCancelType ct
ON wom.strConvCancelType=ct.strDetailCancelCode
A 0 entry is used to account for a code that might not be the same. A code field is unique in the dimension table. It forms the basis for the join. The join to the fact table is based on the id field, however.
I sometimes use a -1 for the unknown bucket when the 0 can be useful.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply