Insert within Select Statement
-
Hi All, I have a situation that I have 3 tables like TabA, TabB and TabC, I have to insert into TabC by using a select statement from TabB but one value I have to take from Scope_Identity from inserting a row into TabA. So my Insert should look like below. Insert into TabC (Col1, Col2, TabAId) Select Col1, Col2, (Insert into TabA(CurrentDate) Values (DateTime) Scope_Identity) FROM TabB. So the Scope_Identity of the TabA insert should be used in the Select. Do we have any way to do it? Any sort of advice, code snippet and link is greatly helpful. Thanks in advance.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Hi All, I have a situation that I have 3 tables like TabA, TabB and TabC, I have to insert into TabC by using a select statement from TabB but one value I have to take from Scope_Identity from inserting a row into TabA. So my Insert should look like below. Insert into TabC (Col1, Col2, TabAId) Select Col1, Col2, (Insert into TabA(CurrentDate) Values (DateTime) Scope_Identity) FROM TabB. So the Scope_Identity of the TabA insert should be used in the Select. Do we have any way to do it? Any sort of advice, code snippet and link is greatly helpful. Thanks in advance.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
You can't embed an
INSERTstatement within aSELECTstatement. Try something like this:DECLARE @TabAId int;
INSERT INTO TabA (CurrentDate) VALUES (GetUtcDate());
SET @TabAId = Scope_Identity();INSERT INTO TabC (Col1, Col2, TabAId)
SELECT Col1, Col2, @TabAId FROM TabB;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
You can't embed an
INSERTstatement within aSELECTstatement. Try something like this:DECLARE @TabAId int;
INSERT INTO TabA (CurrentDate) VALUES (GetUtcDate());
SET @TabAId = Scope_Identity();INSERT INTO TabC (Col1, Col2, TabAId)
SELECT Col1, Col2, @TabAId FROM TabB;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
This works if we have only one TabAId, but we need multiple TabAIds like different TabAIds for every row that SELECT Col1, Col2, @TabAId FROM TabB; generates, thats my problem. Can you help me pls? Even if I can use Insert withing Select that's also fine only thing if I can retrieve that Scope_Identity in selected rows.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
You can't embed an
INSERTstatement within aSELECTstatement. Try something like this:DECLARE @TabAId int;
INSERT INTO TabA (CurrentDate) VALUES (GetUtcDate());
SET @TabAId = Scope_Identity();INSERT INTO TabC (Col1, Col2, TabAId)
SELECT Col1, Col2, @TabAId FROM TabB;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Or even if I can do it in the following way but I am getting errors in the below query, it is saying its not able to recognize the columns, htge.[Name], htge.[Description]. Can I get those columns into Table variable some how? It will fix all my problems.
DECLARE @MyTableVar table (DocumentListId int, [Name] varchar(max), [Description] varchar(max));
INSERT INTO dbo.DocumentList (CreatedDate,CreatedBy,ModifiedDate,ModifiedBy)
OUTPUT INSERTED.DocumentListId,
htge.[Name],
htge.[Description]
INTO @MyTableVar
SELECT GETDATE() ,CURRENT_USER,GETDATE(),CURRENT_USER, htge.[Name], htge.[Description]
FROM OPENROWSET('SQLNCLI', 'Server=xxx;UID=xx;PWD=xxx',
'SELECT * FROM WEB_WebAdmin.dbo.HealthTopicGroup where HealthTopicGroupId < 45') as htge;SELECT * frOM @MyTableVar
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Hi All, I have a situation that I have 3 tables like TabA, TabB and TabC, I have to insert into TabC by using a select statement from TabB but one value I have to take from Scope_Identity from inserting a row into TabA. So my Insert should look like below. Insert into TabC (Col1, Col2, TabAId) Select Col1, Col2, (Insert into TabA(CurrentDate) Values (DateTime) Scope_Identity) FROM TabB. So the Scope_Identity of the TabA insert should be used in the Select. Do we have any way to do it? Any sort of advice, code snippet and link is greatly helpful. Thanks in advance.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
You could use an onInsert trigger spit to insert the new records into TabC. Caveat an error on the trigger is a bitch to locate in the future.
Never underestimate the power of human stupidity RAH
