March 12, 2007 at 8:02 am
Please help
I have 2 tables. TblItem and tblFacultyListing. The faculty meber can be either be fulltime (code688) or part-time (code 689). If the the status change (from ft to part time) I need to update tblFacultyListing (intCategoryId=689) and also get the ItemId from that update and update the tblItem with new intCategoryId=689 (intItemId)
Web programmer
March 12, 2007 at 8:50 am
We need more information on this one.
Can you provide the tables definition, some sample data and the required output. Also explaining wether you need to do this from a proc or a trigger will guide us in the right direction right away.
March 12, 2007 at 9:56 am
Here is the table tblItem defenition:3
intIDint40
intUIDuniqueidentifier160
intWebIDint40
intDataTypeint41
intNavIDint41
intSubIDint41
intTabIDint41
blnFeaturedbit10
blnDeletedbit10
blnApprovedbit10
intApproveUserIDint41
dtExpiredatetime81
intOrderint41
intWebBackupIDint41
blnStagingNewbit10
blnStagingChangebit10
intCategoryIDint41
blnNeedsTranslationbit10
intLastLevelint40
and tblFacultyListing Definition:
3intIDint40
0intUIDuniqueidentifier160
0intFacIDint41
0txtTitlenvarchar5001
0intCategoryIDint41
0txtAbstractntext161
0blnNotificationbit11
0blnPrivatebit11
0txtDescriptionntext161
0intItemIDint41
0intResourceIDint41
0blnLivebit11
0dtAddeddatetime81
0dtStartdatetime81
0dtEnddatetime81
0txtMetaKeywordsntext161
0txtMetaDescriptionntext161
0intMetaIDint41
0intImageIDint41
The sample data is for tblFacultyListing
8732{2501F879-FD04-45F7-8BCE-E46BE9118C0C}486Agresti, William6880102310
8733{DF9DD2A5-01C5-4AA1-9486-B09562B0A3F7}406Anikeeff, Michael6880102321
8734{05254D4E-5019-4F61-A47D-AACBD23BF730}1115Baker Sr., John6880102331
8735{5BD132D0-856D-485C-B50E-1D9DFB04AAD3}1901Brunetti, Celso6880102341
8736{CAE6ACDB-B1D6-4B81-8D95-CF469CBD8B8F}1158Calvin, James6880102351
8737{726A46E7-22A9-447F-8AE3-58AAFE1BF232}1327Cheong, Kwang Soo6880102361
8738{012C1F40-4287-44B3-BB29-5306A26E13A4}1155Crain, Thomas6880Thomas Crain, AM, holds the position of Director, Interdisciplinary Studies with the Division of Undergraduate Studies, Johns Hopkins University, and has been teaching with Johns Hopkins University since 2004. 102371
8739{9725BDD3-AA21-4FC8-8A5E-8993B8339EE1}113DeSimone, Joan6880102381
8740{41363341-0744-41C2-9FF9-F52D52B1F842}1518Djavanshir, Reza6880102391
8741{BBF02E82-9D8F-439B-8A21-09E88C1B8AF1}1313Dreisbach, Christopher6880102401
8742{6D3731E5-5F04-4F01-801F-C811ECA1102E}1275Hough, Douglas6880102411
8743{B2A0AD26-E567-4821-8F7B-7B91C25D1F11}1519Liebowitz, Jay6880102421
8744{B71C02BA-06B7-44CF-8A05-60051A3E9227}1561McMillan, Michael6880102431
8745{EEFD127A-4F80-4D27-8112-A7640990483A}1951Megbolugbe, Isaac6880102441
SAmple for tblItem is:
1{652D6EEF-3839-454D-BA9A-E9DA29323168}255250000120113100
2{CAE0B9AF-C6D0-4DDB-BEEB-E937F0165B60}255250000120113200
3{3E312B6D-F3EA-45A7-96D1-859F0B8520EA}255250000120113300
4{53618722-D963-4B4D-B015-CB41E989868F}2552500001201168800
5{E4369D7F-B24D-4240-8216-71D2C85DAFC6}35-1250011100
6{0D910C4E-AA85-40FB-82A9-B22DF9376070}25225000110113200
7{A61A14FE-33B0-43F9-9638-A565B7755912}14-12414-1001201168800
8{74607F02-F35E-4CB7-90C1-72E34AA14D1A}41-12441-1001201100
9{882DC593-F4CF-44CF-B100-32345BB36519}42-12442-1001201100
What
Web programmer
March 12, 2007 at 10:54 am
So what do we need to do with this?
March 12, 2007 at 11:15 am
OK. I need to perform update. If the status change (from Full time to part time ) I need to update intCategoryId in both tables to 689
Web programmer
March 12, 2007 at 11:25 am
From an existing proc?
From a trigger?
Please provide the required output from the sample data you provided (I'm not trying to annoy you, I just want to get it right the first time)
March 12, 2007 at 12:05 pm
By using DTS package
Web programmer
March 12, 2007 at 1:10 pm
Why a DTS? Do you have data that you need to access on a different database system?
March 12, 2007 at 1:25 pm
Yes I do have data that I need to access on the different database. That where I am getting the faculty status
Web programmer
March 12, 2007 at 1:53 pm
A different sql server database can be accessed like this :
use master
go
Select * from msdb.dbo.sysObjects.
Would this solve the current problem of database access?
Sorry for asking so many questions but I still miss I don't know everything and that we're going all wrong on this one.
March 13, 2007 at 1:59 pm
-- ***** Note per our MSN IM conversation, I'm posting a solution here.
-- Note the only thing you may need to change,
-- considering the table names are valid, is
-- [Server1] --> LinkedServerName the source.
-- [Database1] --> Source Database
Declare @PartTimeInstructor TABLE
(
InstrIDNum INT PRIMARY KEY
)
-- The following inserts Active Part-Time instructor
-- into a Table Variable (temporary storage in memory)
-- When you read directly here, you avoid cross-server/cross-database
-- do not join tables from different databases/servers, bad practice
INSERT INTO @PartTimeInstructor
SELECT
InstrIDNum
-- NOTE YOU MAY NOT EVEN NEED THESE OTHER THREE COLUMNS
-- , LastName + ', ' + FirstName AS txtTitle
-- , Busstatus
-- , FullTimePartTimeFlag
FROM [Server1].[Database1].dbo.tblInstructor
WHERE
BusStatus='active'
AND
FullTimePartTimeFlag = 'PT'
-- NOTE -- Make Sure that BusStatus, FullTimePartTimeFlag
-- is indexed if there is alot of Instructors...
-- This will speed up the query
-- The Following Updates dbo.tblFacultyListing
-- Sets, intCateforyID = 689, in dbo.tblFacultyListing
-- when this listing has a Part Time instructor.
Update f
SET
f.intCategoryID=689
FROM @PartTimeInstructor p
Inner Join dbo.tblFacultyListing f
ON p.InstrIDNum = f.intFacID
-- The Following Updates dbo.tblItem
-- Sets, intCateforyID = 689, in dbo.tblItem
-- when this item is part of a faculty listing
-- and this listing has a Part Time instructor.
Update i
SET
i.intCategoryID=689
FROM @PartTimeInstructor p
Inner Join dbo.tblFacultyListing f
ON p.InstrIDNum = f.intFacID
Inner Join dbo.tblItem i
ON f.intItemID = i.intID
MCTS
March 13, 2007 at 2:03 pm
Erik,
Ideally, this T/SQL should be fast depending on the size of dbo.tblInstructor, whether or not the appropriate indexes are on the tables. If you shall find that it is slow, please look at the indexes for each tables, including columns that are foreign keys and columns in the WHERE clause. Just because a column has a foreign key, does not ensure that there is an index on this column, or that SQL server will use this foreign key in its execution plan.
Regards,
MCTS
March 14, 2007 at 6:48 am
Thanks it works
Web programmer
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply