Data To Flat files from single table
-
Hi i was trying to create flat file which as the following format
10 firstname lastname
10 Address1 Address2
10 state Country
20 firstname lastname
20 Address1 Address2
20 state Country
30 firstname lastname
30 Address1 Address2
30 state Country
number 10 belogs to first person information ,20 belongs to second person etc...
all these records are sitting in one table in fallowing format
10 firstname1 lastname1 Address1 Address2 state Country
20 firstname2 lastname2 Address1 Address2 state Country
30 firstname3 lastname3 Address1 Address2 state Country
what iam thinking to use is UNIONall please give me ideas
-
You can try something like this. You many need to use convert if your non-id fields are of different data types.
I can give you a more exact answer if you include the table DDL.
SELECT id, col1, col2
FROM (SELECT 1 as srt, id, firstname as col1, lastname as col2
FROM address a1
UNION
SELECT 2 as srt, id, address1, address2
FROM address a2
UNION
SELECT 3 as srt, id, state, country
FROM address a3) x1
ORDER BY id, srt;
James Leeper
Database Administrator
WDS Global - Americas Region -
hi ,
Thanks for your reply ,i will try this
-
Hi james ,
so i can create view like this on table and then bcp out to txt file .
-
If you use James' good tried'n'true method, be sure to save a bit on performance and use UNION ALL instead of just UNION. UNION does a "Distinct" in the background where UNION ALL does not. Also, there's no need for the outer SELECT... just add the ORDER BY after the last SELECT in the unioned SELECTs.
That being said, if blazing speed is important, I wouldn't do it that way because it requires 3 full table scans to support the 3 SELECTs and an ORDER BY which is also expensive. Instead, I'd use a BCP format file that uses the \r (Carriage Return/Linefeed characters) to control the formatting of each row as if it were 3. It would use just one SELECT, no unions, and no sorts... it would just fly.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Hi Jeff,
YES you are right ,can you give me clear idea about carriage return format . that would be appreciable
-
amitaryan21 (11/5/2009)
Hi Jeff,YES you are right ,can you give me clear idea about carriage return format . that would be appreciable
Yes... lookup "BCP Format File" in Books Online and simply use \ r \ n (without the spaces) as the delimeter anywhere you want to end a line.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
There is another solution - a turbo-charged version of James' solution, which doesn't require any tedious messing around with format files:
USE tempdb;
GO
-- Sample table
CREATE TABLE dbo.SourceData
(
row_id INTEGER PRIMARY KEY,
first_name NVARCHAR(50) NOT NULL,
last_name NVARCHAR(50) NOT NULL,
address1 NVARCHAR(50) NOT NULL,
address2 NVARCHAR(50) NOT NULL,
state_name NVARCHAR(30) NOT NULL,
country NVARCHAR(50) NOT NULL,
);
GO
-- Test Data
INSERT dbo.SourceData
(row_id, first_name, last_name, address1, address2, state_name, country)
SELECT 10, N'firstname1', N'lastname1', N'Address1', N'Address2', N'state', N'Country' UNION ALL
SELECT 20, N'firstname1', N'lastname1', N'Address1', N'Address2', N'state', N'Country' UNION ALL
SELECT 30, N'firstname1', N'lastname1', N'Address1', N'Address2', N'state', N'Country' UNION ALL
SELECT 40, N'firstname1', N'lastname1', N'Address1', N'Address2', N'state', N'Country' UNION ALL
SELECT 50, N'firstname1', N'lastname1', N'Address1', N'Address2', N'state', N'Country';
GO
-- Procedure to wrap the solution, simply to make the BCP call neat and easy
CREATE PROCEDURE dbo.SourceDataExportFormat
AS
BEGIN
SET NOCOUNT ON;
-- This is amazingly fast
SELECT SD.row_id, CA.column1, CA.column2
FROM dbo.SourceData SD WITH (TABLOCK)
CROSS
APPLY (
SELECT CONVERT(SQL_VARIANT, SD.first_name), CONVERT(SQL_VARIANT, SD.last_name) UNION ALL
SELECT SD.address1, SD.address2 UNION ALL
SELECT SD.state_name, SD.country
)
CA (column1, column2)
ORDER BY
SD.row_id ASC;
END;
GO
EXECUTE dbo.SourceDataExportFormat;
GO
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply