Export & Import Data Into Microsoft Sql Using Celigo

Microsoft Sql Server : Its a relational database management system developed by Microsoft.It is a software product with the primary functions of storing and retrieving data as requested by other software applications.

Export Data From Microsoft Sql Server

• From the Resource menu, select Exports to open the list of exports of account.OR, at the upper right of exports page click + create export.

• For creating an export, a new window will pop up, then select the application  Microsoft SQL.

• A new window will open then select connection. If the connection already exist.If the connection does not exist,then create a new connection by clicking the + icon on the right side of the connection.

• Give the meaning full name to Name field (Name field  is required, Description field is optional)

• After that click the Next button.

A new window will pop up.

• In the SQL query field, write the SQL statement that fetches the data required.(in this window the query is to fetch  all details of the DBO.ITEMS table.).
• Select Export type:

• All -to export all data.
• Delta   -to export data that has changed since the last time data flow was run.
• Once   – to export only data has not already been exported.
• Test – to export only one record.

• When we click  the Preview button it will display the preview data.
• Click the save & close button.

Import Data  to Microsoft Sql Server

• From the Tools menu,select Flow Builder. For destination application, click Microsoft SQL.

OR

• From the Resource menu, select Imports to open the list of Imports of account.OR, at the upper right of imports page click + create import.

• For creating the import, a new window will pop up.Then select the application  Microsoft SQL.

• A new window will open  then select the connection. If the connection already exist.If the connection does not exist,then create a new connection by clicking the + icon on the right side of the connection.
• Give the meaning full name to the Name field (Name field  is required, Description field is optional).
• After that click the Next button.

A new window appears.

One to Many : Where a parent record is being passed around in a flow, but need to access the child record contained within the parent  record.One to many is used in cases where you have a single record that internally needs to create multiple records.

• Query Type : Mainly 3 types,

• Insert   – Choose insert if you are importing records to the database.

• Ignore existing records : Ignore existing records and create new record.

• SQL query : Write the exact sql query to be sent to the database, this query can be static or dynamic, Which means handlebar templates are used to source the data.

 Example: 

Write the exact sql query on handlebar template, the Resources are available on the handlebars that’s coming from export or lookup.

When we click  the preview button it will display the preview of the query.

Click the save & close button.

• Update – Choose update if you are updating any existing records in the database.

• Ignore Missing records : When updating existing data,if it is possible for the data being imported to include stuff that does not exist in the import application you can use this yield to tell integrator.io to just ignore that missing data.

• SQL query : Write the exact sql query to be sent to the database, this query can be static or dynamic, Which means handlebar templates are used to source the data.

When we click the edit button on the right side of the sql query, a  new window will pop up that contains handlebar templates and source data.After editing the handlebar templates, click the preview button it will display preview of the query.

Click the save & close button.

• Insert or Update – If you want to import the records to the database dynamically, check whether a record exists in the database,if so that record will be updated,otherwise a record will be created.

• How would you like to identify existing records? : Choose the method that should be used to identify if a source record already exists in the destination application.

1. Records have a specific field populated – check if a specific field in the source record already has a value, and then if so, the source record will be considered an existing record.

• Which field? : Enter the path to the field in the source record that is used to identify existing records.

• SQL query (for inserts) : Write the exact sql query to be sent to the database, this query can be static or dynamic, Which means handlebar templates are used to source the data. Query is used for inserting.

• SQL query (for updates) : Write the exact sql query to be sent to the database, this query can be static or dynamic, Which means handlebar templates are used to source the data. Query is used for updating.

• Click save & close button.

2. Run a dynamic lookup –Lookups provide a way to dynamically search the destination application to find existing records.

• Lookup : Choose an existing lookup from the list or click + to define a new lookup.

• SQL query (for inserts) : Write the exact sql query to be sent to the database, this query can be static or dynamic, Which means handlebar templates are used to source the data. Query is used for inserting.

• SQL query (for updates) : Write the exact sql query to be sent to the database, this query can be static or dynamic, Which means handlebar templates are used to source the data. Query is used for updating.

• Click save & close button.