Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Howto. ..Load 2 columns from excel file into table with corresponding columns ? Expand / Collapse
Author
Message
Posted Monday, April 01, 2013 8:00 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, February 20, 2014 8:11 AM
Points: 75, Visits: 340
Hello,

Since I'm not a programmer and hopefully there's a simply way...
I need to load one column from an excel file into a database table while trying to match the corresponding columns

1) I have 3 columns in the excel with a server , account and account description column
2) The database table has these same columns but the account description is BLANK
3) I need to populate the account description from the excel from the corresponding row.

I hope i'm clear

many thx
Post #1437444
Posted Monday, April 01, 2013 8:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 32,781, Visits: 14,942
Simple way: Use the import wizard in SSMS to load the data into a temp table.

Then use a query to match up the data. If you temp table is MyStage and your regular table is Accounts, I'd run a select first

select
a.server
, a.account
, a.description
, b.server
, b.account
, b.description
from accounts a
inner join Mystage b
on a.server = b.server
and a.account = b.account

This will show you the contents of your account and staging tables as they match up. If this is right, you can update the account table like this:
update a
set a.description = b.description
from accounts a
inner join Mystage b
on a.server = b.server
and a.account = b.account

Then use the first statement to check things.

Drop the staging table when you are done.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1437466
Posted Monday, April 01, 2013 9:32 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, February 20, 2014 8:11 AM
Points: 75, Visits: 340
Much appreciated !

I actually tried it the import data wizard and recreated the table with the necessary columns by loading all the respective rows... now I will just work from there instead of comparing the rows.

Will also try it the way you mention

thx




Post #1437497
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse