Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 812
  • Last Modified:

How to copy row into same table - MS SQL

I have a datatable with around 50 columns, and I need a way to copy a row.
But before the insert is runned I need to change some column values.

I have Tabel: Profile
Which have columns: system_id, name + 50 other.

Lets say I have rows:
system_id  name + 50
      52        test
      57        test2

I want to copy row with system_id 52 into the same table, but change the system_id and name first.
I have variables containing the new values that will replace the old ones, @system_id and @name. But I'm unsure on how to do this.

Do I have to create a temp table containing all the 50 columns, or is there an easier way to do this?

If you need more clarification please ask.

Thanks in advanced:)

Regards
Marius
0
Contestoas
Asked:
Contestoas
  • 8
  • 5
  • 2
  • +1
2 Solutions
 
Alpha AuCommented:
the easiest way i can think of is :
select * into #temp from Profile where system_id = 52
 
update #temp 
set system_id = 'user_value'
, field1 = value 1
, ...
, ...
 
insert into Profile
    select * from #temp
 
drop table #temp

Open in new window

0
 
ContestoasAuthor Commented:
Perfect, just what I was looking for, thanks :)

Marius
0
 
RiteshShahCommented:
what about this?

 declare @system_id  int
 declare @name varchar(50)
 
 set @system_id=60
 set @name='Ritesh'
 

insert into table1 (system_id,name,50+ columns)
select @system_id,@name, 50+ columns from table1 where system_id=52
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
RiteshShahCommented:
>>Perfect, just what I was looking for, thanks :)<<

creating temp table and dropping it, won't it create overhead on database?

0
 
ContestoasAuthor Commented:
I'm pretty newbie regading DB's so I dont know what overhead means.
0
 
RiteshShahCommented:
It will take so much resources and slow down performance but in my way, it is easy, readable and more efficient.
0
 
ContestoasAuthor Commented:
Okay, thanks for you answer aswell then :)
0
 
RiteshShahCommented:
I expected split points, anyway, that's ok.
0
 
ContestoasAuthor Commented:
Sorry but the points were given away before I saw your posting.
0
 
RiteshShahCommented:
if you wish than you can change it by clicking on "Request Attention" button, moderator will open question and you can re-assign points.
0
 
Alpha AuCommented:
it would be much faster (in developer view) to type
select * into #temp
instead of typing the 50+ column name (correctly)

and it reduce the maintenance need for table structure change.
(if table structure change, the select * into #temp from Profile don't need to change, via the 50+ column name need to add the affected column name)

as for the production impact,
i agree that is not the fastest way to run,
but it don't really affect so much, accounting for the ease of program maintenance .
0
 
RiteshShahCommented:
>>but it don't really affect so much, accounting for the ease of program maintenance .<<

programmer needs to make program just once, but user needs to use it on everyday basis, so it is better to keep that in mind and have to create or develop something which is really faster which can save so many resources of system and man hours.
0
 
ContestoasAuthor Commented:
A colleague of mine showed me a very good way to do this:
declare @cols nvarchar(max), @sql nvarchar(max)
select @cols=null
select @cols=coalesce(@cols + ',', '') + name
from sys.columns where object_id= (select object_id from sys.tables where name='profile')
AND name not in ('SYSTEM_ID', 'DOCNAME')
set @sql=N'insert into docsadm.profile (SYSTEM_ID, DOCNAME, ' + @cols + ')
                  SELECT ' + STR(@SYSTEM_ID) + ', ''MY DOCNAME'', ' + @COLS + ' FROM PROFILE
                  WHERE SYSTEM_ID=16'
EXEC SP_EXECUTESQL @SQL

0
 
RiteshShahCommented:
:) yes, it is also one of the good way to go for, it is partially based on my concept but didn't use temp table so it will have good performance also. Even if you will compare this with my way, my query will run faster for sure, you can compare execution plan. However this is better approach than temp table.


0
 
Anthony PerkinsCommented:
alphaau,

>>instead of typing the 50+ column name (correctly)<<
Why would you do that?  Surely you know that there is no need to type all the column names.  All you have to do is drag the Columns object for the table in question and you have all your comma delimited names.

>>if table structure change, the select * into #temp from Profile don't need to change<<
Please don't encourage the use of SELECT *.  Except for adhoc queries it is a very bad idea.  And no we will not get in to a debate here about it, just do a search on Google and find out why.

>>but it don't really affect so much, accounting for the ease of program maintenance .<<
I see.  So you believe that applications are written solely for the ease of the developer?  Interesting concept.
0
 
RiteshShahCommented:
good points Acperkins, I wanted to convey the same but you know that I am not as good as you in words. :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now