copy rows with special condition
- by pooria_googooli
I have a table with a lot of columns. For example I have a table with these columns :
ID,Fname,Lname,Tel,Mob,Email,Job,Code,Company,......
ID column is auto number column. I want to 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
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
and I had this 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.
What should I do ?
