Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

creating table with excel file that contains column names 1

Status
Not open for further replies.

kev510

Programmer
Jul 12, 2006
61
Hello.

I have an excel file which contains all the column names, type and length of a table that I need to create.

FieldName Type Length
A Int 8
B Varchar 10
C Varchar 8

I can modify the excel file in any way. How can I automatically create the table using SQL Server 2000?

Thanks in advance!
Kev
 
1. Create a DTS package that brings in the Excel data.

2. Write a stored procedure that spins through the record set and dynamically constructs a DDL statement. The example below has hard-coded parameter values and the name of the upload table I created from your example spreadsheet.

declare @tablename varchar(128)
set @tablename = 'Test_Schema'
declare @sql varchar(1024)
set @sql = 'CREATE TABLE ' + @tablename+ ' ('
select @sql = @sql + coalesce('['+fieldname + '] ' + [type] + case when type IN('char','varchar','numeric') then '('+[length]+'),'
else ',' end,'')
FROM Schema_def
set @sql = substring(@sql,1,len(@sql)-1) + ')'

print @sql
--exec(@sql)

Phil H.
-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.
 
thank you for your valuable post!
i will give it a try today.
 
Understand that this is rudimentary. You'll need to consider validating data types and definitions, creating indices, triggers, and managing security.

Have fun!

Phil H.
-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top