SQL Server : Duplicate row in table while changing some values with select *

Question

I need to insert an almost duplicated row into table, while changing few values.

For example insert duplicated row with new id (I don't want automatic id) and different name but all other values the same. The problem is that I need to make a select *

I know that there is a way to insert from select and changing values this way :

insert into Table1(id,name,surname) select newid(),'David',surname from Table1 where id=1

but I don't want to enlist all fields ,instead I want to use select *, so if fields added I won't have to change my stored procedure.

I want something like :

insert into Table1 (
   update (SELECT *
          FROM Table1
          WHERE  id= 1  ) t 
    set t.id= newid(),name='David')

Is there a way to do it ?

It is a bad practice to use * in your queries. So if you start to follow that best practive you will kill two birds with one stone... — Giorgi Nakeuri, Oct 29 '15 at 09:14
I know it's generally a bad practice but in case fields will be added to my table I will have to change my sp. As we have many sp here those new fields surely be forgotten. — Polina F., Oct 29 '15 at 09:18

Oday Fraiwan · Answer 1 · 2015-10-29T10:08:11.627

5

You can use temp hash table to accomplish this.

SELECT *
INTO #temp 
FROM Table1
WHERE id= 1;

UPDATE #temp 
  SET ID = newid(),
      Name='David'

INSERT INTO Table1 SELECT * FROM #temp;

Note that the #temp table is automatically dropped when the client disconnect from the DB server.

Also, as previously noted, I prefer to use column names separately instead of *.

Example: SQL Fiddle

edited Oct 29 '15 at 10:08

answered Oct 29 '15 at 10:01

Oday Fraiwan

1,147
1
9
21

What is the overhead of creating a table ? – Polina F. Oct 29 '15 at 10:10
Please refer to the following post for more details on which temp table type you should use: http://stackoverflow.com/questions/11857789/when-should-i-use-a-table-variable-vs-temporary-table-in-sql-server. – Oday Fraiwan Oct 29 '15 at 10:15
Using a temp hash table was perfect in my scenario as I do not have create table permissions. – Mr.Technician Oct 14 '22 at 14:11

score 1 · Accepted Answer · answered Jun 06 '16 at 12:23

The code I use:

    declare @table sysname
declare @excludecols nvarchar(max)
declare @uniqueWhereToCopy nvarchar(max)
declare @valuesToChange nvarchar(max)

--copy settings
set @table = 'orsrg' --the tablename
set @excludecols='' --columnnames to exclude from the copy, seperated by commas
set @uniqueWhereToCopy = 'ID=1188'
set @valuesToChange = 'regel=''   4''' --columnName=<value>,columnName2=<value2>, .... (needed for unique indexes)

set @excludecols=@excludecols + ','
set @valuesToChange=@valuesToChange + ','

--get the columnnames to copy
declare @sqlcolumns nvarchar(max)
set @sqlcolumns = ''
SELECT @sqlcolumns = @sqlcolumns + name from
    (select '[' + c.name + '], ' as name FROM sys.COLUMNS c inner join sys.objects o
        on c.object_id = o.object_id 
    WHERE o.name = @table
    and is_identity = 0 /*exclude identity*/
    and is_rowguidcol = 0 /*exclude rowguids*/
    and is_computed = 0 /*exclude computed columns*/
    and system_type_id <> 189 /*exclude timestamp*/
    and charindex(c.name, @excludecols,1) = 0 /*exclude user specified columns*/)q

--get the select columns and values
declare @sqlselectvalues nvarchar(max)
set @sqlselectvalues = @sqlcolumns
while len(@valuesToChange)>1
begin
    declare @colValueSet nvarchar(max)
    declare @colname sysname
    declare @value nvarchar(max)
    set @colValueSet = left(@valuesToChange,charindex(',',@valuesToChange,1)-1)
    set @valuesToChange = substring(@valuesToChange,charindex(',',@valuesToChange,1)+1,len(@valuesToChange))
    set @colname = '[' + left(@colValueSet,charindex('=',@colValueSet,1)-1) +']'
    set @value = substring(@colValueSet,charindex('=',@colValueSet,1)+1,len(@colValueSet))

    set @sqlselectvalues = REPLACE(@sqlselectvalues,@colname,@value)
end

--remove the last comma
set @sqlcolumns = left(@sqlcolumns, len(@sqlcolumns)-1)
set @sqlselectvalues = left(@sqlselectvalues, len(@sqlselectvalues)-1)

--create the statement
declare @stmt nvarchar(max)
set @stmt = 'Insert into ' + @table + '(' + @sqlcolumns + ') select ' + @sqlselectvalues + ' from ' + @table + ' with (nolock) where ' + @uniqueWhereToCopy
--copy the row
exec sp_executesql @stmt

Twinkles · Answer 3 · 2015-10-29T09:19:46.567

0

No, because a SELECT * will always contain the id column.

Generally, you should avoid SELECT * anywhere except when querying interactively. When the stored procedure is compiled, the query text will be parsed and replaced with the correct columns, rendering your stored procedure invalid on every change to the structure anyway.

edited Oct 29 '15 at 09:19

answered Oct 29 '15 at 09:13

Twinkles

1,984
1
17
31

That's why I want to somehow insert an update over the selection – Polina F. Oct 29 '15 at 09:14

SQL Server : Duplicate row in table while changing some values with select *

3 Answers3