Create Table with Dynamic Columns

  • I need to create a table and dynamically define the columns based on a string I am being passed in.  For example, if I receive the string "Company, Name, Phone", I must create a table such as:

    EXEC ('CREATE TABLE [dbo].[TABLEA] (

     [Company] [varchar] (50) NULL ,

     [Name] [varchar] (50) NULL ,

     [Phone] [varchar] (20) NULL

    ) ON [PRIMARY]')

    If the string is "Company, Name, State, City", I must create a table such as:

    EXEC ('CREATE TABLE [dbo].[TABLEA] (

     [Company] [varchar] (50) NULL ,

     [Name] [varchar] (50) NULL ,

     [State] [varchar] (20) NULL,

     [City] [varchar] (30) NULL

    ) ON [PRIMARY]')

    How could I parse the incoming string (comma separated) into an array to then based the columns off of?

    Any help would be huge and greatly appreciated!

     

  • You can mimic the functionality of arrays by using split function ( I saw somewhere in the group  called fn_split which splits the input separated by comma)

    store the results in table variable

    pull the data from table

    create a dynamic sql and

    execute it

  • If you intend to create separate table for each contact record they send to you???

    If you really believe it's a great idea you can use

    SELECT @Company Company, @Name Name, @Phone Phone

    INTO dbo.TableA

    But you better start looking for a new job next week.

    Because after month everybody will realise that the thing you've created is not managable at all.

    If you would like to keep the job, follow this:

    CREATE TABLE dbo.Company (

    ID int IDENTITY(1,1),

    Name nvarchar(200),

    ...

    )

    CREATE TABLE dbo.ContactType(

    ID int IDENTITY(1,1),

    Type nvarchar(200) NOT NULL, -- e.g. 'Name', 'Phone', 'State', 'City',

    ...

    )

    CREATE TABLE dbo.Contact(

    ID int IDENTITY(1,1),

    Contact nvarchar(200) NOT NULL, e.g. 'Juan Bob', '0800-800800'

    ...

    )

    CREATE TABLE dbo.CompanyContact(

    CompanyID int NOT NULL,

    ContactTypeId int NOT NULL,

    ContactId int NOT NULL

    )

    Then you may create set of views with INSTEAD OF INSERT triggers on it to populate these tables and having different set of fields to insert.

    _____________
    Code for TallyGenerator

Viewing 3 posts - 1 through 2 (of 2 total)

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