I have anoter Databases than SQL Server where I should export data and insert it into new SQL Server. Data is much complex than just inserting it directly from extracted Excel Sheets. Data has Keywords that should be read using somekind of "lookup"
action and convert it before inserting SQL Server.
Are there any "Best Practices" of How to-documents how Excel could be used converting data from another Database into SQL Server? The problem is that there are some logic to be done before inserting data into SQL Server. Can anyone point be a common problems
and how to solve then in Import process?
You might use PowerPivot to import the source data and perform the calculations you need to do. Next you create Flattened PivotTable to export it to an Excel sheet. With SSIS (Integration Services) you can transfer the Excel data to the destination DB.
Dependant on the volume of data, you might transfer the data in one or more steps by changing criteria of the import query for PowerPivot.