copy rows with special condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

copy rows with special condition

Author

Topic

pooria_googooli
Starting Member

2 Posts

Posted - 2012-03-20 : 07:26:32

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 ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 15:36:10

I don't want to write name all of the columns because i have a lot of table with a lot of columns.

you mean you want this to be done for lot of tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pooria_googooli
Starting Member

2 Posts

Posted - 2012-03-20 : 15:41:55

not all the table at a second , it means that the query should be something like 'select * from TBL1' . for any table i can change the table name and get the result and it is not need to write the columns of every table . do you get it ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 15:45:07

then that means you need to use dynamic sql. you can add cursor or while loop to loop through INFORMATION_SCHEMA.COLUMNS to get column information for each table and then dynamically form update for each

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources