creating table with excel file that contains column names 1

[kev510]

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

 

[philhege]

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.

 

[kev510]

thank you for your valuable post!
i will give it a try today.

 

[philhege]

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.