Hi. I'm using the adapter in llbl v. 1.0.2005.1 and I'm struggling to figure out how to perform an insert using a sub query.

Is there a way to generate an insert statement like:

insert into tableA(field1, field2,field3) select field1,field2,field3 from tableB

I would just perform this action using a sproc, but my application supports both Access and SQL server, so I'm limited to the capabilities of Access (I'm using Access 2003). Also, performance is an issue. The inserts will generally be at least 2000 records.

Any help would be greatly appreciated.

Thanks!

Hi cwest, I'm afraid that's no possible, as LLBLGen is an OR/Mapper tool this feature isn't natural. Did you benchmark the two approaches (in SQLServer for instance)? how much you lose?

daelmo, Thanks for the prompt response. Knowing that one route will lead to thousands of insert statements each separately executed over the wire, I was hoping to envision a more optimal solution prior to writing any code or needing to perform a benchmark. Additionally, I'm afraid concurrent transactions of this type would prove to be more intensive than satisfactory. I was hoping there would be a way to do a bulk insert using field expressions similar to the way one can do bulk updates. I understand a bulk insert would be a bit more complex and isn't exactly natural for an orm, but I've been nothing less than impressed with LLBL so far and thought perhaps there may be a way.

Have you got any ideas of how this problem can be solved in an efficient manor (without the use of a sproc)? I can't bring myself to generate thousands of insert statements that can be achieved in one. The only option I can think of is using ADO directly.

cwest

This can be handled in a method that first fetch from the first table, and then construct a new entityCollection (entities with field values fed from the first fetch) with a for loop to copy data from the fetched collection. Then ending up by saving the new entityCollection.

You might use Projection to ease up your work, You can fetch a dataTable (of table 1), and then project it to an EntityCollection (of table 2).

Walaa, Won't this approach create an insert statement for each item in the collection when the new collection is saved?

Is projection available in V.1.0.2005.1? I couldn't find anything on it in the help.

Thanks

Hi,

The projection is only available in v2.x so you cannot use it. This approach will create an insert statement for each item in the collection but it will be done in the same transaction.