Move values from db A to db B

gistfile1.php

<?php

echo "Retaining lookup tables <BR /><BR />";



///////////////////////////////////////////////////////////////////////////////////////
  


	$sql = "select * from datatypes where cName not in (select cName from $TOdatabase.dbo.datatypes where cSproc = 'lstLookupValues') and  cSproc='lstLookupValues' ";
	
	$query = sqlsrv_query($FROMconn, $sql );

	if( $query === false ){
	      if( ($errors = sqlsrv_errors() ) != null){
	         foreach( $errors as $error){
	            echo "<font color=red>SQLSTATE: ".$error[ 'SQLSTATE']."\n";
	            echo "code: ".$error[ 'code']."\n";
	            echo "message: ".$error[ 'message']."\n</font>";
	         }
	      }
	$errors = '';
	}
	

	
///////////////////////////////////////////////////////////////////////////////////////


	echo "<BR />working on datatypes...<BR />";

	$count = 0;
	while($row = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC)){
	
		//print_r($row);

		//building the sql query for inserting the datatypes
		///////////////////////////////////////////////////////////////////////////////////////
	

		$CurrentDatatypeID = $row['iDatatypesID'];	//well need this for later
		$NewDatatypeID = '';				//well also populate this later
		$SQLSTRING = 'INSERT INTO datatypes (';
	
		
		//the count==0 is to skip the first column (because the first column is set to auto-increment)

		//get the size of the list we are working with.
		$sizeOfList = 0;
		foreach($row as $derp){ $sizeOfList++; }

		
		$count = 0;
		foreach($row as $key=>$value){
			$count++;
			
			if($count == 1) { continue; }

			if($count == $sizeOfList){ 
				$SQLSTRING .= $key . ')';
				continue;
			}

			$SQLSTRING .= $key . ', ';
		}
		$count = 0;

		$SQLSTRING .= ' VALUES (';

		foreach($row as $key=>$value){
			$count++;
			
			if($count == 1) {continue;}

			if($count == $sizeOfList){ 
				$SQLSTRING .= "'" . $value . "'" . ')';
				continue;
			}

			$SQLSTRING .= "'" . $value . "'" . ', ';
		}
		

		echo $SQLSTRING;


		$newquery = sqlsrv_query($TOconn, $SQLSTRING);
	
		if( $newquery === false ){
		      if( ($errors = sqlsrv_errors() ) != null){
		         foreach( $errors as $error){
		            echo "<font color=red>SQLSTATE: ".$error['SQLSTATE']."\n";
		            echo "code: ".$error['code']."\n";
		            echo "message: ".$error[ 'message']."\n</font>";
		         }
		      }
		$errors = '';
		}

		echo "<pre>";
		//echo $row['cName'] . "<BR />";
		echo "</pre>";
	
		$count++;

///////////////////////////////////////////////////////////////////////////////////////

		//now starting to put the lookupvalues themselves into the tables, associated with the correct datatypes
		//>>/////////////////////////////////////////////////////////////////////////////////////

		//first we need to reselect the record we just added to see what datatypesid SQL assigned it
		$reselectQuery = sprintf("SELECT iDatatypesID FROM datatypes WHERE cName = '%s'", $row['cName'] );	//this could be fixed later to be dynamic...but its probably fine

		$reselectQuery = sqlsrv_query($TOconn, $reselectQuery);
		$reselectQueryRow = sqlsrv_fetch_array($reselectQuery, SQLSRV_FETCH_ASSOC);

		echo "<BR />" . $reselectQueryRow['iDatatypesID'] . "<BR />";

		if( $reselectQuery === false ){
		      if( ($errors = sqlsrv_errors() ) != null){
		         foreach( $errors as $error){
		            echo "<font color=red>SQLSTATE: ".$error[ 'SQLSTATE']."\n";
		            echo "code: ".$error[ 'code']."\n";
		            echo "message: ".$error[ 'message']."\n</font>";
		         }
		      }
		$errors = '';
		}

///////////////////////////////////////////////////////////////////////////////////////


		//now we have to select the old lookupvalues from the old data and reinsert them into the new data
		
		$oldLookupValuesQuery = sprintf("SELECT * FROM lookupvalues WHERE idatatypesid = '%s'", $CurrentDatatypeID);

		$oldLookupValuesQuery = sqlsrv_query($FROMconn, $oldLookupValuesQuery);

		if( $oldLookupValuesQuery === false ){
		      if( ($errors = sqlsrv_errors() ) != null){
		         foreach( $errors as $error){
		            echo "<font color=red>SQLSTATE: ".$error[ 'SQLSTATE']."\n";
		            echo "code: ".$error[ 'code']."\n";
		            echo "message: ".$error[ 'message']."\n</font>";
		         }
		      }
		$errors = '';
		}

		//we have to iterate through each old lookupvalues query and reinsert it
		while($oldLookupValuesRow = sqlsrv_fetch_array($oldLookupValuesQuery, SQLSRV_FETCH_ASSOC)){


			$LVSQLSTRING = 'INSERT INTO LookupValues (';

			//get the size of the list we are working with.
			$sizeOfLVList = 0;
			foreach($oldLookupValuesRow as $derp){ $sizeOfLVList++; }
	
			
			$newcount = 0;
			foreach($oldLookupValuesRow as $key=>$value){
				$newcount++;
				
				if($newcount == 1) { continue; }
	
				if($newcount == $sizeOfLVList){ 
					$LVSQLSTRING .= $key . ')';
					continue;
				}
	
				$LVSQLSTRING .= $key . ', ';
			}
			$newcount = 0;
	
			$LVSQLSTRING .= ' VALUES (';
	
			foreach($oldLookupValuesRow as $key=>$value){
				$newcount++;
				
				if($newcount == 1) {continue;}

				if($key == 'iDataTypesID') { $LVSQLSTRING .= "'" . $reselectQueryRow['iDatatypesID'] . "'" . ', '; continue; }
	
				if($newcount == $sizeOfLVList){ 
					$LVSQLSTRING .= "'" . slashify($value) . "'" . ')';
					continue;
				}
	
				$LVSQLSTRING .= "'" . slashify($value) . "'" . ', ';
			}
			
	
			echo $LVSQLSTRING . "<BR /><BR />";


			$oldLookupValuesINSERTQuery = sqlsrv_query($TOconn, $LVSQLSTRING);

			if( $oldLookupValuesINSERTQuery === false ){
			      if( ($errors = sqlsrv_errors() ) != null){
			         foreach( $errors as $error){
			            echo "<font color=red>SQLSTATE: ".$error[ 'SQLSTATE']."\n";
			            echo "code: ".$error[ 'code']."\n";
			            echo "message: ".$error[ 'message']."\n</font>";
			         }
			      }
			$errors = '';
			}
	
		}



///////////////////////////////////////////////////////////////////////////////////////


	}

	echo "<BR /><pre>$count dataypes rows updated.</pre><BR />";



///////////////////////////////////////////////////////////////////////////////////////









?>