randomize student/contact info beta

gistfile1.sql

BEGIN TRANSACTION

--declare 
declare @icounter int;




--remove some uniqueness
update student set 
  cFirstName = substring(cFirstName, 1, charindex(' ', cFirstName + ' ')-1), 
	cLastName = substring(cLastName, 1, charindex(' ', cLastName + ' ')-1),
	cLegalMiddleName = substring(cLegalMiddleName, 1, charindex(' ', cLegalMiddleName + ' ')-1),
	cLegalLastName = substring(cLegalLastName, 1, charindex(' ', cLegalLastName + ' ')-1),
	cLegalFirstName = substring(cLegalFirstName, 1, charindex(' ', cLegalFirstName + ' ')-1)


------------------------------------------------------------------------


/* 
delete things that can't be randomized. 
might make this fill/delete with random length/wordiness later
*/

update Student set 
	mMedical = '',
	cPicturePath = '',
	cHealthCareNumber = '',
	mAcademicResult = '',
	mEmail = '',
	mCellPhone = '',
	cLDAPName = '',
	cUserName = '',	--these might (should?) have to be refilled with something
	cPassWord = ''	



------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------



/* 
randomize student table
*/


--clean up overall
if object_id('tempdb..#newresults') is not null
    drop table #newresults;
--if object_id('tempdb..#finalresults') is not null
--    drop table #finalresults; 
--if object_id('tempdb..#temp') is not null
--    drop table #temp;



--temp result sets
create table #newresults(
	rowid int identity,
	iStudentID int,
	Student_cLastName varchar(25),
	Student_cFirstName varchar(25),
	Student_cLegalLastName varchar(25),
	Student_cLegalFirstName varchar(25),
	Student_cLegalMiddleName varchar(25),
	Student_cStudentNumber varchar(25),
	Student_cGovernmentNumber varchar(25),
	Student_cBarcodeNumber varchar(25),
	Student_dBirthdate smalldatetime,
);    

--initial fill
insert into #newresults
	select 
		top 100 percent iStudentID,
		'',	--Student_cLastName
		'', --Student_cFirstName
		'', --Student_cLegalLastName
		'', --Student_cLegalFirstName
		'', --Student_cLegalMiddleName
		'', --Student_cStudentNumber
		'', --Student_cGovernmentNumber
		'', --Student_cBarcodeNumber
		''	--Student_dBirthdate
	from student order by newid()
	





	  
--cursor
declare @Student_cLastName varchar(25);
declare @Student_cFirstName varchar(25);
declare @Student_cLegalLastName varchar(25);
declare @Student_cLegalFirstName varchar(25);
declare @Student_cLegalMiddleName varchar(25);
declare @Student_cStudentNumber varchar(25);
declare @Student_cGovernmentNumber varchar(25);
declare @Student_cBarcodeNumber varchar(25);
declare @Student_dBirthdate smalldatetime;




------------------------------------------------------------------
------------------------------------------------------------------
--Student_cLastName



--counter

set @icounter=1;


DECLARE test CURSOR FOR 
  select top 100 percent substring(cLastName,0, 25) from student order by newid()
    
open test
fetch next from test into @Student_cLastName
while @@FETCH_STATUS=0
begin;

	update #newresults SET 
		Student_cLastName = @Student_cLastName
	WHERE rowid = @icounter
	      
  
    SET @icounter = @icounter+1;
 
fetch next from test into @Student_cLastName
end;
close test;
deallocate test;


------------------------------------------------------------------
------------------------------------------------------------------
--Student_cFirstName



--counter
set @icounter=1;


DECLARE test CURSOR FOR 
  select top 100 percent substring(cFirstName,0, 25) from student order by newid()
    
open test
fetch next from test into @Student_cFirstName
while @@FETCH_STATUS=0
begin;

	update #newresults SET 
		Student_cFirstName = @Student_cFirstName
	WHERE rowid = @icounter
	      
  
    SET @icounter = @icounter+1;
 
fetch next from test into @Student_cFirstName
end;

--reset counter
SET @icounter=0;

close test;
deallocate test;




------------------------------------------------------------------
------------------------------------------------------------------
--Student_cLegalLastName



--counter
set @icounter=1;


DECLARE test CURSOR FOR 
  select top 100 percent substring(cLegalLastName,0, 25) from student order by newid()
    
open test
fetch next from test into @Student_cLegalLastName
while @@FETCH_STATUS=0
begin;

	update #newresults SET 
		Student_cLegalLastName = @Student_cLegalLastName
	WHERE rowid = @icounter
	      
  
    SET @icounter = @icounter+1;
 
fetch next from test into @Student_cLegalLastName
end;

