SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 get latest date to insert into table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shar_wani
Starting Member

2 Posts

Posted - 07/23/2012 :  04:38:50  Show Profile  Reply with Quote
i have a set of duplicate data and i need to insert into main table.
for now my sql statement is like this :

INSERT INTO ACCOUNT
( acct_no
,state_code
,sys_appl_id
,cust_id
,bill_period
,acct_status
,acct_name
,addr_line1
,addr_line2
,addr_line3
,addr_line4
,addr_line5
,addr_line6
,addr_line7
,addr_line8
,postal_code
,final_acct_date
,last_upd_date
,rev_code
,npcs_acct_cat
,bill_sys_appl_id
)
SELECT acct1_acct_no
,acct1_state_code
,acct1_sys_appl_id
,acct1_cust_id
,acct1_bill_period
,acct1_acct_status
,acct1_acct_name
,acct1_addr_line1
,acct1_addr_line2
,acct1_addr_line3
,acct1_addr_line4
,acct1_addr_line5
,acct1_addr_line6
,acct1_addr_line7
,acct1_addr_line8
,acct1_postal_code
,acct1_final_acct_date
--,acct1_last_upd_date
,SYSDATE
,NULL
,NULL
,acct1_bill_sys_appl_id
FROM
(
SELECT distinct /*+ordered */
T1.acct_no acct1_acct_no
,T1.state_code acct1_state_code
,T1.sys_appl_id acct1_sys_appl_id
,T1.cust_id acct1_cust_id
,T1.bill_period acct1_bill_period
,T1.acct_status acct1_acct_status
,T1.acct_name acct1_acct_name
,T1.addr_line1 acct1_addr_line1
,T1.addr_line2 acct1_addr_line2
,T1.addr_line3 acct1_addr_line3
,T1.addr_line4 acct1_addr_line4
,T1.addr_line5 acct1_addr_line5
,T1.addr_line6 acct1_addr_line6
,T1.addr_line7 acct1_addr_line7
,T1.addr_line8 acct1_addr_line8
,T1.postal_code acct1_postal_code
,T1.final_acct_date acct1_final_acct_date
--,T1.update_date acct1_last_upd_date
,SYSDATE
,NULL
,NULL
,T1.bill_sys_appl_id acct1_bill_sys_appl_id
,T2.acct_no
,T2.sys_appl_id
,T2.cust_id
FROM ICP_NPCS_INBND_PROF_ACCOUNT T1
,ACCOUNT T2
WHERE T1.acct_no = T2.acct_no
AND T1.sys_appl_id = T2.sys_appl_id
AND T1.update_date = (select max(a.update_date) from ICP_NPCS_INBND_PROF_ACCOUNT a, ACCOUNT b where a.acct_no = b.acct_no)
AND T1.update_date >= trunc(T2.last_upd_date)
AND T1.error_code is null
)

0 record(s) affected for this sql!

same statement for update function. but in insert function seems didn't work.
to differentiate the latest data is by update_date.
is the something wrong with my sql?

webfred
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 07/23/2012 :  05:40:46  Show Profile  Visit webfred's Homepage  Reply with Quote
Looks to me like you are working on ORACLE because TRUNK() isn't known in MS SQL Server.
Maybe you can get better help in oracle forums because here we are on MS SQL Server only.

Try DBFORUMS.COM/ORACLE for example.


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000