Help in Formatting without mentioning column name in SQL

Question

Hi, How I can get the following desired result without mentioning column's name in the query.

Select * from country

CCode CName CPost CDest CSET CPOS CJOH

001 AU L1 RE 1 2 CEN

Desired Result:

('001','AU','L1','RE','1','2','CEN')

Answer 1

This can be done through SSIS (Import/Export Wizard from SSMS).

Export table data from your database to a text file you defined.

Choose no header row for the text file (uncheck the box: Column names in the first row) and with text qualifier you choose (single quote).

You can run the package immediately or save it for later run.

Answer 2

Hi @Kenny Gua,

Please try the following solution.

It is using XML and XQuery to tokenize all columns in a row without specifying their names. Very flexible.

-- DDL and sample data population, start
DECLARE @tbl TABLE (CCode CHAR(3), CName CHAR(2), CPost CHAR(2), CDest CHAR(2), CSET INT, CPOS INT, CJOH CHAR(3));
INSERT @tbl (CCode, CName, CPost, CDest, CSET, CPOS, CJOH) VALUES
('001', 'AU', 'L1', 'RE', 1, 2, 'CEN');
-- DDL and sample data population, end

SELECT t.*
	, result = '(''' + REPLACE(c.query('data(/root/r/*)').value('.', 'VARCHAR(128)')
		, SPACE(1), ''',''') + ''')'
FROM @tbl AS t
CROSS APPLY (SELECT t.* FOR XML PATH('r'), TYPE, ROOT('root')) AS t1(c);

Output

('001','AU','L1','RE','1','2','CEN')