How to transfer the data from one SQL server to another using insert into command using python?

i want to transfer the incremental data from one SQL server to another SQL server using python scripts .

i have done this things:-

import os import pyodbc #os.chdir("D:TransOrgProjectsPPAPFilesExtrusion") myConn1 = pyodbc.connect( driver="SQL Server",server="xxx.xxx.xxx.xxx",database="xxx",user="xxx",password="xxxx" ) myConn2 = pyodbc.connect( driver="SQL Server",server="xxx.xxx.xxx.xxx",database="xxx",user="xxx",password="xxx" ) cur1=myConn1.cursor() cur2=myConn2.cursor() print('Cursor established') #cur.execute(''' drop table test_load ;''') #cur.execute("create table test_load (name varchar(40), age int) ON [PRIMARY]") #db_cmd = '''SELECT Fact_Suborder_Journey_Key,SubOrderID,SubOrderDate,SubOrderDate_INT,DIM_Item_Key,DIM_Product_Key,DIM_Category_Key #,Dim_Customer_Key,DIM_DTH_Key,Dim_Acquisition_Channel_Key,DIM_Geography_Key,DIM_LeadSource_Key,Dim_MarketPlace_Key,Dim_SalesBucket_Key #,Cancelled_DATE,MarkedShipped_DATE,MarkedReshipped_DATE,MarkedSalesReturn_DATE,SalesReturn_DATE,MarkedDelivered_DATE,Margin #,GSV,ItemPrice,PayablePrice,PaidByGCAmt,PaidByDC,IsValid,IsSuperBooked,IsSameDayCancellation,IsMISvalid,IsCancelled,IsShipped #,IsMarkedShipped,IsLostInTransit,IsNDR,IsRTO,IsORTV,IsDelivered,IsMarkedDelivered,IsExecuted,IsReturnRequested,IsProvisionalExecuted #,IsSalesReturnDone,IsBuyOrSell,IsSellerShipper,IsNCAwarded,IsMailSentOnStatusChange,IsAdvPayment,IsAdvPaymentPerc,IsPreorderable,IsRenewType #,IsDeleted,IsCallCenter,OrderType,IS_TV_PRODUCT,MarketPlaceId,Dim_DSS_Key,Dim_OrderShowTag_Key,Dim_Suborder_Delivery_Mode_Key,Dim_CustomerMDM_Key #,Dim_NewVsRepeatCustomerMDM_Key #FROM transorg_DW.dbo.Fact_Suborder_Journey #where SubOrderDate between '2016-06-14 00:00:00' and '2016-06-15 23:59:59';''' #res = cur1.execute(db_cmd) #print (res) #for r in res: # print (r) db_cmd2='''insert into transorg_DW.dbo.Fact_Suborder_Journey_inc (Fact_Suborder_Journey_Key,SubOrderID,SubOrderDate,SubOrderDate_INT,DIM_Item_Key,DIM_Product_Key,DIM_Category_Key ,Dim_Customer_Key,DIM_DTH_Key,Dim_Acquisition_Channel_Key,DIM_Geography_Key,DIM_LeadSource_Key,Dim_MarketPlace_Key,Dim_SalesBucket_Key ,Cancelled_DATE,MarkedShipped_DATE,MarkedReshipped_DATE,MarkedSalesReturn_DATE,SalesReturn_DATE,MarkedDelivered_DATE,Margin ,GSV,ItemPrice,PayablePrice,PaidByGCAmt,PaidByDC,IsValid,IsSuperBooked,IsSameDayCancellation,IsMISvalid,IsCancelled,IsShipped ,IsMarkedShipped,IsLostInTransit,IsNDR,IsRTO,IsORTV,IsDelivered,IsMarkedDelivered,IsExecuted,IsReturnRequested,IsProvisionalExecuted ,IsSalesReturnDone,IsBuyOrSell,IsSellerShipper,IsNCAwarded,IsMailSentOnStatusChange,IsAdvPayment,IsAdvPaymentPerc,IsPreorderable,IsRenewType ,IsDeleted,IsCallCenter,OrderType,IS_TV_PRODUCT,MarketPlaceId,Dim_DSS_Key,Dim_OrderShowTag_Key,Dim_Suborder_Delivery_Mode_Key,Dim_CustomerMDM_Key ,Dim_NewVsRepeatCustomerMDM_Key) select Fact_Suborder_Journey_Key,SubOrderID,SubOrderDate,SubOrderDate_INT,DIM_Item_Key,DIM_Product_Key,DIM_Category_Key ,Dim_Customer_Key,DIM_DTH_Key,Dim_Acquisition_Channel_Key,DIM_Geography_Key,DIM_LeadSource_Key,Dim_MarketPlace_Key,Dim_SalesBucket_Key ,Cancelled_DATE,MarkedShipped_DATE,MarkedReshipped_DATE,MarkedSalesReturn_DATE,SalesReturn_DATE,MarkedDelivered_DATE,Margin ,GSV,ItemPrice,PayablePrice,PaidByGCAmt,PaidByDC,IsValid,IsSuperBooked,IsSameDayCancellation,IsMISvalid,IsCancelled,IsShipped ,IsMarkedShipped,IsLostInTransit,IsNDR,IsRTO,IsORTV,IsDelivered,IsMarkedDelivered,IsExecuted,IsReturnRequested,IsProvisionalExecuted ,IsSalesReturnDone,IsBuyOrSell,IsSellerShipper,IsNCAwarded,IsMailSentOnStatusChange,IsAdvPayment,IsAdvPaymentPerc,IsPreorderable,IsRenewType ,IsDeleted,IsCallCenter,OrderType,IS_TV_PRODUCT,MarketPlaceId,Dim_DSS_Key,Dim_OrderShowTag_Key,Dim_Suborder_Delivery_Mode_Key,Dim_CustomerMDM_Key ,Dim_NewVsRepeatCustomerMDM_Key from Fact_Suborder_Journey where SubOrderDate between '2016-06-14 00:00:00' and '2016-06-15 23:59:59';''' res1=cur1.execute(db_cmd2) cur1.commit() cur1.close() cur2.commit() cur2.close() #print("table created successfully")

