Dynamic Insert

Question

Post reply

Dynamic Insert

John-79567

SSCommitted

Points: 1892
More actions
January 12, 2006 at 8:33 am

#170070

I need to insert data from a spreadsheet. The spreadsheet may have different number of columns and different column headings each time.
Basically, I want to insert into a table the column name and value.
EX:
Spreadsheet
ID     Col1     Col2     Col3     Col4
1        10      11         12      13

My Table
ID   Name    Value
1    Col1      10
2    Col2      11
3    Col3      12

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Umm.. this seems a bit vague..

If Col1 and value 10 gets ID 1, why does Col2 and value 11 get ID 2...? Shouldn't it also get ID 1?

Anyway, your true problem here lies in your assumption of not knowing what to do. It's not a particularly comfortable situation, and the solution should be aimed at the spreadsheet in question.

Try to make the spreadsheet conformant to a fixed format. If you have that, you know each time what to expect, and then it's also possible to code against.

/Kenneth

John-79567 SSCommitted Points: 1892 More actions · Answer 2

You're correct. The id value in my table should be ID 1 for each matching column and value. I apologize for the confusion.

John-79567 SSCommitted Points: 1892 More actions · Answer 3

I agree that a standard spreadsheet format would be best. Unfortunately the spreadsheet im dealing with is an ever evolving one. Basically, the columns represent new attributes for a product. The vendor never knows if the mfg will add additional attributes. Ideally, I would set up a biztalk process to communicate with a sophisticated vendor. Unfortunately, this vendor struggles with email communications let alone a database.

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 4

What you describe is a typical situation having a product with different number of properties. You don't tell if you are using DTS VB ADO etc. But what you can do is to create/import the spredsheet to a temporary tabel. When create a table like:

ID Column Property

1 2

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 5

Sorry continues.

ID Column property

1 1 something

1 2 something

etc

2 1 etc

2 2 etc

2 3 etc

And import each individual excelsheet to this table. So this table will contain all products.

David Burrows SSC Guru Points: 65143 More actions · Answer 6

You could try with dynamic sql, first run this (change the name of the excel file)

SELECT *

INTO #temp

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=YES;Database=C:\temp\book1.xls', [sheet1$])

SELECT colid,[name]

FROM tempdb.dbo.syscolumns

WHERE [id] = OBJECT_ID('tempdb.dbo.#temp')

AND [name] <> 'ID'

it will give you a list of the column names, then you can generate dynamic sql to insert the ID and each column in turn to your new table.

p.s. Don't forget to drop the table afterwards

DROP TABLE #temp

Far away is close at hand in the images of elsewhere.
Anon.