asked on

How to duplicate a record from one table to the same table without explicit describe all fields?

Dear experts

I need to write a Stored Procedure for SQL Server 2008 with function to copy a record from table to the same table with change only the primary key /Int/ . So I’m curious do I have option to avoid explicitly describe the fields with T-SQL ? It will be faster (actually I need this solution for 6 tables), but also if I add a addition filed in the table it will be good to avoid the need to fix this (these) stored procedures .. In C# this could be done with loop via Fields collection, so I need equal functions but in T-SQL

Logically, the function for my user will be to copy a old campaign in new one and then to edit this new one campaign. A campaign is consist of these 6 tables relative records.

Mike Eghtebas

In C#:
int intPKnew;
int intPKold

' call proc with intPKold and store the new intPKnew returened to facilitate record edit.

In T-SQL (or C#):
CREATE PROCEDURE uspGetNewPK(@intPKold int, @intPKnew int output)
AS
BEGIN

< will post after a test>

END

Mike

TheAvenger

This is the script. You need to change the name of the table (it's currently mytable) and the id of the record you want to copy (at the very end):

DECLARE @cols VARCHAR(max) 
SELECT @cols = COALESCE(@cols + ', ', '') + name 
FROM (
	select distinct c.name
	from 
    sys.columns c
INNER JOIN 
    sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('mytable')
	and isnull(i.is_primary_key,0) <> 1
) as t

declare @idcol varchar(max)
select @idcol = c.name
	from 
    sys.columns c
INNER JOIN 
    sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('mytable')
	and isnull(i.is_primary_key,0) = 1

declare @sql varchar(max)
set @sql = 'insert into mytable (' + @cols + ') select ' + @cols + ' from mytable where ' + @idcol + ' = @id'

select @sql

exec sp_executesql @sql, N'@id int', @id = 5

Open in new window

Mike Eghtebas

Well, TheAvenger has the solution. For some reason if you prefer stored procedure like below, I would try to complete it. We could include table string also in order to work with any of the tables you have. Please note this is work in progress.

Thx

CREATE PROCEDURE uspGetNewPK(@intPKold int, @intPKnew int output)
AS
BEGIN

	DECLARE @ColCount int, @counter int = 1
	DECLARE @sqlStr varchar(max)=''
	DECLARE @ColName varchar(50)=''
	DECLARE @identity int = 0
	DECLARE @NewPK int

	 -- find column count
	 SELECT @ColCount = count(*) 
	 FROM information_schema.columns 
	 WHERE table_name = 'TestTable'

	 SET @counter = 1

	-- make list of fields
	While @counter <= @ColCount
		Begin

			-- find column name at position @counter
			SELECT @ColName = COL_NAME(OBJECT_ID('dbo.[TestTable]'), @counter)

			-- ignore if identiry column
			if COLUMNPROPERTY( OBJECT_ID('dbo.[TestTable]'),@ColName,'IsIdentity')<>1
				Begin
					IF @sqlStr = '' 
						SELECT @sqlStr =  @ColName
					ELSE
						SELECT @sqlStr =  @sqlStr + ', ' + @ColName
				End

			-- move to next column
			SET @counter = @counter + 1
		End 
		
		select @sqlStr
		-- insert record w/ ID = @intPKold *** not working yet ***
		--INSERT INTO dbo.[TestTable] (@sqlStr) 
		--SELECT @sqlStr FROM dbo.[TestTable] a
		--WHERE a.ID = @intPKold;
   
        -- get the latest PK
		SELECT intPKnew =  @@IDENTITY
		Return
END
GO
DECLARE @OutputPK int
Exec uspGetNewPK 3, @OutputPK
SELECT @OutputPK
GO

Open in new window

dvplayltd

ASKER

To both of your

First – thanks for your help. I guess I’ll split the points, but first I need those improvements:

1). My ID is Int but it is NOT with auto increment, I manually find the max(ID) and increment it with 1. With your offer this code will make Primary Key violation error. How to replace the ID BEFORE insert the record?

2) It will be great if the table name is send as params, then in my real SP I’ll be able to call 6 times this function with 6 line code, not with 6 * 50 = 300 lines

