Create a temporary table and import data to it from Excel
Hi, I need to add values (picklist items) to a table on each environment (e.g. test, development, production) As I will be running the script on each environment I do not want to manually Import the data manually from Excel. Can I / should I create a temparory table to Insert the data from and then drop the temp table? The Excel fiel only has two columns (Venue, Law). I need to add the values from Venue and Law into mytable where the field type should be nvarchar(4000). Please can you advise and provide an easy solution?? Many thanks in advance. yas
If this is to be a re-occuring import, I suggest you look into using SSIS. This is the replacement for DTS (SQL 2000) and is designed for importing/exporting/shaping data. Check out this link: [How to import data from Excel to SQL Server] it is quite exhaustive in the possibilities: OPENROWSET,
ADO.NET OLEDB, Linked Server, DTS, SSIS. Pick one and have a play! :
Instead of maintaining the data in Excel, I'd set up a meta data management database. Load the data into there, once, then use that data to generate the scripts for each environment. That's basically how we manage our meta data at the company where I currently work. It's been very successful.
@Yasemin - You can do it through SSIS as *Will* said. We use lots of **DTS packages** in our environment but now we are using **SSIS** and that is quite reliable to handle the Flat file or Excel file. Also you can use ***Execute SQL Task*** to create and drop the temporary table.