July 4, 2007 at 5:40 am
I need help!!!!!
I have the following tables:
Import
Dest
Activity
Project
Table Import has some project and activity data that needs to be imported in to table Dest. Table Import has Activity names and Project names (2 different cells). 1 Activity can have more than 1 project. So there are a lot of duplicate activities, but with their own Projects. But table Dest has just Activity Id’s. This table is linked to the ACTIVITY (has Activity Name, Activity Id, Project Id) and PROJECT (Project name, Project Id) tables and the way they’re setup, every Activity has its own project. So there’s no need for projectid to be in the DEST.
So I need to get these Activity names and Project names into Activity Id’s. Please Help!!!
July 5, 2007 at 5:06 am
July 5, 2007 at 7:46 am
Bledu, Thanks for your reply. With the below Stored Procedure, I'm hoping to update the table TimeEntryTemp with the values from the table Dataimport. But some of the DataImport values (userid and activityid) needs to be mapped from other tables first. DataImport originally has usernames and activitynames. So i have to get userid's and activity id's from tblUSer and tblActivity. So i make these values to be @ values, When I run this procedure, nothing happens to the TimeEntryTemp table. I get no error, but my table doesn't get populated.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.UpdateTime
(
@Activityid nvarchar(50),
@Duration nvarchar(50),
@description nvarchar(50),
@Date datetime,
@Starttime datetime,
@EndTime datetime,
@PONumber nvarchar(50)
)
AS
DECLARE @userid UNIQUEIDENTIFIER
select @userid = u.userid from DataImport d
inner join tblUser u
on d.username COLLATE Latin1_General_CI_AS = u.firstname + ' ' + u.surname
select @activityid = a.activityid from tblactivity a inner join dataimport d
on a.description COLLATE Latin1_General_CI_AS = d.activity
inner join tblproject p
on p.projectid = a.projectid
and p.description COLLATE Latin1_General_CI_AS = d.project
select @duration = duration, @description = note, @date = note, @starttime = start,
@endtime = finish, @PONumber = '1' from dataimport d
INSERT INTO TimeEntryTemp (Userid, Activityid, description, PONumber, TimeEntryDate,
TimeEntryStarttime, TimeEntryEndTime, Duration)
Values (@userid, @activityid, @description, '1', @Date,
@Starttime, @EndTime, @Duration)
go
July 5, 2007 at 8:13 am
July 5, 2007 at 8:24 am
Hahah ok I'll go easy on the colors. I used the UNIQUEIDENTIFIER because userid is a number, and it doesn't seem to take @user INT ,,, u see..
Oh ok I think I could have used NVARCHAR.. Please do have a look!!
July 5, 2007 at 8:28 am
its advisable to use int, bigint
try this code, i just shifted your code around. i could not figure out the function of the @date variable.
in your code you are trying to assign multiple values of the resultant set to a single value variable e.g.
select
@userid = u.userid from DataImport d inner join tblUser u on d.username = u.firstname + ' ' + u.surname
if you want a single user id, you should specify in your where clause
------- try this
INSERT
INTO TimeEntryTemp (Userid, Activityid, description, PONumber, TimeEntryDate,
TimeEntryStarttime, TimeEntryEndTime, Duration)
SELECT
u.userid,a.activityid,u.note,'1' as [PONumber],@Date ,u.start,u.finish,u.duration
FROM
DataImport d inner join tblUser u
on d.username = u.firstname + ' ' + u.surname
inner join tblactivity a
on a.description = d.activity
inner join tblproject p
on p.projectid = a.projectid
and p.description = d.project
-------
July 5, 2007 at 8:32 am
July 6, 2007 at 2:36 am
WORKED LIKE A CHARM!!!!!!
THANK YOU
(Please excuse the colour)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply