Re: data moving - Database Discussion Boards

Hi

Select count(*) From TableName
Select count(column) From TableName
Select * From TableName

In these queries which will work more fast ? What could be the reason ?

printf("Navaneeth!!")

www.w3hearts.com

DooDooo wrote:
In these queries which will work more fast ?

Try them out to find out. Once you've run them, have a think about what each one is actually doing and how many rows they are returning. Hint - try the second count on a nullable column.

Deja View - the feeling that you've seen this post before.

DooDooo wrote:
In these queries which will work more fast ? What could be the reason ?

They all do different things. It is an invalid comparison.

Upcoming events:
* Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Ya I know all three queries are doing different things. What I mean to say was my friend told that select count(*) will take much time that executing select * from table. Because first it will select all data from table and finds the count. I just wanted to know whether this is rite or not ?

printf("Navaneeth!!")

www.w3hearts.com

DooDooo wrote:
What I mean to say was my friend told that select count(*) will take much time that executing select * from table. Because first it will select all data from table and finds the count. I just wanted to know whether this is rite or not ?

You also have to take in to consideration network bandwidth used. SELECT COUNT(*) FROM MyTable returns a single value. SELECT * FROM MyTable returns the entire contents of the table.

Also, I'd guess the COUNT(*) would be able to take into account an index so it wouldn't actually have to read every single row, just one of the indexes (which would be a lot less data)

Upcoming events:
* Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Thank you very much

printf("Navaneeth!!")

www.w3hearts.com

Hi, All.

example: I have two databases. mySQL and MS SQL. I have to load some data from MySQL into MS SQL.
I can't use SSIS or something like that because my version of MS SQL Server hasn't it. so I must write my pwn programm foor this task.

I write two classes - first fro working with MySQL and second for MS SQL.

----------
MySQL
----------
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;

