I need to Create procedure that will
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.
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;