Precompiled insert-select

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I need to duplicate a record in a table, copying everything but the key. I
used to do this in a different program using a regular SQL sentence (ie
building the sentence in a string and executing it, no parameters) and it
worked fine. However I'm now trying to use a precompiled query such as the
one below (sorry for the Spanish field names) and I get a SQL exception with
the text "Parameter not allowed here". I know "here" means in the SELECT
part, so ... how do I change the key of the new record if I can't put the new
key in a parameter?

INSERT INTO DOCUMENTO_HD (DOCUMENTO_ID, KUNNR, FECHA_ENTREGA, HORA_ENTREGA,
FECHA_COBRO, ESTADO_PC, ESTADO_SAP, TIPO, CONTROL, PRECIO, AGRUPACION) SELECT
?, KUNNR, FECHA_ENTREGA, HORA_ENTREGA, FECHA_COBRO, ESTADO_PC, ESTADO_SAP,
TIPO, CONTROL, PRECIO, AGRUPACION FROM DOCUMENTO_HD WHERE DOCUMENTO_ID=?
 
AFAIK SQL CE does not support INSERT INTO ... SELECT construct. You need 2
separate statements
 
Alex,

It does support it, what it doesn't seem to support are parameters in the
SELECT part. If I replace the ? in that select with a hardcode value, it
works fine (unfortunately it becomes useless in the process).

I am trying to avoid doing the SELECT first because it's way
slower...specially when I need to copy a lot of records. For example, suppose
I have the obvious master/detail tables with orders, the master having the
customer information and the detail having the product codes, quantity etc.
Now I want to duplicate a complete order. With an INSERT ... SELECT
construct, I just need two (one for the header and one for the detail), no
matter how large the order is.
 
Carlos Fernandez said:
Alex,

It does support it, what it doesn't seem to support are parameters in the
SELECT part. If I replace the ? in that select with a hardcode value, it
works fine (unfortunately it becomes useless in the process).
Click to expand...

Interesting. I haven't tried it, just checked the docs and got an impression
that it's not supported. Good to know it does.
I am trying to avoid doing the SELECT first because it's way
slower...specially when I need to copy a lot of records. For example,
suppose
I have the obvious master/detail tables with orders, the master having the
customer information and the detail having the product codes, quantity
etc.
Now I want to duplicate a complete order. With an INSERT ... SELECT
construct, I just need two (one for the header and one for the detail), no
matter how large the order is.
Click to expand...

This would not be the first bug in the command text processor of the SQL CE
ADO.NET provider. It was posted earlier that a similar error is thrown if
you specify a query like "SELECT * from Table where Name = Trim(?)"

I'm afraid this mean that you won't be able to use a precompiled query here.
 
Back
Top