public class MySqlClass
{
public MySqlClass()
{
//
// TODO: Add constructor logic here
//
}

static public MySqlConnection myConnection = null;

public string GetConnection(string con_str)
{
string connection_string = string.Empty;

if (con_str.Length == 0)
connection_string = System.Configuration.ConfigurationManager.ConnectionStrings[2].ToString();
else
connection_string = con_str;

try
{
myConnection = new MySqlConnection(connection_string);
myConnection.Open();
}
catch (MySqlException err)
{
return "Erron in connection :" + err.Message;
}

finally
{
if (myConnection != null)
myConnection.Close();
}

return null;
}

public DataSet MySqlOpen(string sql_cmd)
{
if (myConnection != null)
myConnection.Close();

myConnection.Open();

MySqlDataAdapter myDataAdapter = new MySqlDataAdapter(sql_cmd, myConnection);
DataSet myDataSet = new DataSet();

myDataAdapter.Fill(myDataSet);

myConnection.Close();

return myDataSet;

}

public void MySqlRun(string sql_cmd)
{
if (myConnection != null)
myConnection.Close();

myConnection.Open();
MySqlCommand cmd = new MySqlCommand(sql_cmd, myConnection);
cmd.ExecuteNonQuery();
myConnection.Close();

}

public void MySqlRunTransaction(string[] queries)
{
MySqlTransaction trans = null;

if (myConnection != null)
myConnection.Close();

try
{
myConnection.Open();

trans = myConnection.BeginTransaction();

MySqlCommand cmd = new MySqlCommand();

cmd.Connection = myConnection;
cmd.Transaction = trans;

foreach (string query in queries)
{
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (MySqlException SqlErr)
{
if (trans != null)
{
trans.Rollback();
}
throw new Exception("An error occured while transaction", SqlErr);
//return;
}
finally
{
if (myConnection != null)
{
myConnection.Close();
}
}
}

}

----------

----------
MS SQL
----------
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public class SqlClass
{
public SqlClass()
{
//
// TODO: Add constructor logic here
//
}

static public SqlConnection myConnection = null;

public string GetConnection(string con_str)
{

string connection_string = string.Empty;

if (con_str.Length == 0)
connection_string = System.Configuration.ConfigurationManager.ConnectionStrings[3].ToString();
else
connection_string = con_str;

try
{
myConnection = new SqlConnection(connection_string);
myConnection.Open();

}
catch (SqlException err)
{
return "Erron in connection :" + err.Message;
}

finally
{
if (myConnection != null)
myConnection.Close();
}

return null;
}

public DataSet SqlOpen(string sql_cmd)
{
if (myConnection != null)
myConnection.Close();

myConnection.Open();

SqlDataAdapter myDataAdapter = new SqlDataAdapter(sql_cmd, myConnection);
DataSet myDataSet = new DataSet();

myDataAdapter.Fill(myDataSet);

myConnection.Close();

return myDataSet;

}

public void SqlRun(string sql_cmd)
{
if (myConnection != null)
myConnection.Close();

myConnection.Open();
SqlCommand cmd = new SqlCommand(sql_cmd, myConnection);
cmd.ExecuteNonQuery();
myConnection.Close();

}

public void SqlRunTransaction(string[] queries)
{
SqlTransaction trans = null;

if (myConnection != null)
myConnection.Close();

try
{
myConnection.Open();

trans = myConnection.BeginTransaction();

SqlCommand cmd = new SqlCommand();

cmd.Connection = myConnection;
cmd.Transaction = trans;

foreach (string query in queries)
{
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (SqlException SqlErr)
{
if (trans != null)
{
trans.Rollback();
}
throw new Exception("An error occured while transaction", SqlErr);
//return;
}
finally
{
if (myConnection != null)
{
myConnection.Close();
}
}
}

}

----------

so in my main unit i declared them

public MySqlClass mys = new MySqlClass();
public SqlClass ms = new SqlClass();

and when I wanna get result from MySQL i do -

DataSet myDataSet = new DataSet();

try
{
myDataSet = mys.MySqlOpen(txtMySQLQuery.Text);
}
catch (MySqlException err)
{
Response.Write(err.ToString());
}

so I have a dataset with data (now myDataSet contains result of my query isn't ?)

my question - HOW can I load a data from this dataset into another database and table? i think I should use something like dataadapter or dataset but i'm not sure. and can I use ms.SqlRun for this (i declared it before) or I should use some another way?

thanx a lot!!!

--
"Success is simple. Do what's right, the right way, at the right time."

best regards,
tradakad

tradakad wrote:
I can't use SSIS or something like that because my version of MS SQL Server hasn't it.

SSIS is just a rebranded version of DTS. Use DTS.

Upcoming events:
* Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

I haven't got it too Frown | :(

can i FREE download it? i mean for my MS SQL Server Express Edition version?

thanx!

--
"Success is simple. Do what's right, the right way, at the right time."

best regards,
tradakad

I'm fairly sure that's not true, otherwise why would there be a separate download for the legacy DTS runtime? SSIS is effectively the next version of DTS (in development and Beta it was still called DTS and the process is called MsDtsServer.exe), but is not fully compatible with 2000's DTS.

Yes, if targetting SQL Server 2000 and optionally 2005, use DTS. Otherwise use SSIS.

SQL Server 2005 Express Edition doesn't have SSIS. That's only included in Standard and Enterprise Editions (and of course Developer Edition which is Enterprise with a restricted licence, the capability to run on client Windows operating systems, and autoshrink turned on). DTS was also not present in MSDE 2000.

Stability. What an interesting concept. -- Chris Maunder

Mike Dimmick wrote:
I'm fairly sure that's not true, otherwise why would there be a separate download for the legacy DTS runtime?

It is a rebranded DTS. As you pointed out they rebranded it for SQL Server 2005 half way through the development cycle - hence there are references to DTS in the code.

Mike Dimmick wrote:
SQL Server 2005 Express Edition doesn't have SSIS. That's only included in Standard and Enterprise Editions (and of course Developer Edition which is Enterprise with a restricted licence, the capability to run on client Windows operating systems, and autoshrink turned on). DTS was also not present in MSDE 2000.

True. I made an assumption that it was one of the full server or developer products that was being used.

Upcoming events:
* Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

HI all,

my application is to read a access dtbase tables which has the same format of columns. but it gives an update statement Syntax error. please help

OleDbConnection con = new OleDbConnection();
OleDbDataAdapter da = new OleDbDataAdapter();
OleDbCommand selcom = new OleDbCommand();
OleDbCommand upcom = new OleDbCommand();
DataSet ds = new DataSet();

private void button1_Click(object sender, EventArgs e)
{
string path = txtSelectLoc.Text;
processTables(path);
//UpdateMethod(path, "GRO");

}

void processTables(string path)
{

DataTable dt = new DataTable();
dt = sqlConnection().Tables[0];

foreach (DataRow dr in dt.Rows)
{

UpdateMethod(path,"WWA");

}

}

DataSet sqlConnection()
{
SqlCommand sqlSelect = new SqlCommand();
SqlConnection SQLcon = new SqlConnection("data source=Ruwandi;initial catalog=MIS;user id=sa");
SqlDataAdapter SQLda = new SqlDataAdapter();
sqlSelect.CommandText= "Select Alias from mfLocation where Alias<>''";
sqlSelect.Connection=SQLcon;
SQLda.SelectCommand = sqlSelect;
SQLda.Fill(ds);
return ds;
//dataGridView1.DataSource = ds.Tables[0];
}

private void UpdateMethod(string path,string tbName)
{
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path +
";Jet OLEDB:Database Password=sanuja";
DataTable dt = SelectMethod(tbName).Tables[0];
upcom.Connection = con;
con.Open();
int x = 0;
foreach (DataRow dr in dt.Rows)
{

x = x + 1;
string str = dr["CATEGORY"].ToString();
dr.AcceptChanges();
upcom.CommandText = "update CON_"+ tbName +" set BGT_APR=0,BGT_MAY=0,BGT_JUN=0,BGT_JUL=0,BGT_AUG=0,BGT_SEP=0,BGT_OCT=0,BGT_NOV=0,BGT_DEC=0,BGT_JAN=0,BGT_FEB=0,BGT_MAR=0,BUDGETED=0,BUDGET_PES=0," +
"NET_APR=0,NET_MAY=0,NET_JUN=0,NET_JUL=0,NET_AUG=0,NET_SEP=0,NET_OCT=0,NET_NOV=0,NET_DEC=0,NET_JAN=0,NET_FEB=0,NET_MAR=0," +
"LYNET_APR=" + dr["NET_APR"] + ",LYNET_MAY=" + dr["NET_MAY"] + ",LYNET_JUN=" + dr["NET_JUN"] + ",LYNET_JUL=" + dr["NET_JUL"] + ",LYNET_AUG=" + dr["NET_AUG"] + ",LYNET_SEP=" + dr["NET_SEP"] + ",LYNET_OCT=" + dr["NET_OCT"] + ",LYNET_NOV=" + dr["NET_NOV"] + ",LYNET_DEC=" + dr["NET_DEC"] + ",LYNET_JAN=" + dr["NET_JAN"] + ",LYNET_FEB=" + dr["NET_FEB"] + ",LYNET_MAR=" + dr["NET_MAR"] + "," +
"ADJ_APR=0,ADJ_MAY=0,ADJ_JUN=0,ADJ_JUL=0,ADJ_AUG=0,ADJ_SEP=0,ADJ_OCT=0,ADJ_NOV=0,ADJ_DEC=0,ADJ_JAN=0,ADJ_FEB=0,ADJ_MAR=0," +
"LYADJ_APR=" + dr["ADJ_APR"] + ",LYADJ_MAY=" + dr["ADJ_MAY"] + ",LYADJ_JUN=" + dr["ADJ_JUN"] + ",LYADJ_JUL=" + dr["ADJ_JUL"] + ",LYADJ_AUG=" + dr["ADJ_AUG"] + ",LYADJ_SEP=" + dr["ADJ_SEP"] + ",LYADJ_OCT=" + dr["ADJ_OCT"] + ",LYADJ_NOV=" + dr["ADJ_NOV"] + ",LYADJ_DEC=" + dr["ADJ_DEC"] + ",LYADJ_JAN=" + dr["ADJ_JAN"] + ",LYADJ_FEB=" + dr["ADJ_FEB"] + ",LYADJ_MAR=" + dr["ADJ_MAR"] +
" where CATEGORY=\'" + str + "\'";

da.UpdateCommand = upcom;
upcom.ExecuteNonQuery();

}
dt.AcceptChanges();

dataGridView1.DataSource = dt;
}

DataSet SelectMethod(string tbName)
{
selcom.CommandText = "select * from CON_"+tbName;
selcom.Connection = con;
da.SelectCommand = selcom;
da.Fill(ds);
return ds;

}

private void butSelectLoc_Click(object sender, EventArgs e)
{
DialogResult result = openFileDialog1.ShowDialog();

if (result == DialogResult.OK)
{
string path = openFileDialog1.FileName;
txtSelectLoc.Text = path;
}
}

regards
ruwandi

rkherath

Use parameterised queries. DO NOT INJECT DATA INTO A SQL STATEMENT. The security of your application will be compromised. See: SQL Injection Attacks and Tips on How to Prevent Them[^]

Upcoming events:
* Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

hi,

thanks. I will do that.

regards
ruwandi

rkherath

Hi, ALL!
in MySQL I could execute this "show tables" and i got a list of tables in database. can I get a list of tables in a MS SQL database and how?

thanx!

--
"Success is simple. Do what's right, the right way, at the right time."

best regards,
tradakad

select * from INFORMATION_SCHEMA.TABLES

Gautham

Yep, i mean exactly this thing..
thank you, bro!

--
"Success is simple. Do what's right, the right way, at the right time."

best regards,
tradakad

welcome anytime bro!

Gautham

I am working on Windows mobile platform using C#. I am using Merge Replication to do synchronization between SQL Server2000 and SQL Server 2005 Mobile edition and t is working fine.

The Server Tables are copied in the Client DB. It works fine with one way, i.e., server pushing data to Client Database (in PDA), but when i tried to update from PDA database, server keeps over writting PDA databse.

The requirement is that before adding our PDA database as subscriber,there are some tables with same name and structure as in Server with some records. After that i added it as subscriber and sycnchronized.The tables in the server are going to overrite the records in PDA.My requirement is to not overrite the records and it should update the existing table.Is it possible....Can Any one Help meeeeeeeeeeeee plzzzzzzzzzz.

Ayyappa Konakalla

Hi i want to know exact hour and minute of two times...,
i used this query:
"SELECT '2007-03-14 12:50:55.070' as login,'2007-03-14 13:00:55.070' as logout,DATEDIFF(hour, '2007-03-14 12:50:55.070', '2007-03-14 13:00:55.070') AS hours,DATEDIFF(minute, '2007-03-14 12:50:55.070', '2007-03-14 13:00:55.070') AS minut"

But i am getting value as 1 hour 10 minute like...,
But actual output is 10 minute only...,
And suppose the second time will be in some other day means...,
the logout time will be '2007-03-14 13:00:55.070' as logout
for this also i want to calculate exact hours and minutes like that any query is there in sqlserver???
Advance thanks...

Magi

take the difference in minutes only and then convert the minutes into hours & minutes

SELECT '2007-03-14 12:50:55.070' as login,'2007-03-14 13:00:55.070' as logout,
cast(DATEDIFF(minute, '2007-03-14 12:50:55.070', '2007-03-14 14:00:55.070') / 60 as int) AS h,
DATEDIFF(minute, '2007-03-14 12:50:55.070', '2007-03-14 14:00:55.070') -
cast((DATEDIFF(minute, '2007-03-14 12:50:55.070', '2007-03-14 14:00:55.070') / 60) as int) * 60 AS m

Regards
KP

Oh god sorry man really its working cool...,
once again sorry man..........,
Thank u frd

Magi

you welcome

Regards
KP

hi,

I am having a Access table which does not have a primary key.
i wanna change data in all the rows. my coding has no errors . but when i try to update it writes the same value for all the rows per field. please help me i am stuck with this.

here is the coding

void oledbConnection(/*string path, string CONstr*/)
{

//OleDbTransaction trans;
OleDbCommand OLEDBSelectCom = new OleDbCommand();
OleDbCommand OLEDBupdateCom = new OleDbCommand();
OleDbCommand OLEDBDeletecom = new OleDbCommand();
OleDbCommand OLEDBInsertCom = new OleDbCommand();

OleDbConnection OLEDBcon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\\yearEndExe\\WmsFoodCityPerformance" +
".mdb;Jet OLEDB:Database Password=sanuja");
OLEDBcon.Open();
OleDbTransaction trans;
trans = OLEDBcon.BeginTransaction();
OleDbDataAdapter OLEDBda = new OleDbDataAdapter();
OLEDBSelectCom.Transaction = trans;
OLEDBSelectCom.UpdatedRowSource = UpdateRowSource.Both;
OLEDBSelectCom.CommandText = "Select * from CON_MIN";
//OLEDBDeletecom.CommandText = "Delete from CON_KPT";

OLEDBupdateCom.Connection = OLEDBcon;
OLEDBSelectCom.Connection = OLEDBcon;

DataSet ds = new DataSet();
OLEDBda.SelectCommand = OLEDBSelectCom;
OLEDBda.UpdateCommand = OLEDBupdateCom;

OleDbCommandBuilder combil = new OleDbCommandBuilder(OLEDBda);
OLEDBda.Fill(ds);
DataTable dt = new DataTable();
dt = ds.Tables[0];
int x=0;
dataGridView1.DataSource = dt;
MessageBox.Show("Select completed");
foreach (DataRow dr in dt.Rows)
{
//MessageBox.Show(dr["NET_JUL"].ToString());
//MessageBox.Show(dr["LYNET_JUL"].ToString());
dr.BeginEdit();
dr["BGT_APR"] = 0;
dr["BGT_JUN"] = 0;
dr["BGT_JUL"] = 0;
dr["BGT_AUG"] = 0;
dr["BGT_SEP"] = 0;
dr["BGT_OCT"] = 0;
dr["BGT_NOV"] = 0;
dr["BGT_DEC"] = 0;
dr["BGT_JAN"] = 0;
dr["BGT_FEB"] = 0;
dr["BGT_MAR"] = 0;
dr["BUDGETED"] = 0;
dr["BUDGET_PES"] = 0;
dr["LYNET_APR"] = dr["NET_APR"];
dr["LYNET_JUN"] = dr["NET_JUN"];
dr["LYNET_JUL"] = dr["NET_JUL"];
dr["LYNET_AUG"] = dr["NET_AUG"];
dr["LYNET_SEP"] = dr["NET_SEP"];
dr["LYNET_OCT"] = dr["NET_OCT"];
dr["LYNET_NOV"] = dr["NET_NOV"];
dr["LYNET_DEC"] = dr["NET_DEC"];
dr["LYNET_JAN"] = dr["NET_JAN"];
dr["LYNET_FEB"] = dr["NET_FEB"];
dr["LYNET_MAR"] = dr["NET_MAR"];
dr["NET_APR"] = 0;
dr["NET_JUN"] = 0;
dr["NET_JUL"] = 0;
dr["NET_AUG"] = 0;
dr["NET_SEP"] = 0;
dr["NET_OCT"] = 0;
dr["NET_NOV"] = 0;
dr["NET_DEC"] = 0;
dr["NET_JAN"] = 0;
dr["NET_FEB"] = 0;
dr["NET_MAR"] = 0;
dr["LYADJ_APR"] = dr["ADJ_APR"];
dr["LYADJ_JUN"] = dr["ADJ_JUN"];
dr["LYADJ_JUL"] = dr["ADJ_JUL"];
dr["LYADJ_AUG"] = dr["ADJ_AUG"];
dr["LYADJ_SEP"] = dr["ADJ_SEP"];
dr["LYADJ_OCT"] = dr["ADJ_OCT"];
dr["LYADJ_NOV"] = dr["ADJ_NOV"];
dr["LYADJ_DEC"] = dr["ADJ_DEC"];
dr["LYADJ_JAN"] = dr["ADJ_JAN"];
dr["LYADJ_FEB"] = dr["ADJ_FEB"];
dr["LYADJ_MAR"] = dr["ADJ_MAR"];
dr["LYADJ_APR"] = 0;
dr["LYADJ_JUN"] = 0;
dr["LYADJ_JUL"] = 0;
dr["LYADJ_AUG"] = 0;
dr["LYADJ_SEP"] = 0;
dr["LYADJ_OCT"] = 0;
dr["LYADJ_NOV"] = 0;
dr["LYADJ_DEC"] = 0;
dr["LYADJ_JAN"] = 0;
dr["LYADJ_FEB"] = 0;
dr["LYADJ_MAR"] = 0;
dr.EndEdit();
//MessageBox.Show(dr["NET_JUL"].ToString());
//MessageBox.Show(dr["LYNET_JUL"].ToString());
OLEDBupdateCom.CommandText = "Update CON_MIN set NET_APR=0,LYNET_APR=@LYNET_APR,NET_MAY=0,LYNET_MAY=@LYNET_MAY,NET_JUN=0,LYNET_JUN=@LYNET_JUN,NET_JUL=0,LYNET_JUL=@LYNET_JUL,NET_AUG=0,LYNET_AUG=@LYNET_AUG,NET_SEP=0,LYNET_SEP=@LYNET_SEP,NET_OCT=0,LYNET_OCT=@LYNET_OCT,NET_NOV=0,LYNET_NOV=@LYNET_NOV";
OLEDBupdateCom.Transaction = trans;
x = 10+ x;

OLEDBupdateCom.Parameters.Add(new OleDbParameter("@LYNET_APR", x));
OLEDBupdateCom.Parameters.Add(new OleDbParameter("@LYNET_MAY", dr["LYNET_MAY"]));
OLEDBupdateCom.Parameters.Add(new OleDbParameter("@LYNET_JUN", dr["LYNET_JUN"]));
OLEDBupdateCom.Parameters.Add(new OleDbParameter("@LYNET_JUL", dr["LYNET_JUL"]));
OLEDBupdateCom.Parameters.Add(new OleDbParameter("@LYNET_AUG", dr["LYNET_AUG"]));
OLEDBupdateCom.Parameters.Add(new OleDbParameter("@LYNET_SEP", dr["LYNET_SEP"]));
OLEDBupdateCom.Parameters.Add(new OleDbParameter("@LYNET_OCT", dr["LYNET_OCT"]));
OLEDBupdateCom.Parameters.Add(new OleDbParameter("@LYNET_NOV", dr["LYNET_NOV"]));

//OLEDBupdateCom.ExecuteNonQuery();
//dr.AcceptChanges();

}

OLEDBda.Update(dt);

//dt.AcceptChanges();
//OLEDBda.UpdateCommand = combil.GetUpdateCommand();
//OLEDBda.Update(ds);

trans.Commit();
ds.AcceptChanges();
}
plzzzzzzzzz help

Regards
ruwandi

rkherath

How can I update columns of three tables in on sql statement