SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 copy rows with special condition
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pooria_googooli
Starting Member

2 Posts

Posted - 03/20/2012 :  07:26:32  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 03/20/2012 :  15:36:10  Show Profile  Reply with Quote
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/

Go to Top of Page

pooria_googooli
Starting Member

2 Posts

Posted - 03/20/2012 :  15:41:55  Show Profile  Reply with Quote
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 ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/20/2012 :  15:45:07  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000