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
Ah so this is metadata - I was thinking too far ahead and thought you where describing the data!
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... :-/
Phone,INT|Fax,VARCHAR(10)|Mobile,int shoudl come as ColumnName Datatype Phone INT Fax VARCHAR(10) Mobile int
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?
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
Have a look at this for a starting point:
http://ask.sqlservercentral.com/questions/94875/split-a-string-based-on-delimter.html
12 People are following this question.
