November 9, 2016 at 2:24 pm
i have to check if record exists in table , if there is record then update else insert.
Stg_table
Id seq name company
1 1 aaa yyy
1 2 aaa bbb
table
Id seq name company
1 1 aaa yyy
1 2 aaa bbb
now I have another row in staging with sequence 3
Stg_table
Id seq name company
1 1 aaa yyy
1 2 aaa bbb
1 3 aaa www
I have to check if this exists in table, if it does not exist only then insert. I cannot use MERGE as it is giving me trigger issues and I tried using if exists(below is my code)
If exists(select 1 from stg_table s
join table t
on s.id=t.id and t.seq=s.seq )
begin
update -----
end
else
begin
insert -----
end
How ever, the records are going as updates because it is satisfying the condition. How do i insert in such cases?
table
Id seq name company
1 1 aaa yyy
1 2 aaa bbb
any help is appreciated
November 9, 2016 at 2:38 pm
hegdesuchi (11/9/2016)
i have to check if record exists in table , if there is record then update else insert.Stg_table
Id seq name company
1 1 aaa yyy
1 2 aaa bbb
table
Id seq name company
1 1 aaa yyy
1 2 aaa bbb
now I have another row in staging with sequence 3
Stg_table
Id seq name company
1 1 aaa yyy
1 2 aaa bbb
1 3 aaa www
I have to check if this exists in table, if it does not exist only then insert. I cannot use MERGE as it is giving me trigger issues and I tried using if exists(below is my code)
If exists(select 1 from stg_table s
join table t
on s.id=t.id and t.seq=s.seq )
begin
update -----
end
else
begin
insert -----
end
How ever, the records are going as updates because it is satisfying the condition. How do i insert in such cases?
table
Id seq name company
1 1 aaa yyy
1 2 aaa bbb
any help is appreciated
Without knowledge of what the trigger issue is, we might well run into the same problem with any other solution. We also need to know what fields constitute making a record unique in the table. I presume it's a combination of Id and seq, but you can confirm or reject that hypothesis.
Steve (aka sgmunson) š š š
Rent Servers for Income (picks and shovels strategy)
November 9, 2016 at 2:39 pm
hegdesuchi (11/9/2016)
i have to check if record exists in table , if there is record then update else insert....
If exists(select 1 from stg_table s
join table t
on s.id=t.id and t.seq=s.seq )
begin
update -----
end
else
begin
insert -----
end
It looks like your EXISTS subquery will check if ANY of the rows in stg_table s are in table t, then doing an UPDATE of all of them. What you would need here is get rid of the IF ELSE flow control logic, just an UPDATE statement, followed by an INSERT SELECT statement that has a WHERE clause to only add records that don't exist.
You may want to consider the MERGE TSQL statement as an alternative:
November 9, 2016 at 2:46 pm
You mean something like this?
create table dbo.StgTable (
Id int,
Seq int,
Name varchar(16),
Company varchar(16)
);
create table dbo.TgtTable (
Id int,
Seq int,
Name varchar(16),
Company varchar(16)
);
insert into dbo.TgtTable
values (1,1,'aaa','yyy'),
(1,2,'aaa','bbb');
insert into dbo.StgTable
values (1,1,'aaa','yyy'),
(1,2,'aaa','bbb'),
(1,3,'aaa','www');
update tt set
Name = st.Name
,Company = st.Company
from
dbo.TgtTable tt
inner join dbo.StgTable st
on tt.Id = st.Id and tt.Seq = st.Seq;
insert into dbo.TgtTable
select * from dbo.StgTable st
where not exists(select 1 from dbo.TgtTable tt1 where tt1.Id = st.Id and tt1.Seq = st.Seq);
select * from dbo.TgtTable;
drop table dbo.TgtTable; -- clean up my sandbox database
drop table dbo.StgTable; -- clean up my sandbox database
November 9, 2016 at 2:49 pm
Just based on that logic if any record in the your staging table matches anything in the target table very row in the staging table would be treated as an update. You would need to do something like,
UPDATE TARGET SET TARGET.name = STAGING.name, TARGET.company = STAGING.company FROM TARGET, STAGING WHERE TARGET.id = STAGING.id AND TARGET.seq = STAGING.seq
INSERT INTO target SELECT * FROM staging WHERE NOT EXISTS(SELECT 1 FROM TARGET WHERE TARGET.id = STAGING.id AND TARGET.seq = STAGING.seq)
November 9, 2016 at 2:53 pm
Here's an example:
CREATE TABLE StgTable(
Id int,
Seq int,
name varchar(10),
company varchar(10)
);
INSERT INTO StgTable
VALUES
( 1, 1, 'aaa', 'yyy'),
( 1, 2, 'aaa', 'bbb');
CREATE TABLE MyTable(
Id int,
Seq int,
name varchar(10),
company varchar(10)
);
GO
CREATE PROCEDURE Upsert_MyTable
AS
UPDATE t SET
name = s.name,
company = s.company
FROM MyTable t
JOIN StgTable s ON s.id = t.id AND t.seq = s.seq
--WHERE s.name <> t.name
--OR s.company <> t.company
;
INSERT INTO MyTable
SELECT *
FROM StgTable s
WHERE NOT EXISTS( SELECT 1
FROM MyTable t
WHERE s.id=t.id
AND t.seq=s.seq);
GO
EXEC Upsert_MyTable;
SELECT * FROM MyTable;
INSERT INTO StgTable
VALUES ( 1, 3, 'aaa', 'www');
UPDATE StgTable SET company = 'zzz'
WHERE Seq = 2;
EXEC Upsert_MyTable;
SELECT * FROM MyTable;
GO
DROP PROCEDURE Upsert_MyTable
DROP TABLE MyTable, StgTable;
EDIT: Lynn posted a very similar example while I was writing this one.
November 9, 2016 at 2:56 pm
ZZartin (11/9/2016)
Just based on that logic if any record in the your staging table matches anything in the target table very row in the staging table would be treated as an update. You would need to do something like,UPDATE TARGET SET TARGET.name = STAGING.name, TARGET.company = STAGING.company FROM TARGET, STAGING WHERE TARGET.id = STAGING.id AND TARGET.seq = STAGING.seq
INSERT INTO target SELECT * FROM staging WHERE NOT EXISTS(SELECT 1 FROM TARGET WHERE TARGET.id = STAGING.id AND TARGET.seq = STAGING.seq)
Only issue I have with this code is the SQL-89 style joins. I really prefer the SQL-92 style joins.
November 14, 2016 at 4:32 pm
Building on Lynn's good example here:: I added a couple of audit fields that you may readily see , 'created on' and 'updated on' dates. It is not difficult to handle these:
/* begin*/
Create table #StgTable (
Id int,
Seq int,
Name varchar(16),
Company varchar(16)
)
;
create table #TgtTable (
Id int,
Seq int,
Name varchar(16),
Company varchar(16),
CreatedOn datetime2(0),
lastUpdatedOn datetime2(0)
)
;
insert into #TgtTable
values (1,1,'aaa','yyy', getdate(),getdate()),
(1,2,'aaa','bbb', getdate(),getdate()),
(2,1,'ccc','ddd', getdate(),getdate());
insert into #StgTable
values (1,1,'aaa','yyy'),
(1,2,'aaa','bbc'),
(1,3,'aaa','www')
;
WAITFOR DELAY '00:00:10'; /* waits ten seconds after creation to do the update
and insert */
update tt
set
Name = st.Name
,Company = st.Company
,lastUpdatedOn = getdate() /*only this date audit field is modified
during an update*/
from
#TgtTable tt
inner join #StgTable ston tt.Id = st.Id and tt.Seq = st.Seq;
;
insertinto #TgtTable
select*, getdate(), getdate() /* the two getdates
represent created on and last updated on
fields being set for initialy loaded rows*/
from#StgTable st
wherenot exists(
select1
from#TgtTable tt1
wherett1.Id = st.Id and tt1.Seq = st.Seq
)
;
select * from #TgtTable
order by 1,2;
/* do at the end */
drop table #TgtTable;
drop table #StgTable;
/* end */
In the result set, you have to pay attention to the number after the last ":" in the date fields. The updates should be ten seconds apart from their created on dates.
----------------------------------------------------
November 15, 2016 at 12:17 pm
>> I have to check if record [sic] exists in table, if there is record [sic] then UPDATE else INSERT. <<
Please learn to post DDL, as per the forum rules. Please learn that a table has to have a key, and there is no such crap as a generic "_id" in RDBMS. Also things like "_name", "_seq", etc. are called attribute properties in data modeling. They have to have an attribute in front of them (read any book on basic data modeling or if you really like pain weighed through the ISO 11179 and metadata committee papers).
Also, rows are nothing at all like records. This is a fundamental concept and I am starting to see why you are having problems. You want to do a merge using procedural code in triggers, or worse.
Here is an attempt to write the DDL you fail to post.
CREATE TABLE Staged_Foobars
(something_seq INTEGER NOT NULL PRIMARY KEY,
foobar_name VARCHAR(5) NOT NULL,
company_name VARCHAR(5) NOT NULL);
INSERT INTO Staged_Foobars
VALUES
(1, 'aaa', 'yyy'),
(2, 'aaa', 'bbb');
CREATE TABLE Foobars
(something_seq INTEGER NOT NULL PRIMARY KEY,
foobar_name VARCHAR(5) NOT NULL,
company_name VARCHAR(5) NOT NULL);
>> now I have another row in staging with something_sequence = 3<<
The whole thing of having a staging table is how news mimic the old tape merges from the 1950's magnetic tape systems.
(3, 'aaa', āwwwā)
>> I have to check if this exists in table, if it does not exist only then insert. <<
No, another alternative is to try to insert a row and see if there is a duplicate, which violates the key constraint, catch the error and reject it.
>> I cannot use MERGE as it is giving me trigger issues and I tried using IF EXISTS(below is my code) <<
I just wrote an article under the title "Triggers: Threat or Menace?" explaining why we do not like using them. The short of it is that there procedural code in a declarative language.
Since we do not have any real code, or DDL or clear specs, we really can help you as much as we would like. My guess is you want to get rid of the triggers.
We can probably do some elaborate kludge with procedural code, but frankly, after 30+ years of doing this, I think you need to completely reconsider your design and make it look more like SQL and less like magnetic tapes.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
November 15, 2016 at 12:22 pm
You might want to warn him that the code you proposed does not work because of cardinality errors in the old Sybase UPDATE .. FROM .. WHERE. And of course it does not port. This is why MVP and us SQL purists want it removed and replaced by the merge statement.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
November 15, 2016 at 12:26 pm
>> I have to check if this exists in table, if it does not exist only then insert. <<
No, another alternative is to try to insert a row and see if there is a duplicate, which violates the key constraint, catch the error and reject it.
It think it is more efficient to discover the records needed for insertion ahead of the fact, rather than a row by row insert and see if it fails approach.
----------------------------------------------------
November 15, 2016 at 1:34 pm
If MERGE isn't an option, then perhaps something like the following (pseudo-code of course). This way, if the row does happen to exist, then it's a single lookup.
UPDATE ... WHERE ID = @ID ;
IF @@ROWCOUNT = 0
BEGIN;
INSERT ... ;
END;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 15, 2016 at 1:50 pm
Some concurrency matters to keep in mind: http://source.entelect.co.za/why-is-this-upsert-code-broken
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply