Import Export Wizard

[DougNaf]

I am using SQL2008 and want to export data from an SQL table to excel, make changes and import it back into the SQL table. How can this be done? IE I have a master file that has 20 fields, The key is EmpNo and of the 20 fields I want to export 4 fields to excel (and make changes).

current:
EmpNo Field1 Field2 field4 Field4
2401001 5 6 7 Yes

export to excel and change

EmpNo Field1 Field2 field4 Field4
2401001 8.5 4.5 6 No


For the import back into SQL how can I match the EmpNo and update fields 1/2/3/4?

Thanks in advance for suggestions!

 

[gmmastros]

I am no expert on import/export, but I am curious... why do you want to import in to excel to make changes? Why not make the changes while the data is still in SQL Server?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MakeItSo]

why do you want to import in to excel to make changes? Why not make the changes while the data is still in SQL Server?

I am doing th same with some SQL Server table data: in order to be able to have an external expert review the data and make changes where necessary.

These two links should give you a good start:

http://www.66pacific.com/sql_server_export_to_excel.aspx

http://www.66pacific.com/sql_server_import_from_excel.aspx

Note: when importing from Excel to SQL Server, you import into a new table. After that, simple create an UPDATE query within SQL Server to synchronise your data table with the imported values.

MakeItSo

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[DougNaf]

The applicaion is HR and if I make the changes within the app, there is a history job record added. I am building info and want to update several fields, but do not want a history job record created for every employee. If I can export, edit the spreadsheet, then import to SQL table i can avoid the history job record.

 

[gmmastros]

What creates the history job record? Is it application code, or is it a trigger in the database? If it's a trigger, you'll likely get history job records anyway.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DougNaf]

I do not know for sure what triggers the history records, but I think it is the application (written in fox-pro). Actually the application is SAGE_HRMS.

 

[gmmastros]

If the application is creating the history records, then changing the data with SQL Server Management Studio would not create history records either. If it's a trigger creating the history records, you'll get history records no matter what (unless you disable the trigger).


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom