question

Yasemin avatar image
Yasemin asked

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
sql-server-2005import-datatemporary-table
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
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][1] it is quite exhaustive in the possibilities: OPENROWSET, ADO.NET OLEDB, Linked Server, DTS, SSIS. Pick one and have a play! [1]: http://support.microsoft.com/kb/321686
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

it is one off for this release, just need to be able to query the table imported from excel on each environment.
0 Likes 0 ·
Well those examples in the link will show you what to do.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I agree but I can not create that. There is a meta data database somewhere and the data arcitecht is working on it. I am just making changes to reference data on a specific database.
0 Likes 0 ·
Leo avatar image
Leo answered
@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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.