Results 1 to 2 of 2
  1. #1
    Join Date
    May 2008
    Posts
    39

    Unanswered: Insert statement

    Hi ,

    I have to insert data into a table .Each column is to be loaded from multiple tables.How can i combine all sql statement into 1 statement.for eg:
    insert into x(CUSTOMER_NBR,TITLE_PREFIX_NM,TO_PREFIX_NM,FIRST_ NM,LAST_NM,SUFFIX_NM,FULL_NM,PERSONAL_IDENTIFICATI ON_NBR,JOINT_ACCOUNT_IND,PARTNER_ACCT_KEY_ID,BIRTH _NM,BIRTH_DT,GENDER_DESC,POWER_c_STATUS_DESC,POWER _x_STATUS_BEG_DT,INDIVIDUAL_x_IND,PRIMARY_FUND_NM, POWER_OF_ATTORNEY_HOLDER_NM) values
    (select CUSTOMER_NBR,TITLE_PREFIX_NM,TO_PREFIX_NM,FIRST_NM ,LAST_NM,SUFFIX_NM,FULL_NM,PERSONAL_NBR,JOINT_ACCO UNT_IND,PARTNER_ACCT_KEY_ID,BIRTH_NM,BIRTH_DT,GEND ER_DESC,POWER_r_STATUS_DESC,BEGIN_POWER_r_STATUS_D T,BEGIN_POWER_r_STATUS_DT,INDIVIDUAL_BROKER_IND,PR IMARY_FUND_NM,POWER_OF_r_HOLDER_NM from STAGING_AT)

    insert into x(CONNECTED_ACCOUNT_CNT) values
    ((select count (*) from y,z) where
    (select NON_US_CUSOTMER_SK from y) = (select NON_US_CUSOTMER_SK from z))
    Thanks

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    What are you trying to accomplish with
    Code:
    insert into x(CONNECTED_ACCOUNT_CNT) values
    ((select count (*) from y,z) where
    (select NON_US_CUSOTMER_SK from y) = (select NON_US_CUSOTMER_SK from z))
    It doesn't make a lot of sense to me, in combination with the first SQL command.

    Here's a solution: Mind that there must be some relationship between the tables y, z and STAGING_AT.
    Code:
    insert into x (CUSTOMER_NBR, TITLE_PREFIX_NM, TO_PREFIX_NM, FIRST_ NM, LAST_NM, 
    		SUFFIX_NM, FULL_NM, PERSONAL_IDENTIFICATION_NBR, JOINT_ACCOUNT_IND, 
    		PARTNER_ACCT_KEY_ID, BIRTH_NM, BIRTH_DT, GENDER_DESC, 
    		POWER_c_STATUS_DESC, POWER_x_STATUS_BEG_DT, INDIVIDUAL_x_IND, 
    		PRIMARY_FUND_NM, POWER_OF_ATTORNEY_HOLDER_NM
    		,CONNECTED_ACCOUNT_CNT) values
    (select CUSTOMER_NBR, TITLE_PREFIX_NM, TO_PREFIX_NM, FIRST_NM, LAST_NM, 
    		SUFFIX_NM, FULL_NM, PERSONAL_NBR, JOINT_ACCOUNT_IND, 
    		PARTNER_ACCT_KEY_ID, BIRTH_NM, BIRTH_DT, GENDER_DESC, 
    		POWER_r_STATUS_DESC, BEGIN_POWER_r_STATUS_DT, BEGIN_POWER_r_STATUS_DT, INDIVIDUAL_BROKER_IND,
    		PR IMARY_FUND_NM, POWER_OF_r_HOLDER_NM
    		,(select count (*) 
    		from y, z where
    		 	(select NON_US_CUSOTMER_SK from y where y.id = STAGING_AT.CUSTOMER_NBR) = (select NON_US_CUSOTMER_SK from z where z.id = STAGING_AT.CUSTOMER_NBR))
    from STAGING_AT)
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •