Urgent!!Need to write a sql script

Question

Urgent!!Need to write a sql script

patilpallavi16

Ten Centuries

Points: 1123
More actions
September 13, 2016 at 11:39 am

#329790

I have the following sql query:
declare @naiccode int = 12345
SELECT Policy.policyType, Policy.policyNum, Policy.effectiveDate, '12345' AS "NAIC Code" ,
Insured.lname1, Insured.middle1,
Insured.fname1, Insured.address1, Insured.city, Insured.state, Insured.zip FROM Policy FULL JOIN
Insured ON Insured.insuredID = Policy.insuredID WHERE Policy.policyType = 1;
I want to write the results of this query to a fixed width .txt file. The blanks in between the fields should be blank spaces.
The file structure is as follows:
Field Name1 = Policy Type
Length = 2
Begin = 1
End =2
Type = alpha numeric
Field name2 = NAIC
length = 5
Begin = 3
End = 7
Type = Numeric
Field 3 = policy number
length = 30
begin = 8
end = 37
Field Name 4 = Date
length = 8
begin =38
end = 45
Type = numeric

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 1

Well a couple things, it looks like you have more fields in your select statement than you have fields defined in the file. For the fields are these right or left justified? Other than than that there's a couple options for exporting the data, one option would be to make an SSIS package with the target defined as a fixed width file. Another option would be to format the data as desired in your query and just return one column with the data formatted as desired and however you choose to export it would just have the one column as output.

patilpallavi16 Ten Centuries Points: 1123 More actions · Answer 2

Actually I want to write a sql script rather than use SSIS package. And the requirement is to output a text file with the give specs.

patilpallavi16 Ten Centuries Points: 1123 More actions · Answer 3

Actually I want to write a sql script rather than use SSIS package. And the requirement is to output a text file with the give specs.

I just gave a sample data in the example..If I get to know this I can manage to write the script for rest of the fields. They are left justified.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

patilpallavi16 (9/13/2016)
Actually I want to write a sql script rather than use SSIS package. And the requirement is to output a text file with the give specs.
I just gave a sample data in the example..If I get to know this I can manage to write the script for rest of the fields. They are left justified.

Why not use SSIS? That is what it is designed for...

There is nothing native in SQL Server to create a text file - to do that you need to use some application. That can be SSIS, BCP, Powershell, etc...

If you need the results for each row to be a fixed size and you are not using some other tool - then you need to pad the data in each field to the appropriate length. Something like:

SELECT CAST(Policy.policytype + SPACE(2) As char(2))

+ CAST(@naiccode + SPACE(5) As char(5))

+ CAST(Policy.policyNum + SPACE(30) As char(30))

+ CONVERT(char(8), Policy.effectiveDate, 112)

...

Getting the above into a file will need to be done using some other tool - but hopefully that gets you started.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 5

patilpallavi16 (9/13/2016)
I have the following sql query:
declare @naiccode int = 12345
SELECT Policy.policyType, Policy.policyNum, Policy.effectiveDate, '12345' AS "NAIC Code" ,
Insured.lname1, Insured.middle1,
Insured.fname1, Insured.address1, Insured.city, Insured.state, Insured.zip FROM Policy FULL JOIN
Insured ON Insured.insuredID = Policy.insuredID WHERE Policy.policyType = 1;
I want to write the results of this query to a fixed width .txt file. The blanks in between the fields should be blank spaces.
The file structure is as follows:
Field Name1 = Policy Type
Length = 2
Begin = 1
End =2
Type = alpha numeric
Field name2 = NAIC
length = 5
Begin = 3
End = 7
Type = Numeric
Field 3 = policy number
length = 30
begin = 8
end = 37
Field Name 4 = Date
length = 8
begin =38
end = 45
Type = numeric

Can you post some readily consumable data using the method outlined in the article at the first link in my signature line below under "Helpful Links", please?

--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)