October 6, 2011 at 2:51 am
create table stagingtable
(
idint not null primary key,
namevarchar(10),
cityvarchar(100),
addressvarchar(10),
)
create table paxtable
(
idint not null primary key,
namevarchar(10),
cityvarchar(10),
addressvarchar(10),
)
create table logtable
(
idint not null identity primary key,
rownumint,
stepmessagevarchar(100),
errormessagevarchar(100),
transactiondatedatetime()
)
---- sample data for staging table
insert into stagingtable
select 1,'name1','city1','address1' union all
select 2,'name2','abcdefghijklmnop','address2' union all
select 3,'name3','city3','address3' union all
---- sample data for pax table
insert into paxtable
select 1,'name','city','address'
I need to prepare a stored procedure . the logic is follows
I need to move records from stagingtable to paxtable.
while moving records ,
If id already exists in paxtable then i need to update paxtable with stagingtable data
Else i need to insert into paxtable
insertng rows/updated rows are should be delete from stagingtable.
in logtable i need to store details for each row of staging table
logtable should have data like below
idrownumstepmessage errormessage
11row1:updated null
22row2:insertionfailes error
33row3:insertionsucesses null
Please help me..
October 6, 2011 at 4:07 am
Too bad you weren't on 2008 or better, you could just use the MERGE command.
As it is, you need to do two TSQL statements. The first should be an INNER JOIN for the UPDATE. The second should be an OUTER JOIN looking for NULL values on the ID for the INSERT. Make sure you do them in that order otherwise you'll update the data you just inserted.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 16, 2011 at 7:12 am
Hi
Need clear picture what u exactly want from the query.
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply