How to insert to multiple table from a linked server view?

Question

I am new to sql and hope anyone can help me with this question.

we have a local database called HR-Master and a linked server called SPU . There is three tables in our local database namely EMP, REMEMP and EMPIC and one view created from the linked server called EmployeepassView

EMP

ID,LASTNAME,FIRST NAME, MIDNAME,COMID,LASTCHANGED, PERMISSIONS, ALLOWEDPERMISSIONS,ASSET_GROUPID,TR_DBID,QUED,SEGMENTID -

REMEMP

ID ,*JOBROLE,*NATIONALITY ,*PERMITAREAS,DEPT , *ISSUEDATE,*REFID,*TEAMINCHARGE 
 *MAINROLE,*SUBROLE,*ROLENAME,*ROLEID, EXPIRYDATE

EMPPIC

EMPID,OBJECT,TYPE ,*EMP_BLOB,*LASTCHANGED,ACCEPTANCETHRESHOLD, BIO_BODYPAR -

Source

EmployeepassView

([COMID],[lastName],[Name_En],[PERTYPE],[REFID],[JobTitle],[Nationality],[PERMITAreas],[IssueDate],[ExpiryDate],[TeamInCharge],[MainRole] ,[SubRolE],[RoleName],[ROLEID],[Picture],[isDisable] ,[LastPersonPrint],[LastPrintDate],[NoOfTimesPrinted],[LastUpdated],

I want to copy the values from my views and put it in the above three tables like this

     INSERT dbo.emp ([LASTNAME],[COMID)],[LASTCHANGED])
        INSERT dbo.rememp ([jobrole],[nationality)],[PERMITAREAS], [ISSUEDATE],[REFID],[TEAMINCHARGE], [MAINROLE],[SUBROLE],[ROLENAME],[ROLEID])
        INSERT dbo.emppic ([LASTNAME],[SSNO)],[LASTCHANGED])
    select ([LASTNAME],[COMID)],[LASTCHANGED],[jobrole],[nationality],[PERMITAREAS], [ISSUEDATE],[REFID],[TEAMINCHARGE], [MAINROLE],[SUBROLE],[ROLENAME],[ROLEID],[LASTNAME],[SSNO)],[LASTCHANGED])
from EmployeepassView as ET
where ET.COMID='1234'

Using this above query. How can I do this in a single query?Please help

You can't. SQL Server only allows you to insert into one table in an `insert` statement. — Gordon Linoff, Aug 08 '17 at 10:55
You cannot insert into multiple tables using 1 query. It have to be 3 separate insert statements — Kashif Qureshi, Aug 08 '17 at 10:57

JMabee · Accepted Answer · 2017-08-09T11:40:32.877

0

You would have to do three separate inserts:

If I understand your comment about inserting ID's I am going to assume the EMPID comes from the dbo.emp table. So I would do the following:

EDITED FOR STORED PROC:

CREATE PROCEDURE InsertStuff @ID int
AS

INSERT dbo.emp ([ID],[LASTNAME],[COMID)],[LASTCHANGED])
 select @ID,[LASTNAME],[COMID)],[LASTCHANGED] from EmployeepassView as ET
 where ET.COMID='1234'

        INSERT dbo.rememp 
SELECT (@ID,[jobrole],[nationality)],[PERMITAREAS], [ISSUEDATE],[REFID],[TEAMINCHARGE], [MAINROLE],[SUBROLE],[ROLENAME],[ROLEID])
select [jobrole],[nationality],[PERMITAREAS], [ISSUEDATE],[REFID],[TEAMINCHARGE], [MAINROLE],[SUBROLE],[ROLENAME],[ROLEID],[LASTNAME],[SSNO)],[LASTCHANGED]) from EmployeepassView as ET
where ET.COMID='1234'

INSERT dbo.emppic ([EMPID],[LASTNAME],[SSNO)],[LASTCHANGED])
select @ID, [LASTNAME],[COMID)],[LASTCHANGED] from EmployeepassView as ET
where ET.COMID='1234'

edited Aug 09 '17 at 11:40

answered Aug 08 '17 at 11:09

JMabee

2,230
2
9
13

there is an ID column common and local to all three tables How can i insert ID here. – aryan Aug 08 '17 at 11:46
Where is the ID generated from, i.e. is it an identity column or does it come from somewhere else? Is it shared across all three tables? – JMabee Aug 08 '17 at 11:51
those three empid is showing as : (PK,int,not null ) – aryan Aug 08 '17 at 12:19
How to pass ID value for the above – aryan Aug 08 '17 at 21:52
on set @ID=" input value", the first insert is successful but the second insert is not updating the second table and giving an ID null error – aryan Aug 09 '17 at 06:58
Well your code is not clear at all so I was assuming the first table had an identity field for the ID. If not then SCOPE_IDENTITY will return a null. Set ID = "input value" or whatever value you will be using for ID and then use ID in the insert statement like I showed you above. – JMabee Aug 09 '17 at 10:27
Rather than repeating ID and COMID, is it possible to create a store procedure and receive input for both ID and COMID . – aryan Aug 09 '17 at 10:44
Edited the answer – JMabee Aug 09 '17 at 11:40

How to insert to multiple table from a linked server view?

1 Answers1