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.
dvplayltdAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
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
0
TheAvengerCommented:
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

0
Mike EghtebasDatabase and Application DeveloperCommented:
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

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

dvplayltdAuthor Commented:
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
0
Anthony PerkinsCommented:
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
0
dvplayltdAuthor Commented:
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/
0
Anthony PerkinsCommented:
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.
0
dvplayltdAuthor Commented:
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 …)
0
Anthony PerkinsCommented:
It looks like you are going to have to script it out as suggested previously.
0
TheAvengerCommented:
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.
0
dvplayltdAuthor Commented:
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.
0
TheAvengerCommented:
Here is a version that gives you back the new id:

CREATE PROCEDURE Duplicate
	@tableName varchar(max),
	@id int,
	@newId int output
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 = '
	select @newId = max(' + @idcol + ') + 1 from ' + @tableName + ';
	insert into ' + @tableName + ' (' + @idcol + ', ' + @cols + ') select @newId, ' + @cols + ' from ' + @tableName + ' where ' + @idcol + ' = @id'

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

Open in new window


And here is how to call it:

declare @newId int
exec Duplicate 'mytable', 123, @newId output

Open in new window


Now @newId contains the ID of the new record.

Unfortunately providing the foreign key as parameter would be a bit more difficult. You can do it with a subsequent update (already having the ID of the new record)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dvplayltdAuthor Commented:
10x - it works!

What permission need the user to be able to do this?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.