I'm writing a stored procedure that requires I duplicate records through a loop, one record at a time (required because I need to execute SCOPE_IDENTITY() logic on each insertion). But each row has 40 or so columns, making my stored procedure ridiculously full of long declaration lists. So I want to either:
1) Learn a way to auto-insert the column declarations into my code without having to type them all by hand, or...
2) Learn a way to represent the whole row for insertion, without having to specify each column specifically.
The latter solution would be the most elegant, but I'll take what I can get...
how about this?
--
IDENTITY (Function)
Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.
Although similar, the IDENTITY function is not the IDENTITY property that is used with CREATE TABLE and ALTER TABLE.
Syntax
IDENTITY ( data_type [ , seed , increment ] ) AS column_name
Arguments
data_type
Is the data type of the identity column. Valid data types for an identity column are any data types of the integer data type category (except for the bit data type), or decimal data type.
seed
Is the value to be assigned to the first row in the table. Each subsequent row is assigned the next identity value, which is equal to the last IDENTITY value plus the increment value. If neither seed nor increment is specified, both default to 1.
increment
Is the increment to add to the seed value for successive rows in the table.
column_name
Is the name of the column that is to be inserted into the new table.
Return Types
Returns the same as data_type.
Remarks
Because this function creates a column in a table, a name for the column must be specified in the select list in one of these ways:
--(1)SELECT IDENTITY(int, 1,1) AS ID_Num
INTO NewTable
FROM OldTable
--(2)
SELECT ID_Num = IDENTITY(int, 1, 1)
INTO NewTable
FROM OldTable
Examples
This example inserts all rows from the employee table from the pubs database into a new table called employees. The IDENTITY function is used to start identification numbers at 100 instead of 1 in the employees table.
USE pubsIF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'employees')
DROP TABLE employees
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
SELECT emp_id AS emp_num,
fname AS first,
minit AS middle,
lname AS last,
IDENTITY(smallint, 100, 1) AS job_num,
job_lvl AS job_level,
pub_id,
hire_date
INTO employees
FROM employee
GO
USE pubs
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'
|||What version of SQL Server are you using? In SQL Server 2005, you can use OUTPUT clause in INSERT statement to get the generated identity values for multiple rows easily. See the link below for a post that describes one example:
http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx
You can do the same in older versions by dumping the key values into a temporary table created in the SP from the trigger and accessing it from outside. This will still be much more efficient than what you are doing and run many times faster. And you kinda lost me on why you ned the declare list etc. You can do the looping without cursors.
|||I'm using 2005. I need more than just the newly generated identity values...I also need the original identity values from the records being copied. Like:
old_ID new_ID
4 227
63 228
65 229
I need this "pairing" of old and new, because I am copying both "parents" and "children" of 1-to-many relationships. To fetch and copy the children, I need to know the parent's "copy from" old id (to get the children) and the parent's "copy to" new id (to correlate the new copies of children to the new parent ids).
So I don't really care to explicitly "handle" each and every column of the parent - I don't care what the contents of those columns are - I just want to copy them. So it is really annoying to have an "INTO" clause where I've got a comma delimited list of 40 columns...which I only ever intend on copying without ever inspecting. The only thing I am "inspecting" is the values of the identity (primary key) of the parent.
Perhaps SQL Server 2005 has a "do what I mean" stored proc I can execute...
Ok. This is kind of tricky with OUTPUT clause and INSERT because you can only reference INSERTED table columns / expressions. You can however do it easily using a technique like below:
create table T ( i int not null identity primary key, j int null references T(i));
insert into T (j) values(null) ;
insert into T (j) values(scope_identity());
select * from T;
declare @.t table(i int not null);
set transaction isolation level serializable;
begin tran;
insert into T (j)
output inserted.i into @.t( i)
select j from T order by i;
select t2.i as old_i, t1.i as new_i
from (select i, ROW_NUMBER() OVER(order by i) from @.t) as t1(i, seq)
join (select i, ROW_NUMBER() OVER(order by i) from T) as t2(i, seq)
on t1.seq = t2.seq;
commit;
select * from T;
drop table T;
The trick is to insert the rows into the table in a particular order (you can choose multiple columns if you want). In the code above, the ORDER BY in the SELECT statement of INSERT ensures that the generated identity values are in the same order. You can then sequence the old and newly generated values & join based on the sequence. The serializable transaction is however required since it is not easy to protect the identity values generation in case of concurrent inserts to the table. This technique will work for you if this operation is an expensive one (replicating portions of tree) and you are doing it infrequently. This set-based approach should be much simpler than what you have but there is nothing in the SQL language to simplify column lists. You have to specify those you want to SELECT or use. That is how the language is defined.
|||Wow, that is quite the solution. I think I need just a wee more help...since I am so rusty/inexperienced with T/SQL.
Firstly, you have a "select * from T" statement near the top of your script that doesn't seem to do anything. Is it just a piece of debug/sanity check output - or is it necessary for the solution?
Secondly, it seems to me that when I turn my attention towards copying records in the child table - I still might need to resort to explicit looping while using your "old-to-new" correlation query. That is, after I copy the child's records, I will need to change the foreign key on each child row, to reflect the new foreign keys in the parent. There are perhaps two ways I can think of to avoid such explicit looping:
1) If the child table also has an identity column (primary key), then I can repeat the magical "old-to-new" correlation query for the child table's insert as well. Then, after the insert (copy) on the child is done, I would do an update on the child's newly inserted rows - predicated on a 3-way inner join between the parent's "old-to-new" recordset, the child's "old-to-new" recordset, and the child itself. Phew!!!
2) After duplicating the relevant child records in the child, I would need to use a facility in SQL Server 2005 that allows me to perform an update only on the newly added child records. Perhaps this just means using the "inserted" virtual table again. As such, I perceive a simpler variation of proposal (1) above. That is, I "save" the contents of the "inserted" rows into a temp table. Then I perform an update predicated on a 3-way inner join between the parent's "old-to-new," the "inserted" temp table, and the child itself. This still supposes that the child has its own identity (primary key) column.
Even if you concur with either of the above strategies, is there an alternative you prefer?
|||The main logic is the part between the declare table and the commit tran. The rest of the code was just to show the rows. I didn't quite understand the part about copying child rows. Please post a simple DDL and data like in my example. And also the expected results so it will be easier to suggest a modified solution or show how my previous example can be used.|||I made the effort to implement the rest of what I needed, and discovered that producing copies of the child records was nothing as difficult as I anticipated. I did not need a unique primary key in the children, and I did not need looping. Just an insert statement predicated on a simple join. Thats all.
So, in short, you have shown me how to produce all the copies I needed, without using looping structures. Therefore I will mark your primary response as the answer.
. But when the delete action is held up by sorts on three non