--reset counter
SET @icounter=0;

close test;
deallocate test;



------------------------------------------------------------------
------------------------------------------------------------------
--Student_cLegalFirstName



--counter
set @icounter=1;


DECLARE test CURSOR FOR 
  select top 100 percent substring(cLegalFirstName,0, 25) from student order by newid()
    
open test
fetch next from test into @Student_cLegalFirstName
while @@FETCH_STATUS=0
begin;

	update #newresults SET 
		Student_cLegalFirstName = @Student_cLegalFirstName
	WHERE rowid = @icounter
	      
  
    SET @icounter = @icounter+1;
 
fetch next from test into @Student_cLegalFirstName
end;

--reset counter
SET @icounter=0;

close test;
deallocate test;



------------------------------------------------------------------
------------------------------------------------------------------
--Student_cLegalMiddleName



--counter
set @icounter=1;


DECLARE test CURSOR FOR 
  select top 100 percent substring(cLegalMiddleName,0, 25) from student order by newid()
    
open test
fetch next from test into @Student_cLegalMiddleName
while @@FETCH_STATUS=0
begin;

	update #newresults SET 
		Student_cLegalMiddleName = @Student_cLegalMiddleName
	WHERE rowid = @icounter
	      
  
    SET @icounter = @icounter+1;
 
fetch next from test into @Student_cLegalMiddleName
end;

--reset counter
SET @icounter=0;

close test;
deallocate test;



------------------------------------------------------------------
------------------------------------------------------------------
--Student_cStudentNumber



--counter
set @icounter=1;


DECLARE test CURSOR FOR 
  select top 100 percent substring(cStudentNumber,0, 25) from student order by newid()
    
open test
fetch next from test into @Student_cStudentNumber
while @@FETCH_STATUS=0
begin;

	update #newresults SET 
		Student_cStudentNumber = @Student_cStudentNumber
	WHERE rowid = @icounter
	      
  
    SET @icounter = @icounter+1;
 
fetch next from test into @Student_cStudentNumber
end;

--reset counter
SET @icounter=0;

close test;
deallocate test;




------------------------------------------------------------------
------------------------------------------------------------------
--Student_cGovernmentNumber



--counter
set @icounter=1;


DECLARE test CURSOR FOR 
  select top 100 percent substring(cGovernmentNumber,0, 25) from student order by newid()
    
open test
fetch next from test into @Student_cGovernmentNumber
while @@FETCH_STATUS=0
begin;

	update #newresults SET 
		Student_cGovernmentNumber = @Student_cGovernmentNumber
	WHERE rowid = @icounter
	      
  
    SET @icounter = @icounter+1;
 
fetch next from test into @Student_cGovernmentNumber
end;

--reset counter
SET @icounter=0;

close test;
deallocate test;





------------------------------------------------------------------
------------------------------------------------------------------
--Student_cBarcodeNumber



--counter
set @icounter=1;


DECLARE test CURSOR FOR 
  select top 100 percent substring(cBarcodeNumber,0, 25) from student order by newid()
    
open test
fetch next from test into @Student_cBarcodeNumber
while @@FETCH_STATUS=0
begin;

	update #newresults SET 
		Student_cBarcodeNumber = @Student_cBarcodeNumber
	WHERE rowid = @icounter
	      
  
    SET @icounter = @icounter+1;
 
fetch next from test into @Student_cBarcodeNumber
end;

--reset counter
SET @icounter=0;

close test;
deallocate test;



------------------------------------------------------------------
------------------------------------------------------------------
--Student_dBirthdate



--counter
set @icounter=1;


DECLARE test CURSOR FOR 
  select top 100 percent dBirthdate from student order by newid()
    
open test
fetch next from test into @Student_dBirthdate
while @@FETCH_STATUS=0
begin;

	update #newresults SET 
		Student_dBirthdate = @Student_dBirthdate
	WHERE rowid = @icounter
	      
  
    SET @icounter = @icounter+1;
 
fetch next from test into @Student_dBirthdate
end;

--reset counter
SET @icounter=0;

close test;
deallocate test;



------------------------------------------------------------------
------------------------------------------------------------------
------------------------------------------------------------------


update student set
	cLastName = nr.Student_cLastName,
	cFirstName = nr.Student_cFirstName,
	cLegalLastName = nr.Student_cLegalLastName,
	cLegalFirstName = nr.Student_cLegalFirstName,
	cLegalMiddleName = nr.Student_cLegalMiddleName,
	cStudentNumber = nr.Student_cStudentNumber,
	cGovernmentNumber = nr.Student_cGovernmentNumber,
	cBarcodeNumber = nr.Student_cBarcodeNumber,
	dBirthdate = nr.Student_dBirthdate
