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 |
gpc44
Starting Member
35 Posts |
Posted - 2013-08-14 : 03:54:34
|
Hi,I am looking for a Function/Procedure, which "TableName" as a parameter, and returns each row of the table as a string with ";" as separator and "\ n" is obtained as RowDelimiter (like csv file - 1 Column for each tablerow)Function getCsvFormatFromTable (@ TableName)( @Csv_value RETURNS TABLE (Value NVARCHAR (MAX)) Return @ csv_Value)it also can written in a #Table etc.....ThanksRegards ;-)Nicole |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-08-14 : 05:03:08
|
[code]-- Enable xp_cmdshell featureEXEC sp_configure 'show advanced options', 1GORECONFIGUREGO-- To enable the feature.EXEC sp_configure 'xp_cmdshell', 1GO-- To update the currently configured value for this feature.RECONFIGUREGODECLARE @tableName VARCHAR(50) = 'study.dbo.departments'-- copy one file to another location using xp_cmdshell featureDECLARE @SQL VARCHAR(120)--COPY A NEW ONE INSET @SQL = 'xp_cmdshell ' + CHAR(39) + 'bcp '+ @TableName +' out "D:\temp\dept.txt" -c -t; -r \n -T' + CHAR(39)EXEC (@SQL)This is the main logic 'xp_cmdshell ' + CHAR(39) + 'bcp '+ @TableName +' out "D:\temp\dept.txt" -c -t; -r \n -T' + CHAR(39)-c = Character Data-t; = Column separator is ;-r \n = row separator is New line character -T = windows authentication to the SQL ServerFor SQL server authentication use -U UserName -P password instead of -T--Chandu |
|
|
gpc44
Starting Member
35 Posts |
Posted - 2013-08-14 : 07:31:25
|
Hi Bandi,thats real fine !Is there a option, to write the Output to a #table - not to FileSystem?REGARDSNicole |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-08-14 : 07:50:56
|
Is the following result you want?-- Create resultant table structureCREATE TABLE TestCSV (Row VARCHAR(MAX));-- Insert data of original table with comma separated columns data and row end with ;INSERT TestCSV SELECT CAST(DEPARTMENT_ID AS VARCHAR(10))+ ','+DEPARTMENT_NAME + ',' + CAST(LOCATION_ID AS VARCHAR(10)) +';'FROM DEPARTMENTS-- Verify resultSELECT * FROM TestCSV--Chandu |
|
|
|
|
|