Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
stevenandler
Starting Member
42 Posts |
Posted - 2013-11-04 : 16:34:33
|
I am looking for a way to import data from a CSV or Excel spread sheet and add the data directly into an Extended field instead of a regular field in the table. for example: let's say I have a comma delimited field with the following info:NDC_M_FORMULARY,CUSTOM_EXTSIG,Custom EXT SIGNDC_M_FORMULARY,DRUG_CODE,Alternate key, user definedNDC_M_FORMULARY,CHARGE_CODE,From the Charge code tableThe first column is the table nameSecond Column is the Column name in the tableThe third column contains the description that I would like to store in the Value in the Extended Property Name "MS_Description"Can someone please provide the T-SQL statement to accomplish this?BTW,I did find the following T-SQL which returns the Extended description for a specific Extended PropertyHere it is:SELECT [Table Name] = i_s.TABLE_NAME, [Column Name] = i_s.COLUMN_NAME, [Description] = s.value FROM INFORMATION_SCHEMA.COLUMNS i_s LEFT OUTER JOIN sys.extended_properties s ON s.major_id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME) AND s.minor_id = i_s.ORDINAL_POSITION AND s.name = 'MS_Description' WHERE OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME), 'IsMsShipped')=0 AND i_s.TABLE_NAME = 'NDC_M_FORMULARY' ORDER BY i_s.TABLE_NAME, i_s.ORDINAL_POSITIONThank you. |
|
stevenandler
Starting Member
42 Posts |
Posted - 2013-11-12 : 19:18:03
|
I was able to get my hands on the following code which does what I need. I hope someone else can also use it.BEGINEXEC sp_configure 'show advanced options', 1GORECONFIGUREGOEXEC sp_configure 'Ad Hoc Distributed Queries', 1GORECONFIGURE with overrideGODECLARE @XL AS TABLE(pk INT NOT NULL IDENTITY,SchemaName VARCHAR(255),TableName VARCHAR(255),ColumnName VARCHAR(255),Descr VARCHAR(1024))INSERT INTO @XL (SchemaName, TableName, ColumnName, Descr)select 'OGEN', TableName, ColumnName, Descr FROM OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=\\srvorders\Share\Temp;', 'SELECT TableName, ColumnName, Descr from formulary.csv');DECLARE @I INT, @CNT INT, @SN as varchar(255), @CN as varchar(255), @TN as varchar(255), @DESCR as varchar(255)SET @CNT = @@IDENTITYSET @I = 1WHILE @I <= @CNTBEGINSELECT @SN = SchemaName, @TN = TableName,@CN = ColumnName,@DESCR = DescrFROM @XLWHERE pk = @IEXECUTE sp_updateextendedproperty N'MS_Description', @DESCR, N'SCHEMA', @SN, N'TABLE', @TN, N'COLUMN', @CNSET @I = @I + 1ENDEND |
|
|
|
|
|
|
|