Create procedure that will insert or update data from one temporary table to other 4 tables

Question

Create procedure that will insert or update data from one temporary table to other 4 tables

database sqlbulkcopy sql-server stored-procedures

Question

I need to Create procedure that will

insert or update data from the source table into the target1 table
Insert data into target2 table AND will get inserted ID from target1, target2 tables
Insert them into target table3
Insert target1.ID into target4 table

the source table is a temporary table, and I use bulk copy to insert big data into that table and then move data to target tables for performance.

MSPCompanies - insert or update Ð¿Ð¾ Ð¿Ñ€Ð¸Ð·Ð½Ð°ÐºÑƒ INN
MSPDownloads insert
MSPAccepted insert MSPCompanies.ID Ð¸ MSPDownloads.ID
MSPAcceptedNow insert MSPCompanies.ID

insert or update in MSPCompanies I have done with MERGE

BEGIN
 BEGIN TRY
  BEGIN TRANSACTION
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
 MERGE INTO [MSPCompanies] AS [Target]
USING [MSPTempTable] AS [Source]
 ON Target.INN = Source.INN
WHEN MATCHED THEN
UPDATE SET 
 Target.Name = Source.Name,
 Target.ShortName = Source.ShortName,
 Target.FIO = Source.FIO,
 Target.SubjectType = Source.SubjectType,
 Target.SubjectCategory = Source.SubjectCategory,
 @MSPCompaniesId = Target.MSP_Companies_ID

WHEN NOT MATCHED THEN
INSERT 
 (INN, Name, ShortName, FIO, SubjectType, SubjectCategory) 
VALUES 
 (Source.INN, Source.Name, Source.ShortName, Source.FIO, Source.SubjectType, Source.SubjectCategory);

IF @MSPCompaniesId IS NULL
BEGIN
    SET @MSPCompaniesId = CAST(SCOPE_IDENTITY() as [int]);
END
SELECT @MSPCompaniesId
--BEGIN
INSERT INTO dbo.MSPAcceptedNow
(MSP_Company_ID)
VALUES(@MSPCompaniesId)

but after it seems that can't do other operations with oterh three tables, and trying to do it with insert - update, but anyway can't do that.

BEGIN TRANSACTION MoveDataFromMSPTempTableT; 
UPDATE t
    SET 
  Name = s.Name,
  ShortName = s.ShortName,
  FIO = s.FIO,
  SubjectType = s.SubjectType,
  SubjectCategory = s.SubjectCategory
    FROM MSPCompanies t
        JOIN MSPTempTable s
        ON s.INN = t.INN;

INSERT INTO MSPCompanies
        SELECT s.INN,
               s.Name,
      s.ShortName,
      s.FIO,
      s.SubjectType,
      s.SubjectCategory
            FROM MSPTempTable s
                LEFT JOIN MSPCompanies t
                ON s.INN = t.INN
            WHERE t.INN IS NULL;

COMMIT TRANSACTION MoveDataFromMSPTempTableT;

Thank you.

user5583732

View more on Stack Overflow

ildanny · Accepted Answer · 1/17/2018 2:30:38 PM

You cannot use a variable to track all the changes, but you track all the changes with OUTPUT.

if object_id('dbo.[MSPCompanies]') is not null drop table dbo.[MSPCompanies];
if object_id('dbo.[MSPTempTable]') is not null drop table dbo.[MSPTempTable];

create table dbo.[MSPTempTable]
(
    MSP_Companies_ID    int identity(1,1),
    INN                 varchar(5) primary key clustered,
    [Name]              varchar(20),
    ShortName           varchar(20),
    FIO                 varchar(20),
    SubjectType         varchar(20),
    SubjectCategory     varchar(20)
);

insert into dbo.[MSPTempTable] (inn,
 [Name]         
,ShortName      
,FIO            
,SubjectType    
,SubjectCategory) values ('a','a','a','a','a','a'),     ('a2','a2','a2','a2','a2','a2');

create table dbo.[MSPCompanies]
(
    MSP_Companies_ID    int identity(1,1),
    INN                 varchar(5) primary key clustered,
    [Name]              varchar(20),
    ShortName           varchar(20),
    FIO                 varchar(20),
    SubjectType         varchar(20),
    SubjectCategory     varchar(20)
);

insert into dbo.[MSPCompanies] (inn,
 [Name]         
,ShortName      
,FIO            
,SubjectType    
,SubjectCategory) values ('a','a','a','a','a','a');

begin tran
set xact_abort on;

SET NOCOUNT ON;

 DECLARE @SummaryOfChanges TABLE(
    CompanyID int,
    Change VARCHAR(20)
 ); 


 MERGE INTO [MSPCompanies] AS [Target]
    USING [MSPTempTable] AS [Source]
        ON Target.MSP_Companies_ID = Source.MSP_Companies_ID
    WHEN MATCHED THEN
        UPDATE SET 
             Target.Name = Source.Name,
             Target.ShortName = Source.ShortName,
             Target.FIO = Source.FIO,
             Target.SubjectType = Source.SubjectType,
             Target.SubjectCategory = Source.SubjectCategory
    WHEN NOT MATCHED THEN
        INSERT  (INN, Name, ShortName, FIO, SubjectType, SubjectCategory) 
        VALUES  (Source.INN, Source.Name, Source.ShortName, Source.FIO,     Source.SubjectType, Source.SubjectCategory)

    OUTPUT inserted.MSP_Companies_ID, $action INTO @SummaryOfChanges;

SELECT * from dbo.[MSPCompanies]
SELECT * from @SummaryOfChanges;

if @@TRANCOUNT > 0 
    rollback tran;

Create procedure that will insert or update data from one temporary table to other 4 tables

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator