SELECT INTO information

[orreymalcolm]

Hey Guys,

I'm writing a quick query to combine the results of 2 tables. One has contract information (with contract ID) another is Member information (with Member ID). I have a 3rd table which was passed down to me by the person I replaced. This 3rd table is an "All Members and All contracts" table which has Member ID, Contract ID and whether they are committed to it or not (A or B).

I'm Usuing SQL Server 2000

My code is as follows:

Use <myDatabase>
	DECLARE @MemberNum int
	SET @MemberNum = 0
	WHILE (@MemberNum < 10000)
		BEGIN
			IF EXISTS (SELECT ff_memb_no FROM gsd_hosf WHERE ff_memb_no = @MemberNum)
				PRINT 'Inserting New Contracts for Member '+cast(@MemberNum as nchar(4))
				SELECT fr_agre_no FROM gsd_comr WHERE fr_agre_no NOT IN (SELECT fm_agre_no FROM gsd_hocm WHERE fm_memb_no = @MemberNum)
			SET @MemberNum = @MemberNum + 1
		END
PRINT 'DONE!'

So basically this just goes through the Member list 1 by 1 and for each member lists the contracts that are not already inside the "all Members All contracts" table.

What I'm going to do is use a SELECT INTO statement to combine those results with the "all Members All contracts" table so that the "all Members All contracts" table has every contract listed with every member.

My question is this: Is there a way, while inserting the new rows in the "all Members All contracts" table, to add values to just Member ID and Committed? but still insert the Contract ID as it goes through the loop?

I hope this makes sense.

Thanks!

p.s. There are 2011 contracts available, and 657 members (member ID go up to 9999) so in the end i should have a table with 1,321,227 rows.

 

[orreymalcolm]

Sorry, I forgot...

gsd_hocm = "All Members and All Contracts" Table
gsd_comr = All Contracts table
gsd_hosf = All Members table

*_agre_no = Contract ID
*_memb_no = Member ID

 

[SQLSister]

Never, and I do mean never, consider using a loop for this type of work. It is slow and a very bad programming practice. Forget how to use loops. Learn to use set-based solutions instead.

Use joins instead. How do you know if the member is committed to a contract?

TO get the list of all possible memebers and contracts use a cross join. To compare to the items in the existing allmembersallcontractstable, use a left join to the allmembersallcontracts from this information where the memberid inthe allmembersallcontracts table is null

something like:

insert into allmembersallcontracts (memberid, contractid)
select a.memberid, a.contractid from 
(select memberid, contractid from contracts c cross join memebers m) a 
left join
allmembersallcontracts amac
on a.memeberid = amac.memberid
where amac.memeberid is null

Untested code. Run the select first of course to make sure you are getting what you want.

Questions about posting. See faq183-874