Is it possible to link existing Sharepoint 2010 lists to SQL server databases?

Question

I created a SQL server database and added a table to it. Then I used Sharepoint Designer 2010 to create an external Content Type using this table. Now I can alter the according Sharepoint list which then again performs CRUD operations on the said database table.

Now I want to do the same with a existing list which already contains data. In other words, I want to synchronize already filled lists to a table in my SQL Server database. As I am new to Sharepoint this is the only solution I can think of.

I would create a table in my database which contains the very same columns as my already existing Sharepoint list does. Then I would proceed as described above. Namely I would use Sharepoint Designer to create an External Content Type. This would generate a new list which would be connected to my database table. Now I would copy the complete data from the original list to the newly created and synchronized list. This way the data should also be synchronized with the database table now. Now I would simply delete the old list.

I somehow have the feeling this is not a clean solution for my task. Is there any easier or cleaner way to do this? Is this even possible?

Answer 1

I think this is the correct way of doing it. Copying the data from Old List to new List using datasheet view would have made copying data easy, but datasheet view is not available in External list. You can create a console application or PowerShell script which will read data from old list and insert it into new External list.