Create a SQL Table from a Dataset

I was just trying to get some feedback as to the simplest way to do this (as the dataset has some update methods and such built in).  I realize I can dynamically create a SQL table by looping through the Dataset's table collection and create columns based on the first row of the Dataset.  Then just build up a dynamic CREATE TABLE string, and when complete, pass it to a sproc and execute the string as dynamic SQL.

Any code would be helpful though.

The dataset doesn't have anything like that built in. You will have to do it yourself.

Shouldn't the table be already there and the web application only load the data in it?

No the table doesn't exist in SQL yet.  The user will upload an excel file to the server that they wish to create a table with.  From this excel file, I have imported it directly into a dataset.  From the dataset, I am trying to create a new SQL table based on the dataset and then import the dataset into the SQL table just created.

Try DTS for this. You can make a DTS, save it in DB and then run it from the web page. Let us know if you want to use that and need help.

I thought about using DTS, but how can I create a DTS package that is dynamic to accept Excel files of different names and create the tablename accordingly?

I also thought about saving the DTS as a VB file and using that programatically somehow, but haven't started that direction.

You could do that too but that would be much more complex. I and probably many others solve this by having same name for the Excel file. You said the file will be uploaded. After the upload is done, you can rename the file to the fixed file name and start the DTS.

I hope this is not a multi user application where many users will be uploading the files simultaneously. If it is this method will not work.

Oh I am sorry, your destination table name is also not fixed. This will not work for you.

Asuming that you have a store procedure with a table called Agent that has the following fields

            (client_src_sub_code,
             ap_code,
             agent_name_str,
             agent_ss_num,
              entity ,
             control_grp,
             ledger_key_1,
             ledger_key_2,
             ledger_key_3)


Create a store procedure with the following sintax


/* You only want to create the table the first time
You can also run the create table on a separate stored proceudure
*/

if Not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Agent]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
CREATE TABLE [dbo].[Agent] (
      [client_src_sub_code] [int] NOT NULL ,
      [control_grp] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [ap_code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [agent_ss_num] [numeric](18, 0) NULL ,
      [agent_name_str] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [entity] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ledger_key_1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ledger_key_2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ledger_key_3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [active_bol] [bit] NOT NULL
) ON [PRIMARY]
end

      INSERT INTO Agent
            (client_src_sub_code,
             ap_code,
             agent_name_str,
             agent_ss_num,
              entity ,
             control_grp,
             ledger_key_1,
             ledger_key_2,
             ledger_key_3)
      VALUES
            (@ClientSrcSub,
             @ap_code,
             @agent_name_str,
             @agent_ss_num,
              @entity ,
             @control_grp,
             @ledger_key_1,
             @ledger_key_2,
             @ledger_key_3)
END


Create a command object an associate this command to the InsertCommand of a dataAdapter. You should not need to
create Update and deletes commands because you are only going to  insert, but be prepare to create such commands with
stored procedures that do nothing, just in case.


Associate the parameters of the store procedure with the field on the dataset

insert the records in the dataset

run the update command of the dataadapter

Sorry, I wrote the previous example before the message that indicate that destination table is not fixed.  
Instead of using stored procedures create a string with the corresponding sql statmente.  While you create
such a string you can asign the necesary table name.  The rest should work.

Unless you want to rename the table after it has been created using sp_rename :)

Is it a muti user or single user system?

It is a multi-user application, but they won't be uploading excel files that often.  I really wanted to do it on the VB.NET end instead of relying on SQL to do it.  I am currently evaluating the option of saving a DTS package to a VB file and manipulating it, but I am not sure how well this will work to accomplish what I want.

>> I can dynamically create a SQL table by looping through the Dataset's table collection

I would say, you already have a good approach. Build the CREATE TABLE statment dynamically the way you are thinking.

The only problem that I am having doing this is figuring out what data type to make the column dynamically.  That is one of the benefits of using the DTS, as it will do it for me.

Any suggestions?

Even the DTS's guess is not good always as it is based on the first row only. How are you loading the Excel data in dataset? Can't you get the datatype from dataset tables? Worst scenario make them varchar(8000).

I am pretty sure I can get them programatically from the Dataset, but haven't ever done it before - trying right now.  I can't really make them Varchar(8000) b/c SQL's row size limit for a table is a total of 8000 bytes, so any insert into this table with multiple columns of Varchar(8000) would cause some problems.

Check out DataType and MaxLength properties of DataColumn class. You will still need to do some mapping as the DataType returns .NET datatype not the SQL datatype.

The limit is on actual size. Since varchar doesn't allocate the space unless there is a data in it, you can have as many columns of this size. Yes, the total size of the row data can not exceed 8k but that limitation is always there whatever way the table is created. As I said earlier, it is the worst scenario solution - not a good one. So don't use it unless you have to.

How about taking this a different direction.

I was able to create a XML Schema document (.xsd) and a XML file containing the records from my dataset.  Is there any T-SQL commands that I can use to create a new table directly from the .xsd file and then import the XML file?

maybe you will keep everything in XML and don't need extra table?

If anyone cares, I figured out how to do it and am posting it for others:

Dim myDataType As String = ""
                Dim myDataTypeLength As Integer
                Dim myColumnName As String = ""
                Dim myDataColumn As DataColumn
                Dim CreateTableString As String = "CREATE TABLE " & TableName & "("
                Dim CreateColumnString As String = ""

                With myDataTable.Rows(0)
                    For Each myDataColumn In myDataTable.Columns
                        myColumnName = myDataColumn.ColumnName.ToString
                        myDataType = myDataColumn.DataType.ToString
                        Select Case myDataType
                            Case Is = "System.Double"
                                myDataType = "FLOAT"
                            Case Is = "System.Decimal"
                                myDataType = "FLOAT"
                            Case Is = "System.String"
                                myDataType = "VARCHAR(1000)"
                            Case Is = "System.DateTime"
                                myDataType = "DATETIME"
                            Case Else
                                myDataType = "VARCHAR(500)"
                        End Select
                        CreateColumnString += myColumnName & " " & myDataType & ", "
                    Next myDataColumn
                End With

                CreateColumnString = CreateColumnString + ")"
                CreateColumnString = Replace(CreateColumnString, ", )", ")")
                CreateTableString = CreateTableString & CreateColumnString

                'Make sure table doesn't already exist
                strSQL = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" & TableName & "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[" & TableName & "] "
                app.ExecSQL(strSQL)
                'Create the new table
                strSQL = "EXEC ExecuteSQL '" & CreateTableString & "', '" & TableName & "'"
                app.ExecSQL(strSQL)

No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

PAQ with points refunded

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

TheLearnedOne
EE Cleanup Volunteer