Create table fields with script 1

[Copierbw]

I have in Excel the layout of a table that needs to be created with it's fields in SQL. This table has 76 fields with description (datatype, name, length, etc) already specified in this Excel file. My question is it in any way possible to use this Excel sheet to create the table and fields in SQL without manually doing it all in SQL....Thanks

You don't need eyes to see just the vision because there are always more ways and different answers to what we are used to...

 

[JayKusch]

Yes there is ... You first will import the excel wsheet into a table or table variable. Make sure you define a field for a seed value. That way you can step thru each record. Once the excel is loaded, you will need to create a dynamic SQL statement. Something to the effect of ...

DECLARE @CreateTable VarChar(8000)
DECLARE @WorkString VarChar(200)

DECLARE @CurrRecord Int
DECLARE @TotRecords Int

DECLARE @Fname VarChar(100)
DECLARE @FType VarChar(100)
DECLARE @FSize VarChar(100)

SET @CreateTable = ''
SET @WorkString = ''

SET @Fname = ''
SET @FType = ''
SET @FSize = ''

SET @CurrRecord = 0
SELECT @TotRecords = Count(*) FROM ExcelTable

SET @CreateTable = 'CREATE TABLE [dbo].[MyTable] (['

Then run a loop that reads each record from your excel loaded table and pulls the field info. Something like ...


WHILE @CurrRecord <= @TotRecords

SELECT @FName = FldName, @FType =FldType, @FSize = FldSize
FROM ExcelTable
WHERE ID = @CurrRecord

IF @FType = 'VarChar' or @FType = 'Char'

SELECT @WorkString = '[' + @Fname + '] [' + @FType + '] (' + @Fsize + ') NULL,'
ELSE '
SELECT @WorkString = '[' + @Fname + '] [' + @FType + '] NULL,'

SET @CreateTable = @CreateTable + @WorkString

SET @CurrRecord = @CurrRecord + 1

END


Then make sure the last field info that is added DOES NOT include the &quot;,&quot; and wrap it up with ...

SET @CreateTable = @CreateTable + ') ON [PRIMARY] GO'


May want to print out the @CreateTable var to see what it looks like before execution.


PRINT @CreateTable


Then if all looks good ... execute and create the table ...


EXEC (@CreateTable)


You may need to use the RTRIM/LTRIM verbs to get everything lined up and you must format the fields to be in this format

[FieldName] [VarChar] (100) NULL , -- String
[FieldName] [int] NULL , -- Numeric



Thanks

J. Kusch