output of db_cmd1 is getting but i want to run db_cmd2 query i am getting error .
Error is :-
SQL Server]Invalid object name 'HS18_DW.dbo.fact_suborder_journey'. (208) (SQLExecDirectW)")

The question is largely unreadable mainly due to how you have presented it, so I'd look at improving it if I were you. Off the top of my head I guess you want to be looking at setting up a linked server.
– Tanner
Aug 30 at 12:29

No i am not looking for setting up the linked server. i just want to make script to transfer the data from one server to another using python.
– s.kumar
Sep 3 at 5:05

1 Answer
1

If any one wants to transfer the incremental data from one sql server to another sql server
use this query and modified as per your requirement:-

i have the 266 columns that why i use %s * 266 you can use as per your table defination.

challenge 1 i had to insert the date column because in date field it should be in single quotes ' ' and string data also in single quotes.
challenge 2 auto insert the %s, i dont want to write the %s 266 times

import os from os import listdir from os.path import isfile, join import pyodbc myConn1 = pyodbc.connect( driver="SQL Server",server="xxx.xxx.xxx.xxx",database="xxx",user="xxx",password="xxx" ) #myConn2 = pyodbc.connect( driver="SQL Server",server="xxx.xxx.xxx.xxx",database="xxx",user="xxx",password="xxx" ) cur1=myConn1.cursor() #cur2=myConn2.cursor() print('Cursor established') db_cmd = '''SELECT Fact_Suborder_Journey_Key,SubOrderID,OrderID,SubOrderDate,SubOrderDate_INT,OrderDate,OrderDate_INT,DIM_Item_Key ,DIM_Product_Key,DIM_Category_Key,Dim_Customer_Key,DIM_DTH_Key,DIM_Vendor_Key,DIM_Warehouse_Key,Dim_Acquisition_Channel_Key,DIM_Geography_Key ,DIM_PaymentMode_Key,DIM_Courier_Key,DIM_LeadSource_Key,Dim_MarketPlace_Key,Dim_Dnis_Key,Dim_SalesBucket_Key ,PaymentReceived_DATE,Cancelled_DATE,CanceledForRefund_DATE,Verified_DATE,OrderShipped_DATE,MarkedShipped_DATE,OrderReshipped_DATE ,MarkedReshipped_DATE,MarkedSalesReturn_DATE,SalesReturn_DATE,OUPWaitingForGatewayResponse_DATE,OrderUnderProcessCOD_DATE,RTO_DATE ,OrderDelivered_DATE,MarkedDelivered_DATE,OrderReturnToVendor_DATE,PaymentDeclined_DATE,LostInTransit_DATE,ORTV_Adjustment_DATE,RTO_Delivered_DATE ,DeliveryAttemptFailed_DATE,ReplacementRequested_DATE,ReturnByCustomerRequested_DATE,Quantity,MRP,MSP ,Margin,GSV,ItemPrice,PriceAfterProductDis,PriceAfterSellerDis,PriceAfterAllDis,PriceAfterMembershipDis,PriceAfterPaymentDis,PayModeNCharge ,AddOnNCharge,ShipNcharge,GiftNcharge,PayablePrice,AdvPaymentAmt,NCAmount,PaidByGCAmt,PaidByDC,RBC_Cost,IsValid,IsSuperBooked,IsSameDayCancellation ,IsMISvalid,IsCancelled,IsShipped,IsMarkedShipped,IsLostInTransit,IsNDR,IsRTO,IsORTV,IsDelivered,IsMarkedDelivered,IsExecuted,IsReturnRequested ,IsProvisionalExecuted,IsSalesReturnDone,IsBuyOrSell,IsSellerShipper,IsNCAwarded,IsMailSentOnStatusChange,IsAdvPayment,IsAdvPaymentPerc,IsPreorderable ,IsRenewType,IsDeleted,IsCallCenter,OrderType,SubOrderProductItemID,CallSequenceNumber,AWB_Number_Forward,AWB_Number_Reverse,PaymentModeID,SubOrderStateID ,BranchCode,DTH_ID,OrderReason,DNIS_TYPE,LeadSource_SalesGroup,IS_TV_PRODUCT,MarketPlaceId,Dw_Update_Dt,Dw_Insert_Dt,Reporting_Date,Verified_By,Cancelled_or_CFR_By ,IsSystemVerified,IsSystemCancelled,DIM_SalesBucketProposed_Key,Executed_DATE,ProvisionalExecuted_DATE,OldSuborderId,OldSuborderDate,SubOrderProductPickupID ,SubOrderSellerID,SuborderCourierId,SuborderWarehouseId,OrderReturnToVendor_Actual_DATE,SubOrderReceiverCityID,SubOrderReceiverZip,Dim_CategoryMapping_Key ,CategoryName_L1,CategoryName_L2,PromisedDelivery_Date,Vendor_SLA,Courier_SLA,BookedToShipped_days,ShippedToDelivered_days,OrderCallerNo,HandedToCourier_DATE ,IsHandedToCourier,Dim_OrderDispatchType_Key,CargoReady_DATE,BookedToVerified_Minutes,VerifiedToPickList_Minutes,PickListToCargoReady_Minutes,CargoReadyToGatepass_Minutes ,ShippedToFirstAttempt_Minutes,FirstAttemptToDelivery_Minutes,PicklistGenerated_DATE,GatepassToShipped_Minutes,Canceled_Reason_Key,Canceled_Refund_Reason_Key,Refund_Reason_Key ,RTO_Reason_Key,Sales_Return_Reason_Key,RBC_Reason_Key,RFR_Reason_Key,CancelReason_Instruction_Key,CancelRefundReason_Instruction_Key,Dim_NewVsRepeatCustomer_Key,SameMonthRepeatedAfterDayGap ,Cancelled_DATE_INT,MarkedShipped_DATE_INT,MarkedDelivered_DATE_INT,OrderReturnToVendor_Actual_DATE_INT,MarkedSalesReturn_DATE_INT,RTO_DATE_INT,ReturnByCustomerRequested_DATE_INT ,SubOrder_TIME_ID,BookedToDoor_Hours,Dim_DSS_Key,Dim_OrderShowTag_Key,Dim_PriceBand_Key,DisputeRaised_DATE_INT,DisputeRejected_DATE_INT,NEFTFailed_DATE_INT ,NEFTInitiated_DATE_INT,RefundChequeDispatched_DATE_INT,RefundChequePrepared_DATE_INT,Refunded_DATE_INT,ReplacementInitiated_DATE_INT,ReversePickupDelivered_DATE_INT ,ReversePickupDone_DATE_INT,ReversePickupFailed_DATE_INT,ReversePickupRequiredForRefund_DATE_INT,FirstAttempt_DATE,SecondAttempt_DATE,ThirdAttempt_DATE,FourthAttempt_DATE ,FifthAttempt_DATE,SixthAttempt_DATE,FirstAttempt_ToStatusId,SecondAttempt_ToStatusId,ThirdAttempt_ToStatusId,FourthAttempt_ToStatusId,FifthAttempt_ToStatusId ,SixthAttempt_ToStatusId,IsVendorSLAbreached,IsCourierSLAbreached,NumberOfAttemptsToDeliver,NumberOfCourierAttempts,GatePassed_DATE,IsGatePassed,UserLogin,OrderCallRoute ,TSO_Type,IsReplacement,IsMarkedSaleReturn,IsRtoDelivered,Dim_BookedToShippedDaysBucket_Key,Dim_ShippedToDeliveredDaysBucket_Key,BookedToNOW_Days,ShippedToNOW_Days ,VerifiedToNOW_Days,GatePassedToNOW_Days,Dim_BookedToPendingShipmentDaysBucket_Key,Dim_ShippedToPendingDeliveryDaysBucket_Key,Dim_BookedToPendingVerifiedDaysBucket_Key ,Dim_VerifiedToPendingGatepassedDaysBucket_Key,Dim_GatepassedToPendingShipmentDaysBucket_Key,PaidByShippingPassAmount,CouponGroup,Dim_Suborder_Delivery_Mode_Key ,ORV_STATUS_DATE_INT,Dim_NDR_Reason_Key,Dim_Site_key,Dim_BookedToPendingShipmentAgeingBucket_Key,Dim_ShippedToPendingDeliveryAgeingBucket_Key,Dim_Offer_Key ,Dim_Deal_Key,Dim_Affiliate_key,First_DeliveryAttemptFailed_Courier_DATE,Marked_RTO_Delivered_DATE,RefundCouponDiscount,IsRefundCouponDiscount,ReverseLITDate ,ReverseLITDate_INT,IsReverseLIT,RefundRemark,Dim_CustomerMDM_Key,Payment_GatewayName,Payment_GatewayMID,Dim_NewVsRepeatCustomerMDM_Key,LITRemark,FWDLITRemark ,ReverseLITRemark,FWDLITRemark_MarkedBy,ReverseLITRemark_MarkedBy,ChequeNumber,ChequeAmount,ChequeBankName,ChequeDate,IsChequeIssued,RefundTransactionId,Refund_Amount ,ParentSuborder_GSV,ParentSuborder_ShippingCharge,ParentSuborder_Margin,ParentCouponCategory_Id,Colllected_ShippingCharge,ParentSuborder_Colllected_ShippingCharge ,Dim_Cancellation_Channel_Key,IsSCRefunded,Dim_RBC_Reason_Key,SubOrderSTID,Is_PrePaid_PayMode,OUP_PrepaidSMSDate,DIM_Actual_PaymentMode_Key,dim_brand_key FROM table_name WHERE SubOrderDate between convert(date,'2018-07-25 21:39:58') and convert(date,'2018-07-28 21:39:58');''' res = cur1.execute(db_cmd) res1=res.fetchall() #print (res) import datetime for r in res1: print(list(r)) data_list = list(r) for ind, val in enumerate(data_list): if val is None: data_list[ind] = 'NULL' elif isinstance(val, datetime.datetime): data_list[ind] = "'"+str(val.replace(microsecond=0))+"'" elif isinstance(val, str): data_list[ind] = "'"+str(val)+"'" print(tuple(data_list)) str_concat = '%s,'*266 form_str = '('+str_concat.strip(',')+')' val = form_str%tuple(data_list) db_cmd2='''insert into table_name_ofinsert(Fact_Suborder_Journey_Key,SubOrderID,OrderID,SubOrderDate,SubOrderDate_INT,OrderDate,OrderDate_INT,DIM_Item_Key ,DIM_Product_Key,DIM_Category_Key,Dim_Customer_Key,DIM_DTH_Key,DIM_Vendor_Key,DIM_Warehouse_Key,Dim_Acquisition_Channel_Key,DIM_Geography_Key ,DIM_PaymentMode_Key,DIM_Courier_Key,DIM_LeadSource_Key,Dim_MarketPlace_Key,Dim_Dnis_Key,Dim_SalesBucket_Key ,PaymentReceived_DATE,Cancelled_DATE,CanceledForRefund_DATE,Verified_DATE,OrderShipped_DATE,MarkedShipped_DATE,OrderReshipped_DATE ,MarkedReshipped_DATE,MarkedSalesReturn_DATE,SalesReturn_DATE,OUPWaitingForGatewayResponse_DATE,OrderUnderProcessCOD_DATE,RTO_DATE ,OrderDelivered_DATE,MarkedDelivered_DATE,OrderReturnToVendor_DATE,PaymentDeclined_DATE,LostInTransit_DATE,ORTV_Adjustment_DATE,RTO_Delivered_DATE ,DeliveryAttemptFailed_DATE,ReplacementRequested_DATE,ReturnByCustomerRequested_DATE,Quantity,MRP,MSP ,Margin,GSV,ItemPrice,PriceAfterProductDis,PriceAfterSellerDis,PriceAfterAllDis,PriceAfterMembershipDis,PriceAfterPaymentDis,PayModeNCharge ,AddOnNCharge,ShipNcharge,GiftNcharge,PayablePrice,AdvPaymentAmt,NCAmount,PaidByGCAmt,PaidByDC,RBC_Cost,IsValid,IsSuperBooked,IsSameDayCancellation ,IsMISvalid,IsCancelled,IsShipped,IsMarkedShipped,IsLostInTransit,IsNDR,IsRTO,IsORTV,IsDelivered,IsMarkedDelivered,IsExecuted,IsReturnRequested ,IsProvisionalExecuted,IsSalesReturnDone,IsBuyOrSell,IsSellerShipper,IsNCAwarded,IsMailSentOnStatusChange,IsAdvPayment,IsAdvPaymentPerc,IsPreorderable ,IsRenewType,IsDeleted,IsCallCenter,OrderType,SubOrderProductItemID,CallSequenceNumber,AWB_Number_Forward,AWB_Number_Reverse,PaymentModeID,SubOrderStateID ,BranchCode,DTH_ID,OrderReason,DNIS_TYPE,LeadSource_SalesGroup,IS_TV_PRODUCT,MarketPlaceId,Dw_Update_Dt,Dw_Insert_Dt,Reporting_Date,Verified_By,Cancelled_or_CFR_By ,IsSystemVerified,IsSystemCancelled,DIM_SalesBucketProposed_Key,Executed_DATE,ProvisionalExecuted_DATE,OldSuborderId,OldSuborderDate,SubOrderProductPickupID ,SubOrderSellerID,SuborderCourierId,SuborderWarehouseId,OrderReturnToVendor_Actual_DATE,SubOrderReceiverCityID,SubOrderReceiverZip,Dim_CategoryMapping_Key ,CategoryName_L1,CategoryName_L2,PromisedDelivery_Date,Vendor_SLA,Courier_SLA,BookedToShipped_days,ShippedToDelivered_days,OrderCallerNo,HandedToCourier_DATE ,IsHandedToCourier,Dim_OrderDispatchType_Key,CargoReady_DATE,BookedToVerified_Minutes,VerifiedToPickList_Minutes,PickListToCargoReady_Minutes,CargoReadyToGatepass_Minutes ,ShippedToFirstAttempt_Minutes,FirstAttemptToDelivery_Minutes,PicklistGenerated_DATE,GatepassToShipped_Minutes,Canceled_Reason_Key,Canceled_Refund_Reason_Key,Refund_Reason_Key ,RTO_Reason_Key,Sales_Return_Reason_Key,RBC_Reason_Key,RFR_Reason_Key,CancelReason_Instruction_Key,CancelRefundReason_Instruction_Key,Dim_NewVsRepeatCustomer_Key,SameMonthRepeatedAfterDayGap ,Cancelled_DATE_INT,MarkedShipped_DATE_INT,MarkedDelivered_DATE_INT,OrderReturnToVendor_Actual_DATE_INT,MarkedSalesReturn_DATE_INT,RTO_DATE_INT,ReturnByCustomerRequested_DATE_INT ,SubOrder_TIME_ID,BookedToDoor_Hours,Dim_DSS_Key,Dim_OrderShowTag_Key,Dim_PriceBand_Key,DisputeRaised_DATE_INT,DisputeRejected_DATE_INT,NEFTFailed_DATE_INT ,NEFTInitiated_DATE_INT,RefundChequeDispatched_DATE_INT,RefundChequePrepared_DATE_INT,Refunded_DATE_INT,ReplacementInitiated_DATE_INT,ReversePickupDelivered_DATE_INT ,ReversePickupDone_DATE_INT,ReversePickupFailed_DATE_INT,ReversePickupRequiredForRefund_DATE_INT,FirstAttempt_DATE,SecondAttempt_DATE,ThirdAttempt_DATE,FourthAttempt_DATE ,FifthAttempt_DATE,SixthAttempt_DATE,FirstAttempt_ToStatusId,SecondAttempt_ToStatusId,ThirdAttempt_ToStatusId,FourthAttempt_ToStatusId,FifthAttempt_ToStatusId ,SixthAttempt_ToStatusId,IsVendorSLAbreached,IsCourierSLAbreached,NumberOfAttemptsToDeliver,NumberOfCourierAttempts,GatePassed_DATE,IsGatePassed,UserLogin,OrderCallRoute ,TSO_Type,IsReplacement,IsMarkedSaleReturn,IsRtoDelivered,Dim_BookedToShippedDaysBucket_Key,Dim_ShippedToDeliveredDaysBucket_Key,BookedToNOW_Days,ShippedToNOW_Days ,VerifiedToNOW_Days,GatePassedToNOW_Days,Dim_BookedToPendingShipmentDaysBucket_Key,Dim_ShippedToPendingDeliveryDaysBucket_Key,Dim_BookedToPendingVerifiedDaysBucket_Key ,Dim_VerifiedToPendingGatepassedDaysBucket_Key,Dim_GatepassedToPendingShipmentDaysBucket_Key,PaidByShippingPassAmount,CouponGroup,Dim_Suborder_Delivery_Mode_Key ,ORV_STATUS_DATE_INT,Dim_NDR_Reason_Key,Dim_Site_key,Dim_BookedToPendingShipmentAgeingBucket_Key,Dim_ShippedToPendingDeliveryAgeingBucket_Key,Dim_Offer_Key ,Dim_Deal_Key,Dim_Affiliate_key,First_DeliveryAttemptFailed_Courier_DATE,Marked_RTO_Delivered_DATE,RefundCouponDiscount,IsRefundCouponDiscount,ReverseLITDate ,ReverseLITDate_INT,IsReverseLIT,RefundRemark,Dim_CustomerMDM_Key,Payment_GatewayName,Payment_GatewayMID,Dim_NewVsRepeatCustomerMDM_Key,LITRemark,FWDLITRemark ,ReverseLITRemark,FWDLITRemark_MarkedBy,ReverseLITRemark_MarkedBy,ChequeNumber,ChequeAmount,ChequeBankName,ChequeDate,IsChequeIssued,RefundTransactionId,Refund_Amount ,ParentSuborder_GSV,ParentSuborder_ShippingCharge,ParentSuborder_Margin,ParentCouponCategory_Id,Colllected_ShippingCharge,ParentSuborder_Colllected_ShippingCharge ,Dim_Cancellation_Channel_Key,IsSCRefunded,Dim_RBC_Reason_Key,SubOrderSTID,Is_PrePaid_PayMode,OUP_PrepaidSMSDate,DIM_Actual_PaymentMode_Key,dim_brand_key) values'''+val #print (db_cmd2) res2=cur1.execute(db_cmd2) cur1.commit() #print (db_cmd2) cur1.commit() cur2.commit() cur2.close() cur1.close() #print("table created successfully")

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

搜尋此網誌

Dfyjkt