Get ID of inserted & Selected row after multiple insert [duplicate]
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
1
This question already has an answer here:
Insert Into… Merge… Select (SQL Server)
1 answer
Combine OUTPUT inserted.id with value from selected row
2 answers
I have a query running in loop which I am trying to optimize as this
INSERT INTO myTable (col1, col2, col3)
OUTPUT inserted.id, SOURCE_ROW_ID_NEEDED_HERE
SELECT col1, col2, col3
FROM myTable
WHERE col2 = 20 --any value
My problem is : col2 = 20 can have N number of rows which get inserted, I need the id of the source row for the new record. For example say there are 3 rows for col2 = 20 and id of them are 11,12,15. The new inserted ID are say 150,151,152.
I would need
11 150
12 151
15 152
sql
sql-server tsql edited Nov 26 '18 at 19:20
marc_s
584k13011251272
asked Nov 26 '18 at 18:33
CFML_DeveloperCFML_Developer
1,095516
marked as duplicate by Zohar Peled sql
Users with the sql badge can single-handedly close sql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 27 '18 at 7:59
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
|
show 4 more comments
1
This question already has an answer here:
Insert Into… Merge… Select (SQL Server)
1 answer
Combine OUTPUT inserted.id with value from selected row
2 answers
I have a query running in loop which I am trying to optimize as this
INSERT INTO myTable (col1, col2, col3)
OUTPUT inserted.id, SOURCE_ROW_ID_NEEDED_HERE
SELECT col1, col2, col3
FROM myTable
WHERE col2 = 20 --any value
My problem is : col2 = 20 can have N number of rows which get inserted, I need the id of the source row for the new record. For example say there are 3 rows for col2 = 20 and id of them are 11,12,15. The new inserted ID are say 150,151,152.
I would need
11 150
12 151
15 152
sql
sql-server tsql edited Nov 26 '18 at 19:20
marc_s
584k13011251272
asked Nov 26 '18 at 18:33
CFML_DeveloperCFML_Developer
1,095516
marked as duplicate by Zohar Peled sql
Users with the sql badge can single-handedly close sql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 27 '18 at 7:59
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
You can only put columns inoutputthat are in the records being inserted into the new table. So if theidis being inserted, you can include it in the output.
– Gordon Linoff
Nov 26 '18 at 18:37
@GordonLinoff, thanks. I reckon that would be the case. Any hack to achieve desired result?
– CFML_Developer
Nov 26 '18 at 18:40
You can probably do it with a merge statement
– Hogan
Nov 26 '18 at 18:44
does the source table have an id column? If so, include it in the insert statement and output it via the output clause
– user1443098
Nov 26 '18 at 18:47
You might be able to use this existing answer and go from there: stackoverflow.com/questions/35687950/…
– TTeeple
Nov 26 '18 at 18:48
|
show 4 more comments
1
1
1
This question already has an answer here:
Insert Into… Merge… Select (SQL Server)
1 answer
Combine OUTPUT inserted.id with value from selected row
2 answers
I have a query running in loop which I am trying to optimize as this
INSERT INTO myTable (col1, col2, col3)
OUTPUT inserted.id, SOURCE_ROW_ID_NEEDED_HERE
SELECT col1, col2, col3
FROM myTable
WHERE col2 = 20 --any value
My problem is : col2 = 20 can have N number of rows which get inserted, I need the id of the source row for the new record. For example say there are 3 rows for col2 = 20 and id of them are 11,12,15. The new inserted ID are say 150,151,152.
I would need
11 150
12 151
15 152
sql
sql-server tsql edited Nov 26 '18 at 19:20
marc_s
584k13011251272
asked Nov 26 '18 at 18:33
CFML_DeveloperCFML_Developer
1,095516
This question already has an answer here:
Insert Into… Merge… Select (SQL Server)
1 answer
Combine OUTPUT inserted.id with value from selected row
2 answers
I have a query running in loop which I am trying to optimize as this
INSERT INTO myTable (col1, col2, col3)
OUTPUT inserted.id, SOURCE_ROW_ID_NEEDED_HERE
SELECT col1, col2, col3
FROM myTable
WHERE col2 = 20 --any value
My problem is : col2 = 20 can have N number of rows which get inserted, I need the id of the source row for the new record. For example say there are 3 rows for col2 = 20 and id of them are 11,12,15. The new inserted ID are say 150,151,152.
I would need
11 150
12 151
15 152
This question already has an answer here:
Insert Into… Merge… Select (SQL Server)
1 answer
Combine OUTPUT inserted.id with value from selected row
2 answers
sql
sql-server tsql sql
sql-server tsql edited Nov 26 '18 at 19:20
marc_s
584k13011251272
asked Nov 26 '18 at 18:33
CFML_DeveloperCFML_Developer
1,095516
edited Nov 26 '18 at 19:20
marc_s
584k13011251272
asked Nov 26 '18 at 18:33
CFML_DeveloperCFML_Developer
1,095516
edited Nov 26 '18 at 19:20
marc_s
584k13011251272
edited Nov 26 '18 at 19:20
marc_s
584k13011251272
edited Nov 26 '18 at 19:20
marc_s
584k13011251272
584k13011251272
asked Nov 26 '18 at 18:33
CFML_DeveloperCFML_Developer
1,095516
asked Nov 26 '18 at 18:33
CFML_DeveloperCFML_Developer
1,095516
asked Nov 26 '18 at 18:33
CFML_DeveloperCFML_Developer
1,095516
1,095516
marked as duplicate by Zohar Peled sql
Users with the sql badge can single-handedly close sql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 27 '18 at 7:59
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by Zohar Peled sql
Users with the sql badge can single-handedly close sql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 27 '18 at 7:59
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
You can only put columns inoutputthat are in the records being inserted into the new table. So if theidis being inserted, you can include it in the output.
– Gordon Linoff
Nov 26 '18 at 18:37
@GordonLinoff, thanks. I reckon that would be the case. Any hack to achieve desired result?
– CFML_Developer
Nov 26 '18 at 18:40
You can probably do it with a merge statement
– Hogan
Nov 26 '18 at 18:44
does the source table have an id column? If so, include it in the insert statement and output it via the output clause
– user1443098
Nov 26 '18 at 18:47
You might be able to use this existing answer and go from there: stackoverflow.com/questions/35687950/…
– TTeeple
Nov 26 '18 at 18:48
|
show 4 more comments
You can only put columns inoutputthat are in the records being inserted into the new table. So if theidis being inserted, you can include it in the output.
– Gordon Linoff
Nov 26 '18 at 18:37
@GordonLinoff, thanks. I reckon that would be the case. Any hack to achieve desired result?
– CFML_Developer
Nov 26 '18 at 18:40
You can probably do it with a merge statement
– Hogan
Nov 26 '18 at 18:44
does the source table have an id column? If so, include it in the insert statement and output it via the output clause
– user1443098
Nov 26 '18 at 18:47
You might be able to use this existing answer and go from there: stackoverflow.com/questions/35687950/…
– TTeeple
Nov 26 '18 at 18:48
You can only put columns in
output that are in the records being inserted into the new table. So if the id is being inserted, you can include it in the output.– Gordon Linoff
Nov 26 '18 at 18:37
You can only put columns in
output that are in the records being inserted into the new table. So if the id is being inserted, you can include it in the output.– Gordon Linoff
Nov 26 '18 at 18:37
@GordonLinoff, thanks. I reckon that would be the case. Any hack to achieve desired result?
– CFML_Developer
Nov 26 '18 at 18:40
@GordonLinoff, thanks. I reckon that would be the case. Any hack to achieve desired result?
– CFML_Developer
Nov 26 '18 at 18:40
You can probably do it with a merge statement
– Hogan
Nov 26 '18 at 18:44
You can probably do it with a merge statement
– Hogan
Nov 26 '18 at 18:44
does the source table have an id column? If so, include it in the insert statement and output it via the output clause
– user1443098
Nov 26 '18 at 18:47
does the source table have an id column? If so, include it in the insert statement and output it via the output clause
– user1443098
Nov 26 '18 at 18:47
You might be able to use this existing answer and go from there: stackoverflow.com/questions/35687950/…
– TTeeple
Nov 26 '18 at 18:48
You might be able to use this existing answer and go from there: stackoverflow.com/questions/35687950/…
– TTeeple
Nov 26 '18 at 18:48
|
show 4 more comments
1 Answer
1
active
oldest
votes
0
Are you looking for something like
CREATE TABLE T1(
Col1 INT IDENTITY(1, 1),
Col2 INT,
Col3 INT
);
CREATE TABLE T2(
Col1 INT IDENTITY(1, 1),
Col2 INT,
Col3 INT
);
INSERT INTO T2(Col2, Col3) VALUES
(11, 150),
(12, 151),
(15, 152);
DECLARE @TT TABLE (ID INT, Col2 INT);
SET IDENTITY_INSERT T1 ON;
INSERT INTO T1 (Col1, Col2, Col3)
OUTPUT INSERTED.Col1,
INSERTED.Col2
INTO @TT (ID, Col2)
SELECT Col1,
Col2,
Col3
FROM T2;
SELECT *
FROM @TT;
SET IDENTITY_INSERT T1 OFF;
Demo
answered Nov 26 '18 at 19:09
SamiSami
9,40331244
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Are you looking for something like
CREATE TABLE T1(
Col1 INT IDENTITY(1, 1),
Col2 INT,
Col3 INT
);
CREATE TABLE T2(
Col1 INT IDENTITY(1, 1),
Col2 INT,
Col3 INT
);
INSERT INTO T2(Col2, Col3) VALUES
(11, 150),
(12, 151),
(15, 152);
DECLARE @TT TABLE (ID INT, Col2 INT);
SET IDENTITY_INSERT T1 ON;
INSERT INTO T1 (Col1, Col2, Col3)
OUTPUT INSERTED.Col1,
INSERTED.Col2
INTO @TT (ID, Col2)
SELECT Col1,
Col2,
Col3
FROM T2;
SELECT *
FROM @TT;
SET IDENTITY_INSERT T1 OFF;
Demo
answered Nov 26 '18 at 19:09
SamiSami
9,40331244
add a comment |
0
Are you looking for something like
CREATE TABLE T1(
Col1 INT IDENTITY(1, 1),
Col2 INT,
Col3 INT
);
CREATE TABLE T2(
Col1 INT IDENTITY(1, 1),
Col2 INT,
Col3 INT
);
INSERT INTO T2(Col2, Col3) VALUES
(11, 150),
(12, 151),
(15, 152);
DECLARE @TT TABLE (ID INT, Col2 INT);
SET IDENTITY_INSERT T1 ON;
INSERT INTO T1 (Col1, Col2, Col3)
OUTPUT INSERTED.Col1,
INSERTED.Col2
INTO @TT (ID, Col2)
SELECT Col1,
Col2,
Col3
FROM T2;
SELECT *
FROM @TT;
SET IDENTITY_INSERT T1 OFF;
Demo
answered Nov 26 '18 at 19:09
SamiSami
9,40331244
add a comment |
0
0
0
Are you looking for something like
CREATE TABLE T1(
Col1 INT IDENTITY(1, 1),
Col2 INT,
Col3 INT
);
CREATE TABLE T2(
Col1 INT IDENTITY(1, 1),
Col2 INT,
Col3 INT
);
INSERT INTO T2(Col2, Col3) VALUES
(11, 150),
(12, 151),
(15, 152);
DECLARE @TT TABLE (ID INT, Col2 INT);
SET IDENTITY_INSERT T1 ON;
INSERT INTO T1 (Col1, Col2, Col3)
OUTPUT INSERTED.Col1,
INSERTED.Col2
INTO @TT (ID, Col2)
SELECT Col1,
Col2,
Col3
FROM T2;
SELECT *
FROM @TT;
SET IDENTITY_INSERT T1 OFF;
Demo
answered Nov 26 '18 at 19:09
SamiSami
9,40331244
Are you looking for something like
CREATE TABLE T1(
Col1 INT IDENTITY(1, 1),
Col2 INT,
Col3 INT
);
CREATE TABLE T2(
Col1 INT IDENTITY(1, 1),
Col2 INT,
Col3 INT
);
INSERT INTO T2(Col2, Col3) VALUES
(11, 150),
(12, 151),
(15, 152);
DECLARE @TT TABLE (ID INT, Col2 INT);
SET IDENTITY_INSERT T1 ON;
INSERT INTO T1 (Col1, Col2, Col3)
OUTPUT INSERTED.Col1,
INSERTED.Col2
INTO @TT (ID, Col2)
SELECT Col1,
Col2,
Col3
FROM T2;
SELECT *
FROM @TT;
SET IDENTITY_INSERT T1 OFF;
Demo
answered Nov 26 '18 at 19:09
SamiSami
9,40331244
answered Nov 26 '18 at 19:09
SamiSami
9,40331244
answered Nov 26 '18 at 19:09
SamiSami
9,40331244
answered Nov 26 '18 at 19:09
SamiSami
9,40331244
9,40331244
add a comment |
add a comment |
You can only put columns in
outputthat are in the records being inserted into the new table. So if theidis being inserted, you can include it in the output.– Gordon Linoff
Nov 26 '18 at 18:37
@GordonLinoff, thanks. I reckon that would be the case. Any hack to achieve desired result?
– CFML_Developer
Nov 26 '18 at 18:40
You can probably do it with a merge statement
– Hogan
Nov 26 '18 at 18:44
does the source table have an id column? If so, include it in the insert statement and output it via the output clause
– user1443098
Nov 26 '18 at 18:47
You might be able to use this existing answer and go from there: stackoverflow.com/questions/35687950/…
– TTeeple
Nov 26 '18 at 18:48