Problem to copy values from one table to another in Mysql

abhishekgit · 03-28-2014, 03:28 AM

Hello,
I am attempting to copy values from one table to another by eliminating the duplicate values. The logic behind the process is, "Insert the first row in the destination table. If the succeeding rows of the source table are same as in the destination, do not insert. Otherwise, insert". This is the code.

Code:

//removing duplicates from the database
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
public class duplicate
{
	public static void main(String args[])
	{
		Statement stat=null,stat1=null,stat2=null;
		PreparedStatement pstat;
		Connection con=null;
		ResultSet result1,result2;
		int s_id=0;
		String date=null,time=null,temp=null;
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			System.out.println("Loaded Driver");
			con=DriverManager.getConnection("jdbc:mysql://localhost:3306/preprocessor","root","bigdata");
			System.out.println("Connected to mysql");
			stat=con.createStatement();
			stat1=con.createStatement();
			stat2=con.createStatement();
			String query1="insert into temperature_demo(ddslno,ddtstation_id,ddtdate,ddttime,ddtemp,ddtype_code,ddelevation) select dslno,dtstation_id,dtdate,dttime,dtemp,dtype_code,delevation from temperature_dup where dslno=1";
			String query2="select * from temperature_demo";
			String query3="select * from temperature_dup";
			//stat1.execute(query1);
			result1=stat1.executeQuery(query2);
			result2=stat2.executeQuery(query3);

				while(result1.next())
				{
					s_id=result1.getInt(2);
					date=result1.getString(3);
					time=result1.getString(4);
					temp=result1.getString(5);
					break;
				}
				//String query4=;
				
				pstat=con.prepareStatement("INSERT INTO temperature_demo VALUES(?,?,?,?,?,?,?)");
				while(result2.next())
				{
					int sno=result2.getInt(1);
					int s1_id=result2.getInt(2);
					String date1=result2.getString(3);
					String time1=result2.getString(4);
					String temp1=result2.getString(5);
					String type_cd=result2.getString(6);
					String elev=result2.getString(7);
					
					try
					{
					if( (s_id==s1_id)&&(date.equals(date1))&&(time.equals(time1))&&(temp.equals(temp1)) )
						;
					else
					{
						System.out.println(sno+" "+s1_id+" "+date1);
						pstat.setInt(1,sno);
						pstat.setInt(2,s1_id);
						pstat.setString(3,date1);
						pstat.setString(4,time1);
						pstat.setString(5,temp1);
						pstat.setString(6,type_cd);
						pstat.setString(7,elev);
					}
					}
					catch(Exception ex)
					{

					}
				}
			
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			if(con!=null)
				try
				{
					con.close();
				}
				catch(Exception ex)
				{

				}
			if(stat1!=null)
				try
				{
					stat1.close();
				}
				catch(Exception ex)
				{

				}
			if(stat2!=null)
				try
				{
					stat2.close();
				}
				catch(Exception ex)
				{

				}
		}
	}
}

The problem is, The values being inserted using PreparedStatement is not being reflected in the table. Kindly Help. Thanks

TB0ne · 03-28-2014, 09:15 AM

Quote:

Originally Posted by abhishekgit

Hello,
I am attempting to copy values from one table to another by eliminating the duplicate values. The logic behind the process is, "Insert the first row in the destination table. If the succeeding rows of the source table are same as in the destination, do not insert. Otherwise, insert". This is the code.

The problem is, The values being inserted using PreparedStatement is not being reflected in the table. Kindly Help. Thanks

I think you're doing this the wrong way. You don't give us any examples of the data, database, or what you're trying to do, but from what little you've told us, it seems like you only want records, based on some unique/distinct value IN that record. Without knowing the input or output desired, or seeing all the code, there's no way we can test what you've posted.

Which takes us back to a question you asked before, and the documentation you were pointed to before:
http://www.linuxquestions.org/questi...se-4175494534/
https://dev.mysql.com/doc/refman/5.7/en/select.html

Reading the documentation on the select command will show you the "DISTINCT" flag...which will let you select just unique records. Once you have those, you will have a subset that you can then insert into the other database. Or, you can delete the duplicates from the input database first, then do your migration.

szboardstretcher · 03-28-2014, 09:20 AM

I didn't look at your code. But you mention that you are using Mysql. And your requirements are:

copy values from one table to another by eliminating the duplicate values

In mysql, with the use of a temporary tables this is possible without a bunch of java code by doing this:

Code:

SELECT DISTINCT * INTO temporary_table FROM actual_table;