1

I am trying insert 1 row into an audit table for every row in a temporary table and store the ID of each audit table row in the temporary table.

The columns inserted into the audit table will come from the rows in the temporary table and I will need to refer back to the audit.id via the temporary table (so they need to match correctly)

I do not want to use any loops.

Kirk
  • 19
  • 1
  • Does your temporary table have and `Id` as well? – SqlZim Apr 07 '17 at 17:05
  • The temporary table has a unique (PK) id and has an (FK) id for the Audit.ID – Kirk Apr 07 '17 at 17:08
  • 1
    You really need to show us the data model and example data before an after -- very hard to know what you are asking – Hogan Apr 07 '17 at 17:13
  • Possible duplicate of [How to insert multiple records and get the identity value?](http://stackoverflow.com/questions/95988/how-to-insert-multiple-records-and-get-the-identity-value) – Tab Alleman Apr 07 '17 at 18:38
  • Instead of the accepted answer in the duplicate, look at the most highly upvoted answer by Andy Irving. – Tab Alleman Apr 07 '17 at 18:39
  • 1) Not sure whether I should edit the original question details or not? I have a large number of rows to delete from a primary table. I need to record the deleted fields into an Audit Item table. (1 row for every field in every row of the temp table). Before I can record the fields in the Audit Item, I need to create the Audit Header tables and insert the new Audit IDs into the temp table. I have no control over the way the auditing works on this database. – Kirk Apr 11 '17 at 13:09
  • 2) So far (using SqlZim's suggestion) I have the IDs I need in a tempory table called Output I now need to get the Audit.IDs from Output into the FKAudit field in my temp table. It doesn't matter which ID goes into which row (as long as each ID only appears once). Once I have the FKAudit field updated I can easily update the Audit and Audit_Item tables in the format required and delete my data. Maybe my Q now is how do I insert 1 ID from each row in Output into the temporary table without any way to explicitly join the two by the data they contain? – Kirk Apr 11 '17 at 13:17
  • 3) Or is there a way to output the Audit ID straight into my temp table as I am using the temp table to determine how many Audit IDs I am generating? – Kirk Apr 11 '17 at 13:17

1 Answers1

2

Without any example tables, data, and desired results, here is my attempt at a solution using output and a table variable.

create table t (id int not null identity(1,1), val varchar(32), audit_id int null);
insert into t (val) values ('three'),('two'),('one');
create table audit (id int not null identity(1,1), val varchar(32));

/* table variable for output */
declare @output table (id int, val varchar(32));

/* insert with output */
insert into audit (val)
output inserted.id, inserted.val into @output
select val
from t;

/* updated t from output */
update t 
set audit_id = o.id
from t
  inner join @output o
    on t.val = o.val;

select * from audit;
select * from t;

rextester demo: http://rextester.com/JMOT34416

for the audit table, returns:

+----+-------+
| id |  val  |
+----+-------+
|  1 | three |
|  2 | two   |
|  3 | one   |
+----+-------+

and for the temporary table t

+----+-------+----------+
| id |  val  | audit_id |
+----+-------+----------+
|  1 | three |        1 |
|  2 | two   |        2 |
|  3 | one   |        3 |
+----+-------+----------+
SqlZim
  • 37,248
  • 6
  • 41
  • 59
  • I think that will do what I want but I will need to play around with it (and translate it to my real tables). Many thanks SQLZim for such a prompt response. – Kirk Apr 07 '17 at 17:26