|
I have a two MySQL databases, of which one is a Live(production) and the other is local database.
I want to synchronize specific table data from Live database to local database at regular time intervals(only specific tables and not the whole database).
Is it possible?? If yes, How can i implement??
Thanks in advance..
|
|
|
|
|
If MySQL has the concept of Jobs and you can hve a linked server then you write a stored proc that inserts new records from DB1 to DB2. Schedule that job to run as often as you require.
If MySQL supports replication you can also look into that as a much more robust operation, much more complex but it will have recovery options.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Arun Sylvester J wrote: is a Live(production) and the other is local database.
Probably depends on exactly what that means.
Certainly in normal production systems there is no access at all to the database outside of the hosting center. And of course if you really have a "local" database on some random client machine then just allowing arbitrary duplication is normally going to be a security concern.
|
|
|
|
|
I have a two MySQL databases, of which one is a Live(production) and the other is local database.
I want to synchronize specific table data from Live database to local database at regular time intervals(only specific tables and not the whole database).
Is it possible?? If yes, How can i implement??
Thanks in advance..
|
|
|
|
|
Greetings to you,
Please assist me in combining these sql queries
I have some record in my database (sql ce 4.0), using;
1.
Select Customer_ID as Customer, Credit, Debit, Account_Balance as Balance, Transaction_Date as Date
from ThriftsTrans Where Transaction_Date Between '15/Dec/2013' And '18/Dec/2013'
Produces this result;
Customer Credit Debit Balance Date
---------------------------------------------------------------------
000001 12000 12000 2013-12-16T14:32:00.9770000
000001 5000 17000 2013-12-16T14:32:26.0070000
000001 2000 15000 2013-12-16T14:32:43.3870000
000001 2000 17000 2013-12-17T10:23:08.6470000
000002 50000 50000 2013-12-17T10:25:35.4730000
000002 1000 51000 2013-12-17T10:25:49.4230000
000002 3000 48000 2013-12-17T10:26:07.4800000
If i run this;
2.
Select Customer_ID as Customer, sum(Credit) as Credit, sum(Debit) as Debit, (count(Credit) + count(Debit)) as Count from ThriftsTrans Where Transaction_Date Between '15/Dec/2013' And '18/Dec/2013' Group By Customer_ID
I will get;
Customer Credit Debit Count
-------------------------------------------
000001 19000 2000 4
000002 51000 3000 3
and lastly if i run;
3.
Select Customer_ID as Customer, Account_Balance as Balance, Transaction_Date as Date From ThriftsTrans Where Transaction_Date In (Select Max(Transaction_Date) From ThriftsTrans Where Transaction_Date
Between '15/Dec/2013' And '18/Dec/2013' Group By Customer_ID)
I will get;
Customer Balance Date
-------------------------------------------
000001 17000 2013-12-17T10:23:08.6470000
000002 48000 2013-12-17T10:26:07.4800000
But my problem now, is how do i combine query 2 & query 3 to get the following output from my database;
Customer Credit Debit Count Balance Date
---------------------------------------------------------------------
000001 19000 2000 4 17000 2013-12-17T10:23:08.6470000
000002 51000 3000 3 48000 2013-12-17T10:26:07.4800000
Please help me and thanks a lot in advance.
|
|
|
|
|
I suggest you start exploring the wonderful world of JOINs. Identify the common field (foreign key) and create an inner join then select the filed you need from the 2 tables.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks Holmes,
I know the solution to my problem can be solved by using one of the JOINS. But my challenge is that my little brain is not just connecting me to the appropriate JOIN to use.
I tried using a CROSS JOIN but the errors thrown shows that i am not formulating the query properly at all.
Please help me.
thanks a lot
|
|
|
|
|
paul4everyone wrote: I tried using a CROSS JOIN but the errors thrown shows that i am not formulating
the query properly at all.
Why didn't you share those details in your question? 
thatrajaCode converters | Education Needed
No thanks, I am all stocked up. - Luc Pattyn
When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute
|
|
|
|
|
because what i did with the joins doesnt make sense.
i just messed up the query 2 and 3 using JOIN.
please help me
|
|
|
|
|
I suggest you start studying, this article [^]may be useful to you.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
you would need to look at an INNER JOIN BUT also formulate your SELECT accordingly to show only the columns that you require.
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
thanks for the reply.
but i really dont know what to do in this.
please assist me
|
|
|
|
|
|
Thanks for the link
but all the examples i am seeing is slightly different from my case.
i dont know how to capture this fraction of code in the query.
i mean
(Select Max(Transaction_Date) From ThriftsTrans Where Transaction_Date
Between '15/Dec/2013' And '18/Dec/2013' Group By Customer_ID)
|
|
|
|
|
After asking so many questions, i finally succeeded in tweaking out a query that is correct.
it is as follows;
Select Customer, Count, Credit, Debit, Balance, Date from
(
Select Customer_ID as Customer, (count(Credit) + count(Debit)) as Count, sum(Credit) as Credit, sum(Debit) as Debit
from ThriftsTrans Where Transaction_Date Between '15/Dec/2013' And '18/Dec/2013' Group By Customer_ID
) as a left join (
Select Customer_ID as Cust, Account_Balance as Balance,
Transaction_Date as Date From ThriftsTrans Where Transaction_Date In
(Select Max(Transaction_Date) From ThriftsTrans Where Transaction_Date
Between '15/Dec/2013' And '18/Dec/2013' Group By Customer_ID)
) as b on a.Customer = b.Cust
thanks to you all
|
|
|
|
|
Hi,
I have to write a query where there will be multiple filters selected by user. These filters will be selected randomly in any combination
(for example filters like: Co.name, deptId, Startdate, endDate, employee name, Assignment, Shifts, class)
Can anyone tell me how to write such type of query?
As there cn be n numbers of permutation & combination of all filters.
|
|
|
|
|
one solution that I have used before is
select *
from table
where ((@endDate is null) or (enddate = @enddate)) and
((@deptid is null) or (deptid = @deptid)) and
((@employeeName is null) or (employeeName = @employeeName))
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
Thanks Simon..for ur reply will implement this today and let u know..hope this works for me.
|
|
|
|
|
This is not working in case I dont give deptid or any one of parameters.
pls help me.
|
|
|
|
|
What simon supplied is the idea/concept of how it should be done, writing the actual query is up to you.
The sample shows you that simon is testing the value of the variable to find out if it is null and if it is not null then applying the filter. You need to understand the concept and extend it to your needs. That is called learning!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Share your query first
thatrajaCode converters | Education Needed
No thanks, I am all stocked up. - Luc Pattyn
When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute
|
|
|
|
|
I run a query in SSMS. A date/time column displays correctly like "2013-12-15 12:19:43.583".
I get the data table object back into my app and iterate through the data rows. Converting the column using DateTime always "loses" the millisecond component. I tried converting it to a string just to see if it was coming through that way, but no, the millisecond is always "lost". I then changed the query to cast the datetime column into two columns, one for the date and one for the time. Converting the time column to a DateTime now correctly contains the millisecond component.
If the column in the database is defined as datetime why am I not getting the millsecond component? There's no problem converting the value in the row using DateTime but the loss of the millsecond puzzles me because I thought a database datetime column would be wholly compatible with a Dotnet DateTime object?
Are my pudgy fingers, and thus by association, my pudgy brain, doing something wrong in my code?
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
|
|
|
|
|
Thankfully in all my years as a LOB developer I have not once had to utilise the millisecond information so I have no idea 
I'm assuming this is an intellectual exercise only!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Nothing to see here. 
Are you using a datareader?
If so, using DateTime myDate = (DateTime)reader["MyDateTimeColumn"]; will convert the DateTime from the DB into a string and then convert it back to DateTime, and thus losing precision.
Instead use reader.GetDateTime(reader.GetOrdinal("MyDateTimeColumn"));
<edit>
Instead use DateTime myDate = (DateTime)reader.GetSQLDateTime(reader.GetOrdinal("MyDateTimeColumn"));
or DateTime myDate = (DateTime)reader.GetSQLValue(reader.GetOrdinal("MyDateTimeColumn"));
</edit>
Otherwise you need to update the question with a bit of code.
modified 16-Dec-13 9:01am.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
