Results 1 to 4 of 4

Thread: SQL Cursor?

  1. #1
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Unanswered: SQL Cursor?

    Hi all,

    I have two tables with

    IMPORT_TABLE (Imported from MS Excel) <-- Source table
    Name
    Address
    City
    State
    Zip

    CONTACT_TABLE: <-- Target Table
    Full_Name
    Address_Line1
    City
    State
    Zip


    I have to match the name from the Import table with the full_name in Contact table, if it matches, i have to update address / city / state / zip information in the Contact table if and only if corresponding columns in the target table (Contact table) is empty. If the name does not match, i have to insert a new record in contact table.

    Should i use SQL cursor? If so could any one provide me with a sample solution?

    Thanks in Advance,
    Newbie

    ---------------------------------------------
    SQL 2000 on Windows 2000 Server
    Last edited by dsusendran; 12-07-06 at 15:33.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No. You do not need to use a cursor for this.

    Are you only looking for EXACT matches between IMPORT_TABLE.name and CONTACT_TABLE.Full_Name? Spreadsheet data is notoriously unreliable.

    Give us some sample data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    thanks

    i had this in csv format, i cleaned up the extra spaces using rtrim and ltrim and it is actually imported into SQL as 'IMPORT_TABLE'.

    Yes, i have to make an exact match of the name. If not i have to insert a new record with the information.

    If the name matches, i have to check if the address column is null/empty, if empty update address
    Next i have to check the state, if empty update state
    next City and Zip

    This is fairly reliable as the imports were originally from outlook...

    Thanks,
    Newbie

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need to make two passes through the data. Once to update existing records, and once to add new records.
    I don't think you want to be update just parts of addresses. That is asking for hodgepodge data. Address data should be treated as a unit, so that is what the following code does:

    Code:
    update	CONTACT_TABLE
    set	Address_Line1 = IMPORT_TABLE.Address,
    	City = IMPORT_TABLE.City,
    	State = IMPORT_TABLE.State,
    	Zip = IMPORT_TABLE.Zip
    from	CONTACT_TABLE
    	inner join IMPORT_TABLE on CONTACT_TABLE.Full_Name = IMPORT_TABLE.Name
    where	(CONTACT_TABLE.Address_Line1 is null and IMPORT_TABLE.Address is not null)
    	or (CONTACT_TABLE.City is null and IMPORT_TABLE.City is not null)
    	or (CONTACT_TABLE.State is null and IMPORT_TABLE.State is not null)
    	or (CONTACT_TABLE.Zip is null and IMPORT_TABLE.Zip is not null)
    
    insert into CONTACT_TABLE
    	(Full_Name,
    	Address_Line1,
    	City,
    	State,
    	Zip)
    select	Name,
    	Address,
    	City,
    	State,
    	Zip
    from	IMPORT_TABLE
    	left outer join CONTACT_TABLE on IMPORT_TABLE.Name = CONTACT_TABLE.Full_Name
    where	CONTACT_TABLE.Full_Name is null
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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