August 14, 2013 at 1:53 am
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.....
Thanks
Regards 😉
Nicole
August 14, 2013 at 6:17 am
You'd need to use dynamic SQL for this. You can query the INFORMATION_SCHEMA.columns view to find out which columns a table has.
Using this info, you can construct a SQL query of the following form:
SELECT CONVERT(NVARCHAR(100),col1) + ';' + CONVERT(NVARCHAR(100),col2) + ... + ';' + CONVERT(NVARCHAR(100),coln)
FROM myTable
You can execute this SQL statement using sp_executesql.
If you make the dynamic query more complex, you can take NULL values, data types et cetera into account.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 14, 2013 at 6:33 am
This is something you should do client-side, not in SQL Server. SQL Server serves the data. Formatting is best done elsewhere. I assume that this for some export purpose. There is SSIS, there is BCP, and you can write your own C# for custom format. But T-SQL is not your guy.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 14, 2013 at 6:40 am
Erland Sommarskog (8/14/2013)
This is something you should do client-side, not in SQL Server. SQL Server serves the data. Formatting is best done elsewhere. I assume that this for some export purpose. There is SSIS, there is BCP, and you can write your own C# for custom format. But T-SQL is not your guy.
Tsk tsk, don't you know everything is possible with TSQL? 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 15, 2013 at 7:58 am
If the idea is to simply dump the output to a csv file, the following powershell script will do that for you.
You'll have to parameterize the Query.
invoke-sqlcmd -Query "select name, create_date, collation_name from sys.databases;" | export-csv -Delimiter `; -path test.csv
August 15, 2013 at 10:28 am
Not a proposed solution, but an example of using system views to create a dynamic SQL SELECT statement that is only executed once to generate the output.
/*
* Simple example of generating the output as a set without having to dynamically loop through all the records in the table.
*It does loop through sys.columns once to get each column in the table.
*Certainly not a "use anywhere" solution, for instance, you may need to return a certain date format for date columns.
* You still have to get it into a text file.
AdventureWorks has the table used in the example:
SELECT [ContactTypeID]
,[Name]
,[ModifiedDate]
FROM [AdventureWorks].[Person].[ContactType]
*/
--use AdventureWorks
DECLARE @TableName AS NVARCHAR(128)
DECLARE @MaxColumnValueLength AS NVARCHAR(4)
SET @TableName = 'ContactType'
SET @MaxColumnValueLength = '256'
SET NOCOUNT ON
--setup table to hold column list
DECLARE @junk22 TABLE (
yID INT IDENTITY(1,1) NOT NULL
,zID INT NULL
,zSQL NVARCHAR(2000) NULL
)
DECLARE @SQL AS NVARCHAR(max) --select statement to be produced
, @row AS INT --row counter
, @rows AS INT -- number of columns in table + 2
, @TableSchema AS NVARCHAR(128) -- in case table belongs to non-default schema
SET @row = 1
SET @SQL = ''
-- get schema
SELECT @TableSchema = sys.schemas.[name]
FROM sys.schemas
JOIN sys.objects
ON sys.schemas.schema_id = sys.objects.schema_id
WHERE sys.objects.name = @TableName
--cte of columns in table converted to string
;WITH cols
AS (
SELECT column_ID, CASE WHEN column_id = 1 THEN '' ELSE ' + ' + char(39) + ',' + CHAR(39) + ' + ' END + CHAR(39) + '"' + CHAR(39) + ' + Convert(varchar(' + @MaxColumnValueLength + '),[' + sys.columns.Name + ']) + ' + CHAR(39) + '" ' + CHAR(39) AS csv
FROM sys.columns
JOIN sys.objects
ON sys.columns.object_id = sys.objects.object_id
WHERE sys.objects.name = @TableName
--ORDER BY column_id
)
--cte of columns names prefixed by 'SELECT ' and suffixed by 'FROM...'
, cte_sql (zID, zSQL)
AS (
SELECT 0 AS zID, 'SELECT ' AS strSQL
UNION ALL
SELECT column_id, csv FROM cols
UNION ALL
SELECT 1000, 'FROM ' + @TableSchema + '.' + @TableName
)
--insert results into table
INSERT INTO @junk22
( zID, zSQL )
SELECT
zID, zSQL
FROM cte_sql
ORDER BY zID
--count rows from column list to process
SELECT @rows = COUNT(*) FROM @junk22
-- the while only loops through the column list for the table, not the table itself
-- to build up SQL SELECT statement.
WHILE @row <= @Rows
BEGIN
SELECT
@SQL = @SQL + zSQL + ' ' + CHAR(13)
FROM @junk22
WHERE yID = @row
SET @Row = @Row + 1
END
-- shows the select statement you have generated
print @SQL
--execute select statement
exec (@SQL)
August 15, 2013 at 10:35 am
Gee, this sure sound like using BCP in character based mode would do the trick.
BCP DatabaseName.schema.table OUT FileName.csv /m 100 /b 100 /e TableName.err /c /t\, /r /S ServerName /T /E
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply