sql server - Query optimization.: Insert few raws from a table into same table after modifiying one or few column: -

well, need insert rows table (dbo.orderdata) mathematical modification(here multiplication -1) column(unit price) keeping other columns value is.

here code: considering northwind db:

create table dbo.orderdata (     orderid int not null,     productid int not null,     unitprice money not null,     quantity smallint not null,     discount real not null )  insert dbo.orderdata select * dbo.[order details] 

and insert code follows :

insert dbo.orderdata (orderid, productid, unitprice, quantity, discount)     select          orderid, productid, unitprice * -1, quantity, discount               dbo.orderdata              orderid = 10530  

i escaped inbuilt dbo.[order details] table due many key-constraint, i'm bit lazy ;)

finally alternatives above insertion script need not specify columns name .please note in scenario 1 column may identity column.

all answers , comments appreciated.

if orderid, or combination of orderid , productid, pk, insert duplicates pk fail pk constraint violation. you're trying insert 10530, there.

if orderid identity autoincrement field, following should work, , new orderid value created:

insert dbo.orderdata(productid,unitprice,quantity,discount) select productid,unitprice*-1,quantity,discount dbo.orderdata orderid=10530  

you may wish avoid dealing key constraints, in fact telling important data. if, example, combination of orderid , productid pk, means table should have 1 record combination of key values. should not want insert duplicate in manner.

what reason trying insert duplicate? if trying implement "credit" on order, might want think of way implement doesn't break data integrity. keys there reason. could, example, add "transactiontype" field composite key, if best description of data means.

the same thing true trying insert record without naming of fields. can avoid doing in following cases, why want to? fieldnames important integrity of insert.

if column not in column_list, database engine must able provide value based on definition of column; otherwise, row cannot loaded. database engine automatically provides value column if column:

1. has identity property. next incremental identity value used.

2. has default. default value column used.

3. has timestamp data type. current timestamp value used.

4. is nullable. null value used.

5. is computed column. calculated value used.