Solved

How to copy row into same table - MS SQL

Posted on 2009-07-08
17
792 Views
Last Modified: 2012-05-07
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
Comment
Question by:Contestoas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
  • +1
17 Comments
 
LVL 7

Accepted Solution

by:
Alpha Au earned 250 total points
ID: 24801740
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
 

Author Comment

by:Contestoas
ID: 24801810
Perfect, just what I was looking for, thanks :)

Marius
0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 250 total points
ID: 24801827
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24801832
>>Perfect, just what I was looking for, thanks :)<<

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

0
 

Author Comment

by:Contestoas
ID: 24801988
I'm pretty newbie regading DB's so I dont know what overhead means.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24802065
It will take so much resources and slow down performance but in my way, it is easy, readable and more efficient.
0
 

Author Comment

by:Contestoas
ID: 24802106
Okay, thanks for you answer aswell then :)
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24802115
I expected split points, anyway, that's ok.
0
 

Author Comment

by:Contestoas
ID: 24802146
Sorry but the points were given away before I saw your posting.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24802205
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
 
LVL 7

Expert Comment

by:Alpha Au
ID: 24802316
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24802325
>>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
 

Author Comment

by:Contestoas
ID: 24802439
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24802456
:) 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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24810592
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24810949
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Running total between 2 sql tables in Sql 6 50
MSSQL Query for Selecting the SUM of a Specific Group 2 37
What type of testing am I doing? 4 76
What does "Between" mean? 6 47
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question