August 31, 2006 at 5:06 pm
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!
August 31, 2006 at 6:05 pm
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
August 31, 2006 at 6:16 pm
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