from #newresults nr
where student.iStudentID = nr.iStudentID


-----------------------------------------------------------------
------------------------------------------------------------------
------------------------------------------------------------------
------------------------------------------------------------------
------------------------------------------------------------------
------------------------------------------------------------------
------------------------------------------------------------------
------------------------------------------------------------------
------------------------------------------------------------------
------------------------------------------------------------------
------------------------------------------------------------------
--CONTACTS
------------------------------------------------------------------
------------------------------------------------------------------


	--remove some uniqueness
	update contact set 
		cFirstName = substring(cFirstName, 1, charindex(' ', cFirstName + ' ')-1), 
		cLastName = substring(cLastName, 1, charindex(' ', cLastName + ' ')-1),
		cBusPhone = substring(cBusPhone, 1, charindex(' ', cBusPhone + ' ')-1) --shoudl maybe generate fake phone numbers
		

	------------------------------------------------------------------------


	/* 
	delete things that can't be randomized. 
	might make this fill/delete with random length/wordiness later
	*/

	update contact set 
		mCellphone = '',
		cUserName = '',	--these might (should?) have to be refilled with something
		cPassWord = ''	



	------------------------------------------------------------------------
	------------------------------------------------------------------------
	------------------------------------------------------------------------



	/* 
	randomize contact table
	*/


	--clean up overall
	if object_id('tempdb..#contactresults') is not null
		drop table #contactresults;
	--if object_id('tempdb..#finalresults') is not null
	--    drop table #finalresults; 
	--if object_id('tempdb..#temp') is not null
	--    drop table #temp;



	--temp result sets
	create table #contactresults(
		rowid int identity,
		iContactID int,
		Contact_cLastName varchar(25),
		Contact_cFirstName varchar(25),
		Contact_cBusPhone varchar (25)
	);    

	--initial fill
	insert into #contactresults
		select 
			top 100 percent iContactID,
			'',	--Student_cLastName
			'', --Student_cFirstName
			''	--Contact_cBusPhone
		from contact order by newid()
		

		  
	--cursor
	declare @Contact_cLastName varchar(25);
	declare @Contact_cFirstName varchar(25);
	declare @Contact_cBusPhone varchar(25);



	------------------------------------------------------------------
	------------------------------------------------------------------
	--Contact_cLastName



	--counter
	set @icounter=1;


	DECLARE test CURSOR FOR 
	  select top 100 percent substring(cLastName,0, 25) from Contact order by newid()
	    
	open test
	fetch next from test into @Contact_cLastName
	while @@FETCH_STATUS=0
	begin;

		update #contactresults SET 
			Contact_cLastName = @Contact_cLastName
		WHERE rowid = @icounter
		      
	  
		SET @icounter = @icounter+1;
	 
	fetch next from test into @Contact_cLastName
	end;
	close test;
	deallocate test;


	------------------------------------------------------------------
	------------------------------------------------------------------
	--Contact_cFirstName



	--counter
	set @icounter=1;


	DECLARE test CURSOR FOR 
	  select top 100 percent substring(cFirstName,0, 25) from Contact order by newid()
	    
	open test
	fetch next from test into @Contact_cFirstName
	while @@FETCH_STATUS=0
	begin;

		update #contactresults SET 
			Contact_cFirstName = @Contact_cFirstName
		WHERE rowid = @icounter
		      
	  
		SET @icounter = @icounter+1;
	 
	fetch next from test into @Contact_cFirstName
	end;
	close test;
	deallocate test;



	------------------------------------------------------------------
	------------------------------------------------------------------
	--Contact_cBusPhone


	--counter
	set @icounter=1;


	DECLARE test CURSOR FOR 
	  select top 100 percent substring(cBusPhone,0, 25) from Contact order by newid()
	    
	open test
	fetch next from test into @Contact_cBusPhone
	while @@FETCH_STATUS=0
	begin;

		update #contactresults SET 
			Contact_cBusPhone = @Contact_cBusPhone
		WHERE rowid = @icounter
		      
	  
		SET @icounter = @icounter+1;
	 
	fetch next from test into @Contact_cBusPhone
	end;
	close test;
	deallocate test;


	------------------------------------------------------------------
	------------------------------------------------------------------
	------------------------------------------------------------------


	update Contact set
		cFirstName = cr.Contact_cFirstName,
		cLastName = cr.Contact_cLastName,
		cBusPhone = cr.Contact_cBusPhone
	from #contactresults cr
	where contact.iContactID = cr.iContactID





------------------
------------------
------------------
--other misc deletes/changes
update Marks set mComment = '', mAltLang_Comment = ''
update location set cPhone = ''