INSERT INTO...WHERE (copy rows from table into same table)

12 Nov 2005 6:48 PM

GW

Trying to copy rows into new rows but changing the first column id to
sNewASMM which is entered by the user.

There are several rows with the sSourceASMM ID.

Table is indexed on inkasmm, inkprod.
sSQL = "INSERT INTO INKT" _
                  & " (inkasmm, inkprod, inkcqty)" _
                  & " VALUES( " _
                  & Chr(34) & sNewASMM & Chr(34) & "," _
                  & "inkprod" & "," _
                  & "inkcqty)" _
                  & " WHERE inkasmm = " & Chr(34) & sSourceASMM & Chr(34) &
";"

The above is probably not the correct method of accomplishing this.

Correct syntax?

TIA GW

12 Nov 2005 7:13 PM

David Portas

Show quote

"GW" <g*@look.ca> wrote in message
news:ePdnlm75FHA.3312@TK2MSFTNGP15.phx.gbl...
> Trying to copy rows into new rows but changing the first column id to
> sNewASMM which is entered by the user.
>
> There are several rows with the sSourceASMM ID.
>
> Table is indexed on inkasmm, inkprod.
> sSQL = "INSERT INTO INKT" _
>                  & " (inkasmm, inkprod, inkcqty)" _
>                  & " VALUES( " _
>                  & Chr(34) & sNewASMM & Chr(34) & "," _
>                  & "inkprod" & "," _
>                  & "inkcqty)" _
>                  & " WHERE inkasmm = " & Chr(34) & sSourceASMM & Chr(34) &
> ";"
>
> The above is probably not the correct method of accomplishing this.
>
> Correct syntax?
>
> TIA GW
>
>

For example:

INSERT INTO INKT (inkasmm, inkprod, inkcqty)
SELECT @newasmm, inkprod, inkcqty
FROM inkasmm = @sourceasmm ;

Learn how to pass parameters properly. Use the parameters connection instead
of concatenated strings. Your INSERT would be better in a stored procedure,
not in your client code. All data access should be done through stored procs
where possible.

--
David Portas
SQL Server MVP
--

12 Nov 2005 9:06 PM

GW

Thank you will do some more reading.

IN the meantime tried your example. Throws an error "Syntax Error in FROM
clause".

sSQL = "INSERT INTO INKT (inkasmm, inkprod, inkcqty)" _

& " SELECT @sNewASMM, inkprod, inkcqty" _

& " FROM inkasmm = @sSourceASMM;"

Show quote

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message
news:D7OdnXGviIM9o-veRVnyvw@giganews.com...
> "GW" <g*@look.ca> wrote in message
> news:ePdnlm75FHA.3312@TK2MSFTNGP15.phx.gbl...
>> Trying to copy rows into new rows but changing the first column id to
>> sNewASMM which is entered by the user.
>>
>> There are several rows with the sSourceASMM ID.
>>
>> Table is indexed on inkasmm, inkprod.
>> sSQL = "INSERT INTO INKT" _
>>                  & " (inkasmm, inkprod, inkcqty)" _
>>                  & " VALUES( " _
>>                  & Chr(34) & sNewASMM & Chr(34) & "," _
>>                  & "inkprod" & "," _
>>                  & "inkcqty)" _
>>                  & " WHERE inkasmm = " & Chr(34) & sSourceASMM & Chr(34)
>> & ";"
>>
>> The above is probably not the correct method of accomplishing this.
>>
>> Correct syntax?
>>
>> TIA GW
>>
>>
>
> For example:
>
> INSERT INTO INKT (inkasmm, inkprod, inkcqty)
> SELECT @newasmm, inkprod, inkcqty
> FROM inkasmm = @sourceasmm ;
>
> Learn how to pass parameters properly. Use the parameters connection
> instead of concatenated strings. Your INSERT would be better in a stored
> procedure, not in your client code. All data access should be done through
> stored procs where possible.
>
> --
> David Portas
> SQL Server MVP
> --
>
>

12 Nov 2005 11:34 PM

Erland Sommarskog

GW (g*@look.ca) writes:

>
> IN the meantime tried your example. Throws an error "Syntax Error in FROM
> clause".
>
> sSQL = "INSERT INTO INKT (inkasmm, inkprod, inkcqty)" _
>
> & " SELECT @sNewASMM, inkprod, inkcqty" _
>
> & " FROM inkasmm = @sSourceASMM;"

David's example should have read:

INSERT INTO INKT (inkasmm, inkprod, inkcqty)
SELECT @newasmm, inkprod, inkcqty
FROM INKT
WHERE inkasmm = @sourceasmm ;

I echo his recommendation of using parameterised statements. Stored
procedures is good, but it is more important that you use parameterised
statements, because when you insert input data from users in your SQL
strings, nasty things can happens if there are string delimiters in
the data. Malicious users can use this do to evil things.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

13 Nov 2005 12:20 AM

GW

Thks. Yes I'm aware of this but not a problem here.

&& Allthough I posted this in an SQL Server prg group. Forgot to mention
this is applied against Access DB using OleBD in DN 2003.

GW

Show quote

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns970D583B3D35Yazorman@127.0.0.1...
> GW (g*@look.ca) writes:
>>
>> IN the meantime tried your example. Throws an error "Syntax Error in FROM
>> clause".
>>
>> sSQL = "INSERT INTO INKT (inkasmm, inkprod, inkcqty)" _
>>
>> & " SELECT @sNewASMM, inkprod, inkcqty" _
>>
>> & " FROM inkasmm = @sSourceASMM;"
>
> David's example should have read:
>
> INSERT INTO INKT (inkasmm, inkprod, inkcqty)
> SELECT @newasmm, inkprod, inkcqty
> FROM INKT
> WHERE inkasmm = @sourceasmm ;
>
> I echo his recommendation of using parameterised statements. Stored
> procedures is good, but it is more important that you use parameterised
> statements, because when you insert input data from users in your SQL
> strings, nasty things can happens if there are string delimiters in
> the data. Malicious users can use this do to evil things.
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx