Cross Referencing Insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Cross Referencing Insert

Author

Topic

belfastchild
Starting Member

11 Posts

Posted - 2006-11-01 : 05:55:18

I have a table dbo.AdminCheckList.

AdminChecklistId AdminCheckListName
1 ApplCheckPAStatedInHeading
2 ApplCheckTypeOfPermission
3 ApplCheckNameOfApplicant
4 ApplCheckAddressCONotAcceptable
5 ApplCheckApplicantsTelNo
6 ApplCheckNameOfAqent
7 ApplCheckAddressOfAqent
8 ApplCheckTelNoOfAqent
9 ApplCheckIfRegCoDirectorsName
10 ApplCheckAddressOfCompany
11 ApplCheckRegNoOfCompany
12 ApplCheckCorrespondenceAddress
13 ApplCheckApplicationSignedAndDated
14 ApplCheckNatureAndExtent
15 ApplCheckIfToRetainProposedUse
16 ApplCheckIfToRetainPeriod
17 ApplCheckIfToContinuePresentUse
18 ApplCheckIfToContinueProposedUse
19 ApplCheckIfChangeofUse
20 ApplCheckPreviousPresentUse

In my application I’m doing a for loop on an array of AdminCheckListNames and ApplicationId.

I need to be able to write a stored procedure to cross reference the names I’m looping through with the AdminCheckListNames in the above table (dbo.AdminCheckList).

Once the AdminCheckListName table is cross referenced I want to insert into the table below the values something like this.

dbo.InvalidAdminCheckList table
InvalidAdminCheckListId ApplicationId AdminCheckListId
1 3 20
2 3 16
3 2 10
etc

Any ideas?

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-11-01 : 06:03:45

I think you should thank Peso for his help before you start posting more questions !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-01 : 06:10:34

Where is the fun of that?

Peter Larsson
Helsingborg, Sweden

belfastchild
Starting Member

11 Posts

Posted - 2006-11-01 : 06:12:05

Had posted this without seeing his reply.
Apologies if anyone was offended.

Working 15 hour days the past few weeks so not all with it at the moment

I have since replied to him thanking him.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-01 : 06:25:55

SELECT a.ID ApplicationID, acl.AdminCheckListID
FROM dbo.Applications a
CROSS JOIN dbo.AdminCheckList acl

Peter Larsson
Helsingborg, Sweden

belfastchild
Starting Member

11 Posts

Posted - 2006-11-01 : 06:29:06

Thanks for your help Peso.
Much appreciated.

belfastchild
Starting Member

11 Posts

Posted - 2006-11-01 : 13:09:35

ended up doing something similar.
thought this was perhaps more readable (for me anyway)...

create procedure sp_InsertAdminCheckList
@filenumber varchar(8),
@checklistname varchar(50)

AS

DECLARE @applicationId varchar(8)
DECLARE @adminchecklistid tinyint

SET @applicationId = ( SELECT dbo.Application.ApplicationId
FROM         dbo.Application CROSS JOIN
             dbo.AdminChecklist
WHERE     (dbo.AdminChecklist.AdminChecklistName = @checklistname) AND (dbo.Application.file_Number = @filenumber) )

SET @adminchecklistid = ( SELECT dbo.AdminChecklist.AdminChecklistId
FROM         dbo.Application CROSS JOIN
             dbo.AdminChecklist
WHERE     (dbo.AdminChecklist.AdminChecklistName = @checklistname) AND (dbo.Application.file_Number = @filenumber) )


INSERT INTO [Planning].[dbo].[InvalidAdminCheckList]
           ([ApplicationId]
           ,[AdminChecklistId])
VALUES
           (@applicationId,
            @adminchecklistid)

GO

works a treat!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-01 : 13:59:53

What about this one?

create procedure sp_InsertAdminCheckList
(
	@filenumber varchar(8),
	@checklistname varchar(50)
)
AS

INSERT		InvalidAdminCheckList
           	(
			ApplicationId,
			AdminChecklistId
		)
SELECT TOP 1	dbo.Application.ApplicationId,
		dbo.AdminChecklist.AdminChecklistId
FROM		dbo.Application
CROSS JOIN	dbo.AdminChecklist
WHERE		dbo.AdminChecklist.AdminChecklistName = @checklistname
		AND dbo.Application.file_Number = @filenumber

Peter Larsson
Helsingborg, Sweden

belfastchild
Starting Member

11 Posts

Posted - 2006-11-01 : 14:46:43

even better

cheers!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources