asked on

Update records in the database table based on select/join query

Hello there,

I wrote a query and exported the result to excel, then in the excel sheet I correct some columns which had issues in them. Now my data is correct in the excel but now my problem is how do i again send these data to my mssql 2008 database table. I need to some how update those columns in my table with this excel sheet based on my original select query.Please help.

cheers

Guy Hengel [angelIII / a3]

you have several options...
depending on the data i would either load the excel file to a staging table, and update with join
or using formulas in excel build the update statments...

Zolf

ASKER

Thanks angel for your comments.
I managed to create a table in my database and loaded that table with my excel data. Now what do i do to update my table with this col. it is little complex as i mentioned.

Zolf

ASKER

this is my original sql query which i then exported to excel and did my correction in the excel. now i need to update these columns

ISNULL(Customer.registerCode, '') as registerCode,
ISNULL(Customer.economalCode, '') as economicCode,
ISNULL(CustomerOwnerInfo.melliCode, '') as melliCode,

SELECT DISTINCT
    RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('000' + CAST(mohin.code AS VARCHAR), 3) +
    RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
    LTRIM(RTRIM(dbo.Customer.printName)) AS printName,
    LTRIM(RTRIM(dbo.Customer.printNameAlternate))  AS printNameFarsi,
    ISNULL(dbo.City.alternateName, '') + ', ' + ISNULL(dbo.Address.streetAlternate,'') AS addr,
    dbo.customertype.name as customertypename,
    dbo.customertype.alternateName as customertypepername,
    ISNULL(ContactInformation.phoneNumber, '') as phoneNo,
          ISNULL(ContactInformation.mobileNumber, '') as mobileNo,
          ISNULL(ContactInformation.faxNumber, '') as faxNo,
          ISNULL(ContactInformation.email, '') as email,
          ISNULL(Customer.registerCode, '') as registerCode,
    dbo.Customer.organizationNameAlternate as organizationName,
    ISNULL(Customer.economalCode, '') as economicCode,
          ISNULL(CustomerOwnerInfo.melliCode, '') as melliCode,
          ISNULL(Address.postCode, -1) as postCode,
          ISNULL(CustomerOwnerInfo.firstNameAlternate, '') as firstName,
          ISNULL(CustomerOwnerInfo.lastNameAlternate, '') as lastName,
    branch.name,
    branch.alternatename,
    Customer.organizationNameAlternate + ' ('+customertype.alternateName+')'+' '+'-'+' '+PersonTitle.alternateName+' '+CustomerOwnerInfo.lastNameAlternate as kaizencustomername,
    dbo.Visitor.firstName1 as visit_firstName1,
    dbo.Visitor.firstName2 as visit_firstName2,
    dbo.Visitor.lastName1 as visit_lastName1,
    dbo.Visitor.lastName2 as visit_lastName2
    
FROM
    dbo.Customer
LEFT JOIN
    dbo.CustomerOwnerInfo
ON
    (
        dbo.Customer.ownerInfoId = dbo.CustomerOwnerInfo.id)
        LEFT JOIN
    dbo.PersonTitle
ON
    (
        dbo.PersonTitle.id = dbo.CustomerOwnerInfo.persontitleid)
LEFT JOIN
    dbo.ContactInformation
ON
    (
        dbo.Customer.organizationContactInformationId = dbo.ContactInformation.id)
LEFT JOIN
    dbo.Tafsil
ON
    (
        dbo.Customer.tafsilId = dbo.Tafsil.id)
LEFT JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
LEFT JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
LEFT JOIN
    dbo.Address
ON
    (
        dbo.ContactInformation.addressId = dbo.Address.id)
LEFT JOIN
    dbo.RouteCustomerLink
ON
    (
        dbo.Customer.id = dbo.RouteCustomerLink.customerId)
LEFT JOIN
    dbo.Route
ON
    (
        dbo.RouteCustomerLink.routeId = dbo.Route.id)
LEFT JOIN
    dbo.Visitor
ON
    (
        dbo.Route.visitorId = dbo.Visitor.id)
LEFT JOIN
    dbo.City
ON
    (
        dbo.Address.cityId = dbo.City.id) 
        LEFT JOIN
    dbo.customertype
ON
    (
        dbo.customer.customertypeid = dbo.customertype.id)
        LEFT JOIN
        dbo.ContactInformation as ci
ON
    (
        dbo.Customerownerinfo.contactInformationId = dbo.ContactInformation.id)
        LEFT JOIN
    dbo.branch
ON
    (
        dbo.branch.Id = dbo.Tafsil.branchid)

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zolf

ASKER

thanks