question

kamesh_rao avatar image
kamesh_rao asked

I have a string. I want to generate a table from it

@STR = 'sadf dfd gf rgt fg rt tr ew' REQUIRED OUTPUT: sadf dfd gf rgt fg rt tr ew (i.e., columns(sadf dfd gf rgt fg rt tr ew) in a table )
delimited-stringstring-splitting
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

do you want to generate and execute a `CREATE TABLE` statement using the characters in the string as column names? Where is the string generated - an application? How do you determine data types, null-ability, default constraints or primary key - a second string with control language or should the process "just know"? Maybe whatever method you are using to generate the delimited string could be modified to include the rest of the `CREATE TABLE` statement.
2 Likes 2 ·
I need to use the results from this newly generated table in where clause comparision in a stored procedure. (i.e., SomeTable.ColumnName in (newly generated table.ColumnName))
0 Likes 0 ·
Then you'll probably want to grab the last "column" name from the string before applying the conversion supplied by @ThomasRushton so you know the column name to use in the comparison.
0 Likes 0 ·

1 Answer

· Write an Answer
ThomasRushton avatar image
ThomasRushton answered
So, something like: declare @CreateTable AS varchar(max) SELECT @CreateTable = REPLACE(@STR, ' ', ' nvarchar(max) NULL,') SELECT @CreateTable = @CreateTable + ' navarchar(max) NULL)' SELECT @CreateTable = 'CREATE TABLE TableName (' + @CreateTable + ')' exec @CreateTable (Untested, and not attempting to answer the question "Why?") What that does (or, rather, should do) is build a string in the following steps: * replace every space in the input string with a data type `(nvarchar(max) NULL)` and a comma at the end * adds the same again at the end (but without the comma this time) * pre- & appends those bits that are needed to round out the `CREATE TABLE` statement * executes it.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

good point, I recommend using sp_executesql instead of exec. sp_executesql is the most effective and safe way to perform dynamic code execution.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.