Dynamically Creating Table using Excel Sheet

Question

Dynamically Creating Table using Excel Sheet

ns22boss

Mr or Mrs. 500

Points: 563
More actions
December 16, 2011 at 7:07 am

#264877

Hi
Is there anyway to dynamically create DB Table by reading the Excel Value.
thanks

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Have you taken a look at OpenRowset and OpenQuery?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ns22boss Mr or Mrs. 500 Points: 563 More actions · Answer 2

Hi gsquared,

My question is I am reading the excel name from the user and according to that excel, i need to generate DB Table with same number of columns and rows.

Thanks

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

ns22boss (12/16/2011)
Hi gsquared,
My question is I am reading the excel name from the user and according to that excel, i need to generate DB Table with same number of columns and rows.
Thanks

Try to bundle OpenQuery statement in Dynamic SQL with @XLS_File_Name variable.

GSquared SSC Guru Points: 260824 More actions · Answer 4

You can use Select Into to dynamically create a table, and you can use dynamic SQL to query an Excel file named by an input parameter. Combine those two things, and you have what it sounds like you need. The only thing left is to determine how to name and query those tables, since the table names will be generated at runtime.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Peter Lavelle-397610 SSC Veteran Points: 215 More actions · Answer 5

Are you sure you want a database full of tables where the name has been generated by something a user entered in Excel?

Will the names follow some pattern? What about validation of the name?

The worst you could do is have something like a bunch of tables called something like Accounts_Dec_2011, Accounts_Jan_2012, etc.