copy dynamics crm field data to another using sql query

(0) Share

Report

Posted on by Community Member

Hi guys,

I'm trying to copy dynamics crm field data ( for exemple data for telephone1 ) to another field (for exemple telephone2 using sql query , I've tried this query

"INSERT INTO AccountBase (Telephone2)
SELECT Telephone1
FROM AccountBase a;"

but It doesn't work and here is the error message:

Cannot insert the value NULL into column 'AccountId', table 'FormationInterne_MSCRM.dbo.AccountBase'; column does not allow nulls. INSERT fails.
The statement has been terminated.

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (3)

Verified answer

Nick.Doelman 1,947 Most Valuable Professional on at

Like (2)

Report

As Guido suggested, updating CRM data using SQL statements is unsupported and strictly frowned upon.

As mentioned, a workflow is your best option but is limited to 250 records at a time.

Now I am done repeating what he said and here is my contribution;

If you find that you need to update a large number of records (1000's) then you could export to excel, update using Excel formulas and then re-import.

I am not sure if I really should share the following, but the reason why your (unsupported) attempt failed is because the SQL syntax is wrong.

FYI - NOT FOR CRM DATABASES but strictly from a pure T-SQL learning perspective, the correct syntax would be:

Update <<tablename>>

Set <<field1>> = <<field2>>

From <<Database>>

As mentioned DO NOT run this on a CRM database. Use the other supported solutions mentioned.

Cheers

Nick

PS - Did I mention to NOT run that SQL Statement on a CRM database? Please don't.

Was this reply helpful? Yes No
Verified answer

Nithya Gopinath 17,078 on at

Like (1)

Report

Hi,

As told by Guido and Nick, SQL query is not supported. Instead of this, you have two ways to copy one field to another field.

1. You could run a workflow in the background.

If the telephone1 contains data, then update record with telephone2 = telephone1

https://community.dynamics.com/crm/f/117/t/103874

2. You could export the data to excel and create a new column with the name of telephone2 and copy the telephone1 column to telephone2 in excel. Then import the excel sheet as csv or xml.

https://community.dynamics.com/crm/f/117/t/154830

http://www.powerobjects.com/2011/09/27/mass-editing-dynamics-crm-2011-records-using-microsoft-excel/

Hope this helps you.

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (1)

Report

Update Account

set telephone2 = telephone1

Unsupported but will work

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

A big thank to you all , your answers were very helpful to me , done with the workflow ,the excel export , modify and re-import, and for the sql update try too (just tried the sql query).

Was this reply helpful? Yes No
Suggested answer

tw0sh3ds 5,600 on at

Like (1)

Report

Hi,

Really I don't understand why everyone are so strict about updating column value using T-SQL. Inserting data, changing database schema, adding your own columns, triggers, SQL jobs etc. is unsupported. But if I have text field on on entity and want to copy its value to another column? Give me a break guys, I work as a CRM support for dozens of projects since almost 4 years and a lot of my work requires some SQL data updates, because otherwise I would have to spent days writing data-update applications or running worklfows... And if you have 100 millions of records to update, you would still recommend exporting to excel or running a workflow? Your suggestions are fine with small or middle-sized CRMs, but sometimes it's not possible if you have to do data migration for example during weekend for big CRM with millions of records.

Updating value of a COLUMN in SQL is the only operation that you can do in CRM directly in SQL (apart from getting data of course). Just remember it's only this operation, nothing else and you should not touch any system columns (also you should use FilteredViews, not directly Tables in SQL)

Was this reply helpful? Yes No
Nick.Doelman 1,947 Most Valuable Professional on at

Like (0)

Report

Its not to say that I haven't done it myself (SQL updates), I might have even once added a sql trigger in lieu of a plug-in (as a temporary solution) but it's a slippery slope. I have also cut wood without safety glasses and mowed the lawn in flipflops.

No offense to EmmaJ, but the initial syntax was pretty off and someone learning SQL could potentially do a lot of damage. All it takes is an mistype and an update to a GUID column instead of a phone number column and then you are in a bit of fix.

I have been doing this for 20 years, in both CRM and Great Plains and more than once have had that conversation where someone ran some SQL queries that went sideways and needed a bailout and we had to revert to a backup thus losing time.

SQL updates are way faster, and if someone is comfortable then it will work, and a backup should always be taken immediately before, but sometimes doing it the "right" way may take longer but save some headaches.

Safety first makes you last :)

Thanks for spicing up this thread a bit!

Was this reply helpful? Yes No
tw0sh3ds 5,600 on at

Like (0)

Report

@Guido

"suggest it on a public forum to a user that doesn't know neither the difference between INSERT and UPDATE commands."

ok maybe you are right. I just wanted to point out that if you understand how databases work, there is no difference between CRM DB and any other DB. So if you never worked with CRM before, but you understand that changing DB schema or adding some additional configurations may break the application which is using the database, there is no problem in updating column Value even using SQL. But in general you are right, if you will not tell people that SQL is off-limits, they will have some dummy ideas at some point.

@Nick

Again - you are right that if you let people use SQL, they can break it if they don't know what they are doing. I was simply referring to the fact that there are some good SQL admin out there, who can understand how SQL works and how application consuming SQL can work and even without knowledge of CRM, they can perform some simple operation (like updating field) on their own.

Was this reply helpful? Yes No