2022
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
USE [DBSyncDB] CREATE TABLE [dbo].[account]( [id] [varchar](20) NULL,[Name] [varchar](50) NULL,[firstname] [varchar](50) NULL,[lastname] [varchar](50) NULL, [phone] [nchar](20) NULL,[fax] [nchar](20) NULL,[billingstreet] [varchar](200) NULL,[billingcity] [varchar](50) NULL, [billingstate] [varchar](50) NULL,[billingcountry] [varchar](50) NULL,[billingcode] [varchar](50) NULL,[company] [nchar](100) NULL ) ON [PRIMARY] GO USE [DBSyncDB] CREATE TABLE [dbo].[invoice]( [invoice_no] [nchar](10) NULL,[customerid] [nchar](100) NULL,[item] [nchar](100) NULL,[itemdesc] [nchar](100) NULL, [quantity] [nchar](10) NULL,[uom] [nchar](50) NULL,[salesprice] [nchar](10) NULL,[total] [nchar](10) NULL, [billaddress] [nchar](100) NULL,[billingcity] [nchar](100) NULL,[billingstate] [nchar](100) NULL, [billingpostalcode] [nchar](10) NULL,[billingcountry] [nchar](100) NULL,[date] [nchar](50) NULL ) ON [PRIMARY] GO |
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
CREATE TABLE `dbsyncdb`.`account` (`id` VARCHAR(20) DEFAULT NULL,`Name` VARCHAR(50) DEFAULT NULL,`firstname` VARCHAR(50) DEFAULT NULL, `lastname` VARCHAR(50) DEFAULT NULL,`phone` VARCHAR(20) DEFAULT NULL,`fax` VARCHAR(20) DEFAULT NULL, `billingstreet` VARCHAR(200) DEFAULT NULL,`billingcity` VARCHAR(50) DEFAULT NULL,`billingstate` VARCHAR(50) DEFAULT NULL, `billingcountry` VARCHAR(50) DEFAULT NULL,`billingcode` VARCHAR(50) DEFAULT NULL,`company` VARCHAR(100) DEFAULT NULL ) ENGINE=INNODB DEFAULT CHARSET=utf8; /*Table structure for table `invoice` */ CREATE TABLE `dbsyncdb`.`invoice` (`invoice_no` CHAR(10) DEFAULT NULL,`customerid` CHAR(100) DEFAULT NULL,`item` CHAR(100) DEFAULT NULL, `itemdesc` CHAR(100) DEFAULT NULL,`quantity` CHAR(10) DEFAULT NULL,`uom` CHAR(50) DEFAULT NULL,`salesprice` CHAR(10) DEFAULT NULL, `total` CHAR(10) DEFAULT NULL,`billaddress` CHAR(100) DEFAULT NULL,`billingcity` CHAR(100) DEFAULT NULL, `billingstate` CHAR(100) DEFAULT NULL,`billingpostalcode` CHAR(10) DEFAULT NULL,`billingcountry` CHAR(100) DEFAULT NULL, `date` CHAR(50) DEFAULT NULL) ENGINE=INNODB DEFAULT CHARSET=utf8; |
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
CREATE TABLE "SYS"."ACCOUNT" ("id" VARCHAR(20 BYTE) DEFAULT NULL,"Name" VARCHAR(50 BYTE) DEFAULT NULL,"firstname" VARCHAR(50 BYTE) DEFAULT NULL, "lastname" VARCHAR(50 BYTE) DEFAULT NULL,"phone" VARCHAR(20 BYTE) DEFAULT NULL,"fax" VARCHAR(20 BYTE) DEFAULT NULL, "billingstreet" VARCHAR(200 BYTE) DEFAULT NULL,"billingcity" VARCHAR(50 BYTE) DEFAULT NULL,"billingstate" VARCHAR(50 BYTE) DEFAULT NULL, "billingcountry" VARCHAR(50 BYTE) DEFAULT NULL,"billingcode" VARCHAR(50 BYTE) DEFAULT NULL, "company" VARCHAR(100 BYTE) DEFAULT NULL ); /*Table structure for table `invoice` */ CREATE TABLE "SYS"."INVOICE" ("invoice_no" CHAR(10 BYTE) DEFAULT NULL,"customerid" CHAR(100 BYTE) DEFAULT NULL,"item" CHAR(100 BYTE) DEFAULT NULL, "itemdesc" CHAR(100 BYTE) DEFAULT NULL,"quantity" CHAR(10 BYTE) DEFAULT NULL,"uom" CHAR(50 BYTE) DEFAULT NULL,"salesprice" CHAR(10 BYTE) DEFAULT NULL, "total" CHAR(10 BYTE) DEFAULT NULL,"billaddress" CHAR(100) DEFAULT NULL,"billingcity" CHAR(100) DEFAULT NULL, "billingstate" CHAR(100 BYTE) DEFAULT NULL,"billingpostalcode" CHAR(10 BYTE) DEFAULT NULL,"billingcountry" CHAR(100 BYTE) DEFAULT NULL, "date" CHAR(50 BYTE) DEFAULT NULL); |
Note : Make sure both the tables have sample data in them before they can be mapped within DBSync.
Note : If you are using a different Database server, you can enter the connection string for the same as shown here.
Wiki Markup |
---|
Click on *\[X\]* icon on *Start_state* to de-link *Start_State* to *End_state.* |
Note: Always establish connection between states in the sequence of Reader: — Map: — Writer:
Code Block |
---|
select * from account |
Table : CustomerAddRq
maxbatchsize : 1
writetype_ _ : insert
autocreate : True
Target | = | Source |
---|---|---|
CustomerAddRq/CustomerAdd/BillAddress/Addr1 | = | VALUE("billingstreet") |
CustomerAddRq/CustomerAdd/BillAddress/City | = | VALUE("billingcity") |
CustomerAddRq/CustomerAdd/BillAddress/Country | = | VALUE("billingcountry") |
CustomerAddRq/CustomerAdd/BillAddress/PostalCode | = | VALUE("billingcode") |
CustomerAddRq/CustomerAdd/BillAddress/State | = | VALUE("billingstate") |
CustomerAddRq/CustomerAdd/CompanyName | = | VALUE("company") |
CustomerAddRq/CustomerAdd/Fax | = | VALUE("fax") |
CustomerAddRq/CustomerAdd/FirstName | = | VALUE("firstname") |
CustomerAddRq/CustomerAdd/IsActive | = | "true" |
CustomerAddRq/CustomerAdd/LastName | = | VALUE("lastname") |
CustomerAddRq/CustomerAdd/Name | = | VALUE("Name") |
CustomerAddRq/CustomerAdd/Phone | = | VALUE("phone") |
VALIDATEROW | = | "true" |
Code Block |
---|
select invoice_no,invoice_no as invoicenumber,customerid,item,itemdesc,quantity,uom, salesprice,total,billaddress,billingcity,billingstate,billingpostalcode,billingcountry, convert(varchar,cast(date as datetime),126) as 'date' from invoice |
Code Block |
---|
customerid,billaddress,billingcity,billingstate,billingpostalcode,billingcountry,date, invoice_no,[item,itemdesc,quantity,salesprice] |
Code Block |
---|
Table : InvoiceAddRq
maxbatchsize : 1
writetype_ _ : insert
autocreate : True
Target | = | Source |
---|---|---|
InvoiceAddRq/InvoiceAdd/BillAddress/Addr1 | = | VALUE("billingaddress") |
InvoiceAddRq/InvoiceAdd/BillAddress/City | = | VALUE("billingcity") |
InvoiceAddRq/InvoiceAdd/BillAddress/Country | = | VALUE("billingcountry") |
InvoiceAddRq/InvoiceAdd/BillAddress/PostalCode | = | VALUE("billingpostalcode") |
InvoiceAddRq/InvoiceAdd/BillAddress/State | = | VALUE("billingpostalcode") |
InvoiceAddRq/InvoiceAdd/CustomerRef/FullName | = | VALUE("customerid") |
InvoiceAddRq/InvoiceAdd/InvoiceLineAdd | loop | "invoice_no/list" |
InvoiceAddRq/InvoiceAdd/InvoiceLineAdd/Desc | = | VALUE("itemdesc") |
InvoiceAddRq/InvoiceAdd/InvoiceLineAdd/ItemRef/FullName | = | VALUE("item") |
InvoiceAddRq/InvoiceAdd/InvoiceLineAdd/Quantity | = | VALUE("quantity") |
InvoiceAddRq/InvoiceAdd/InvoiceLineAdd/Rate | = | VALUE("salesprice") |
InvoiceAddRq/InvoiceAdd/IsPending | = | "true" |
InvoiceAddRq/InvoiceAdd/TxnDate | = | VALUE("date") |
VALIDATEROW | = | "true" |
At this point, you have configured your DBSync profile to integrate both the Accounts and Invoice tables that were created in your SQL Server Database. A similar procedure can be followed for other databases like MYSQL,Oracle,DB2 etc. The only thing that would be changing within DBSync would be the connection strings for the respective databases.