Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

job for collecting data Expand / Collapse
Author
Message
Posted Monday, September 17, 2012 8:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 3, 2014 1:02 AM
Points: 45, Visits: 136
I need a script to copy data from one table(source) to anotehr table (destination),
i want to sheduling this task for every 5 minute , rows which data were copied before don't copy again.
Post #1360534
Posted Monday, September 17, 2012 9:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:08 PM
Points: 5,401, Visits: 7,514
eh.shams (9/17/2012)
I need a script to copy data from one table(source) to anotehr table (destination),
i want to sheduling this task for every 5 minute , rows which data were copied before don't copy again.

What columns/data do you ahve to detect what are new rows? What's the table look like? what tools do you have available? SQLAgent, SSIS, what is available?



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1360539
Posted Monday, September 17, 2012 9:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 3, 2014 1:02 AM
Points: 45, Visits: 136
source table is like this one :

Customer Code|cslAccumulationInvoiceItmId| cslAccumulationInvoiceRef |cslDeliveryNoteRef| cslProductRef |Amount |Rate CurrencyRate| price before PriceInBaseCurrency| Deliver No| Date-Reci
400560 46663 46644 28532 21 21.54 504807 1 10873542.78 10873542.78 17169 1/10/2012 00:00
400560 46659 46644 28488 21 22.86 504807 1 11539888.02 11539888.02 17146 1/10/2012 00:00
400560 36414 36413 19204 47 17.66 519230 1 9169601.8 9169601.8 10956 11/30/2011 00:00
400560 35305 35304 18176 47 24.12 519230 1 12523827.6 12523827.6 10361 11/27/2011 00:00
400560 96490 96488 84639 47 22.26 568000 1 12643680 12643680 27082 8/8/2012 00:00
400560 35312 35304 18465 47 22.7 519230 1 11786521 11786521 10549 11/28/2011 00:00

______________________________________________

on the destination i have same table , with only add status (bit) column.
i want to reade from destination and if the status is false , send a message to customer, every 5 minute for example.

on server i have MSSQL SERVER 2008 SP1,
sql agent is ready to .

Post #1360542
Posted Monday, September 17, 2012 10:16 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, October 6, 2014 6:14 AM
Points: 564, Visits: 888
eh.shams (9/17/2012)
source table is like this one :

Customer Code|cslAccumulationInvoiceItmId| cslAccumulationInvoiceRef |cslDeliveryNoteRef| cslProductRef |Amount |Rate CurrencyRate| price before PriceInBaseCurrency| Deliver No| Date-Reci
400560 46663 46644 28532 21 21.54 504807 1 10873542.78 10873542.78 17169 1/10/2012 00:00
400560 46659 46644 28488 21 22.86 504807 1 11539888.02 11539888.02 17146 1/10/2012 00:00
400560 36414 36413 19204 47 17.66 519230 1 9169601.8 9169601.8 10956 11/30/2011 00:00
400560 35305 35304 18176 47 24.12 519230 1 12523827.6 12523827.6 10361 11/27/2011 00:00
400560 96490 96488 84639 47 22.26 568000 1 12643680 12643680 27082 8/8/2012 00:00
400560 35312 35304 18465 47 22.7 519230 1 11786521 11786521 10549 11/28/2011 00:00

______________________________________________

on the destination i have same table , with only add status (bit) column.
i want to reade from destination and if the status is false , send a message to customer, every 5 minute for example.

on server i have MSSQL SERVER 2008 SP1,
sql agent is ready to .



As mentioned by Kraig
What columns/data do you ahve to detect what are new rows?


Based on that you can have UPINSERT script and schedule it as per your need.


--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1360545
Posted Monday, September 17, 2012 10:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 3, 2014 1:02 AM
Points: 45, Visits: 136
would you please be more specific , or let me know with example ?
Thanks
Post #1360550
Posted Monday, September 17, 2012 10:58 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, October 6, 2014 6:14 AM
Points: 564, Visits: 888
eh.shams (9/17/2012)
would you please be more specific , or let me know with example ?
Thanks


e.g. in Source and Destination table CustID is unique number then we can have below query to insert only new records
INSERT INTO dest_tbl (Col1,Col2, .....)
SELECT Col1,Col2, FROM sorce_tbl
WHERE NOT EXISTS (SELECT CustId FROM dest_tbl )



--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1360557
Posted Monday, September 17, 2012 11:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 3, 2014 1:02 AM
Points: 45, Visits: 136
thanks , how i can create job to do it ,periodicaly ?
Post #1360560
Posted Monday, September 17, 2012 11:49 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
eh.shams (9/17/2012)
thanks , how i can create job to do it ,periodicaly ?


That could be a very involved question. I suggest you look through the Stairway to SQL Server Agent series: http://www.sqlservercentral.com/stairway/72403/

You'll probably only need to read the first 3-4 articles to do what you need to do.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1360569
Posted Tuesday, September 18, 2012 12:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 3, 2014 1:02 AM
Points: 45, Visits: 136
Thank's
Post #1360574
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse