CRM365 on premises - I want to insert Industry type for few accounts whose name content specific word using sql

(0) Share

Report

Posted on by MKapde

I created entity called industry as each account types are more than one.

Few accounts name contain specific word e.g. Finance. I want to insert industry type Finance using SQL query.

Three tables (1) AccountBase- Accountid, Name (2) Industry - Industryid, name (3) new_Account_Insdutry - accounted, industryid.

I need sql query to insert rows in to new_account_industry table those raws who met with where condition.

*This post is locked for comments

All responses (7)

Answers (0)

Suggested answer

Nick.Doelman 1,947 Most Valuable Professional on at

Like (0)

Report

RE: CRM365 on premises - I want to insert Industry type for few accounts whose name content specific word using sql

This is unsupported and while you can run some basic SQL queries against CRM data, this is more dangerous because it will create records and things like GUIDs, links may not be properly created.

From what I understand, you want to create N:N links. Tools already mentioned in this thread would be good options.

A tool I have used in the past is the in the XrmToolBox (A MUST HAVE for using Dynamics CRM).

The particular tool is the "Manage-NN-relationships", details here:

github.com/.../Manage-NN-relationships

You can then setup a file to update your relationships (Account to Industry) and it will update in a supported, clean manner.

Hope this helps.

Cheers

Nick
MKapde 15 on at

Like (0)

Report

RE: CRM365 on premises - I want to insert Industry type for few accounts whose name content specific word using sql

Thanks Aric.
MKapde 15 on at

Like (0)

Report

RE: CRM365 on premises - I want to insert Industry type for few accounts whose name content specific word using sql

Mahender,

Thanks for reply. I am using scribe. Could you please elaborate more how can I use with scribe.
MKapde 15 on at

Like (0)

Report

RE: CRM365 on premises - I want to insert Industry type for few accounts whose name content specific word using sql

Hi Ravi,

Thanks for the reply.

My challenge is bit different then simple data import.

Goal: Search Accounts name content word 'Finance', Add industry type 'Finance' if it's not there.

(1) My all Accounts already have their industry types.

(2) I want to search all Accounts name content word 'Finance'.

(3) Search those accounts in many-to-many relationship table. Who do not have industry type 'Finance'

(4) Insert records for only those accounts. I don't want duplicates industry types on account.

Do you have any suggestions to accomplish above ?? then please guide me.
Suggested answer

RaviKashyap 55,410 Moderator on at

Like (0)

Report

RE: CRM365 on premises - I want to insert Industry type for few accounts whose name content specific word using sql

Hi,

As mentioned above, it is unsupported and may cause some other unknown issues. Why are you looking only for SQl query? As this seems pretty straight forward insert, you could download the excel data template for Industry entity, populate it withj the required data values and then import it back in CRM.

You can refer the below video on excel data import (though it is for 2011 but still valid)

www.youtube.com/watch

Hope this helps.
Suggested answer

Mahendar Pal 45,095 on at

Like (0)

Report

RE: CRM365 on premises - I want to insert Industry type for few accounts whose name content specific word using sql

Hi,

Technically it's possible, but you need to be very careful as it can lead to data inconsistency and off course as said earlier this is totally unsupported, so it will be better to consider other options like import export/ondemand workflow (if you can query specific accounts) or custom tools like SSIS toolkit or scribe.
Suggested answer

Aric Levin - MVP 30,188 Moderator on at

Like (1)

Report

RE: CRM365 on premises - I want to insert Industry type for few accounts whose name content specific word using sql

This is unsupported.

You should only insert data using the SDK/Web Api/Data Import Tool or something other method that connects to CRM directly.

SQL should basically used to read data using FilteredViews (and possibly some performance enhancements where necessary), but not for manipulating the data.

Hope this helps.