Anthony Perkins

So I’m curious do I have option to avoid explicitly describe the fields with T-SQL ?
It depends, if you are using IDENTITY columns

1.

If you are then you will have to explicitly name your columns (you can of course script these out as described previously).

2.

If you are not using an IDENTITY column then you don't have to and you can resort to something like this:
INSERT TableName1 SELECT * FROM TableName2

dvplayltd

ASKER

To acperkins

Well, I can’t use this

INSERT TableName1 SELECT * FROM TableName2

Because I’ll get Primary Key violation. So in fact – I need this T-SQL but without the first filed for which I should manually pass new value (the last entered + 1). How to do this? In fact, I need to change 2 fields in a table – the primary key value and the foreign key value /if this table is detail table to other, but here I ask only for Primary key as it will be easy later to change and the foreign key/

Anthony Perkins

INSERT TableName1 SELECT * FROM TableName2
That was just an example and obviously not the true code.

Because I’ll get Primary Key violation.
That is unrelated.

Again, please answer the question (it is not complicated) are you using IDENTITY columns or not? If you are then you have no choice but to explicitly name all the columns. If you don't understand the question or know what is an IDENTITY column, then ask.

dvplayltd

ASKER

I do not use a Indentify column in meaning a new key which to be auto given by SQL Server. I use manually set of new ID – here you one of my SP for insert new record

SELECT @ID=(ISNULL(MAX(LPrgBlockID), 0) + 1) FROM dbo.LPrgBlocks

INSERT INTO LPrgBlocks
(LPrgBlockID, otherfields ...)
VALUES
(@ID, @ otherfields_data …)

Anthony Perkins

It looks like you are going to have to script it out as suggested previously.

TheAvenger

Here is the exact procedure which works with the ID being a normal int column:

CREATE PROCEDURE Duplicate
	@tableName varchar(max),
	@id int
AS
	DECLARE @cols VARCHAR(max) 
	SELECT @cols = COALESCE(@cols + ', ', '') + name 
	FROM (
		select distinct c.name
		from 
		sys.columns c
	INNER JOIN 
		sys.types t ON c.system_type_id = t.system_type_id
	LEFT OUTER JOIN 
		sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
	LEFT OUTER JOIN 
		sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
	WHERE
		c.object_id = OBJECT_ID(@tableName)
		and isnull(i.is_primary_key,0) <> 1
	) as t

	declare @idcol varchar(max)
	select @idcol = c.name
		from 
		sys.columns c
	INNER JOIN 
		sys.types t ON c.system_type_id = t.system_type_id
	LEFT OUTER JOIN 
		sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
	LEFT OUTER JOIN 
		sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
	WHERE
		c.object_id = OBJECT_ID(@tableName)
		and isnull(i.is_primary_key,0) = 1

	declare @sql nvarchar(max)
	set @sql = '
	declare @maxId int;
	select @maxId = max(' + @idcol + ') + 1 from ' + @tableName + ';
	insert into ' + @tableName + ' (' + @idcol + ', ' + @cols + ') select @maxId, ' + @cols + ' from ' + @tableName + ' where ' + @idcol + ' = @id'

	exec sp_executesql @sql, N'@id int', @id = @id
GO

Open in new window

And here is how to call it:

exec Duplicate 'mytable', 123

Open in new window

where mytable is the name of the table you want to copy, 123 is the ID of the record you want to copy.

dvplayltd

ASKER

To TheAvenger

It look great and it working, I test it already. One last note - is it possible to return back the new ID ? How ?

And other - if it is not tool much and it is possible, I want to pass one other field name and its value to the SP /this is the for foringn field - if I not do this at insert I'll do it after I get new record and its ID.

ASKER CERTIFIED SOLUTION

TheAvenger

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dvplayltd

ASKER

10x - it works!

What permission need the user to be able to do this?