question

aRookieBIdev avatar image
aRookieBIdev asked

string to columns in select

Hi One of my string column has data such as ColumnA,INT|COLUMNB,VARCHAR(10) i need to read the data and insert into a temp table Columns DataType ColumnA INT ColumnB varchar(10) Kindly help me with this Thanks, Kannan
sql-server-2008delimited-string
7 comments
10 |1200

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

Ah so this is metadata - I was thinking too far ahead and thought you where describing the data!
2 Likes 2 ·
My guess is that what we're looking at is a data stream like: InputData Phone,INT|Fax,Varchar(10)|Mobile,int and @aRookieBIdev wants the output in a table: FieldName NameOfDataType Phone INT FAX varchar(10) Mobile int Similarly, if the input was: InputData Field1,text|Field2,bigint Then the output would be FieldName NameOfDataType Field1 text Field2 bigint I'm guessing no restrictions... :-/
1 Like 1 ·
Can you provide some example data?
0 Likes 0 ·
Phone,INT|Fax,VARCHAR(10)|Mobile,int shoudl come as ColumnName Datatype Phone INT Fax VARCHAR(10) Mobile int
0 Likes 0 ·
I meant some actual data, I don't know what you mean by the pipe | symbol - are you saying it is explicitly in the data like '123|text'? How many of these delimiters can there be? Is there a fixed number?
0 Likes 0 ·
Show more comments
TimothyAWiseman avatar image
TimothyAWiseman answered
If I understand your question right, then what you need to do is split the string. Then you need to generate a dynamic SQL command to create the table based on the split version of the string. Then of course execute it to actually create the table. I don't have time to write it out and test a full solution right now, but for some quick pointers: Thomas's link will help with the string splits. Another good resource for that is [ http://www.sqlservercentral.com/articles/XML/66932/][1] Since you are specifying column names dynamically, you will not be able to fully parameterize the SQL String. This means that if you encode this into a stored procedure or something that might be user facing you need to be wary of [SQL Injection attacks][2]. Since you can't fully paramaterize anyway, just using the exec (@sql) pattern might be simply than sp_executesql after you make your dynamic query. And while it might be a bit long if you just want a fast solution, I recommend "[The Curse and Blessing of Dynamic SQL][3]" to anyone working with dynamic sql. [1]: http://www.sqlservercentral.com/articles/XML/66932/ [2]: http://www.simple-talk.com/sql/learn-sql-server/sql-injection-defense-in-depth/ [3]: http://www.sommarskog.se/dynamic_sql.html
10 |1200

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

ThomasRushton avatar image
ThomasRushton answered
10 |1200

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

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.