hello every body . i have some table with a lot of columns . for example i have a table with this columns :
ID,Fname,Lname,Tel,Mob,Email,Job,Code,Company,......
ID columns is auto number column. i wanna copy all rows in this table to this table . and change the 'company' column value to 12 in this copied row . I don't want to write name all of the columns because i have a lot of table with a lot of columns.
i tried this code but i had this error :
declare @c int
declare @i int
select * into CmDet from CmDet;
select @C= count(id) from CmDet;
while @i < @C
begin
UPDATE CmDet
SET company =12
WHERE company=11
set @i += 1
end
error :
Msg 2714, Level 16, State 6, Line 3
There is already an object named 'CmDet' in the database.
i changed the code to this and i had this error :
declare @c int
declare @i int
insert into CmDet select * from CmDet;
select @C= count(id) from CmDet;
while @i < @C
begin
UPDATE CmDet
SET company =12
WHERE company=11
set @i += 1
end
error : Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'CmDet' can only be specified when a column list is used and IDENTITY_INSERT is ON.
for example i want to have these rows in table 'CmDet'
Id=1 fname=pooria lname=lajevardi job=programmer company=11
Id=2 fname=roya lname=akbari job=teacher company=11
Id=3 fname=pooria lname=lajevardi job=programmer company=12
Id=4 fname=roya lname=akbari job=teacher company=12
what should i do ?
copy rows with special condition
Posted - 03/20/2012 : 07:26:32
