INSERT range question

I have would like to insert n rows where fieldA = 1
When I insert the new rows, I would like to set FieldA = [some value]
I see in the docs that I can insert a range like this:
<code>
INSERT INTO Tbl_A
SELECT * FROM Tbl_A WHERE FieldA = 1
</code>
Is there anything I can do to change the value of FieldA to [some value]
before INSERTING it?
Thanks for any tips!Steve,
There needs to be data in the table prior to the update so perform the
INSERT first then perform the UPDATE. Also, choose a different table for
the INSERT.
i.e.,
INSERT INTO Tbl_A_1
SELECT * FROM Tbl_A WHERE FieldA = 1
UPDATE TBL_A_1
SET FIELDA = xxx
WHERE FIELDA = 1
You can put this in a user-defined transaction if required.
HTH
Jerry
"Steve" <sss@.sss.com> wrote in message
news:usiPxvSyFHA.2992@.TK2MSFTNGP11.phx.gbl...
>I have would like to insert n rows where fieldA = 1
> When I insert the new rows, I would like to set FieldA = [some value]
> I see in the docs that I can insert a range like this:
> <code>
> INSERT INTO Tbl_A
> SELECT * FROM Tbl_A WHERE FieldA = 1
> </code>
> Is there anything I can do to change the value of FieldA to [some value]
> before INSERTING it?
> Thanks for any tips!
>|||Hi Steve
Best practices recommend that you NEVER use SELECT * in your production
code, but always explicitly list the columns you want to return. So if you
take that to heart you can insert whatever you like in place of FieldA.
INSERT INTO Tbl_A
SELECT MyNewValue as FieldA, FieldB, FieldC ...
WHERE FieldA = 1
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Steve" <sss@.sss.com> wrote in message
news:usiPxvSyFHA.2992@.TK2MSFTNGP11.phx.gbl...
>I have would like to insert n rows where fieldA = 1
> When I insert the new rows, I would like to set FieldA = [some value]
> I see in the docs that I can insert a range like this:
> <code>
> INSERT INTO Tbl_A
> SELECT * FROM Tbl_A WHERE FieldA = 1
> </code>
> Is there anything I can do to change the value of FieldA to [some value]
> before INSERTING it?
> Thanks for any tips!
>
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23DVt35SyFHA.612@.TK2MSFTNGP10.phx.gbl...
> Steve,
> There needs to be data in the table prior to the update so perform the
> INSERT first then perform the UPDATE. Also, choose a different table for
> the INSERT.
> i.e.,
> INSERT INTO Tbl_A_1
> SELECT * FROM Tbl_A WHERE FieldA = 1
> UPDATE TBL_A_1
> SET FIELDA = xxx
> WHERE FIELDA = 1
> You can put this in a user-defined transaction if required.
> HTH
> Jerry
>
> "Steve" <sss@.sss.com> wrote in message
> news:usiPxvSyFHA.2992@.TK2MSFTNGP11.phx.gbl...
>
Hi Jerry, thank you for the response. I should have been more clear. I see
what you are suggesting and it makes sense, however I don't wish to change
the original rows from the select statement, only the rows that were
inserted later. With your example, I would be changing both the selected
and inserted rows FieldA value or am I missing something?
I basically want to copy n rows and change a field on the copied rows only.
Thanks for the post!
Steve|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:udl$N9SyFHA.3720@.TK2MSFTNGP11.phx.gbl...
> Hi Steve
> Best practices recommend that you NEVER use SELECT * in your production
> code, but always explicitly list the columns you want to return. So if you
> take that to heart you can insert whatever you like in place of FieldA.
> INSERT INTO Tbl_A
> SELECT MyNewValue as FieldA, FieldB, FieldC ...
> WHERE FieldA = 1
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Steve" <sss@.sss.com> wrote in message
> news:usiPxvSyFHA.2992@.TK2MSFTNGP11.phx.gbl...
Thanks for the post Kalen,
I agree with that you said about not selecting wildcards, that is my mistake
;)
I will attempt what you have outlined here, it looks promising, I would like
to find a solution that will work with SqlServer and MS Access so that our
sales guys can take the app with them on their laptops.
I'll let you know how I make out.
Thanks again!