Duplicate row - select all but one column

Question

I know this question could go through several times here, but I really did not find a workaround. Also my question may be a little different.

Situation: I have a table with 130 columns (+100,000 rows), and the number of columns will even increase in the future. For this reason, I would like to define the selection of columns as [select all but one]

AS I want to duplicate a row using [select all] I get a primary key error message → nc_ID, because of course it is trying to duplicate this value, not increase it by one.

Obviously, the column that I want to exclude from the selection is the first, nc_ID. I read and heard that such decisions [select all but one] can only be done using dynamic sql. If anyone could explain to me through a piece, if sql code?

INSERT into TableName (all columns except the first *nc_ID*) Select * From TableName Where nc_ID=12345;

Thanks in advance!

Answer 1

You asked how to do this in dynamic SQL? “Something like the following should work.”

(Mandatory link to Curse and blessings of dynamic SQL )

DECLARE @TableName varchar(500)
DECLARE @nc_ID INT
SET @nc_ID = 12345
SET @TableName = '[dbo].[TableName]'

DECLARE @Dynsql nvarchar(max)

SELECT @Dynsql = ISNULL(@Dynsql + ',','') + QUOTENAME(name) FROM sys.columns
WHERE object_id = object_id(@TableName) and is_identity = 0 and is_computed = 0
ORDER BY column_id

IF @@ROWCOUNT=0
RAISERROR('%s not found in sys.columns',16,1, @TableName)

SET @Dynsql = 'INSERT INTO  ' + @TableName + '
           ('+ @Dynsql +')
SELECT '+ @Dynsql +'
  FROM ' + @TableName + '
WHERE  nc_ID = @nc_ID'

EXEC sp_executesql @Dynsql, N'@nc_ID int',@nc_ID=@nc_ID

Answer 2

You need to list the column list .

, .

( SQL-), , .

Answer 3

:

SELECT * INTO #MYTEMP FROM TableName WHERE nc_ID = 12345

UPDATE #MYTEMP SET nc_ID = nc_ID + 1;   -- or some other calculation or queried value

INSERT INTO TableName 
 SELECT * FROM #MYTEMP

DROP TABLE #MYTEMP

Answer 4

, . SQL " , ".

130 , , , , . , , () .

Answer 5

When using the query designer in Managment Studio, you make a simple request, for example

SELECT * FROM Table

Management Studio overwrites SQL to explicitly specify all columns. You can then remove the PK from this list.

If you make this request “select all but PK” and use it for all other requests, you will have only one request for updating.