August 22, 2018 at 9:36 am
I am new to SQL Server Stored Procedure/Functions. need Suggestion for Creating below layout.
Input Param: EmployeeId, Add1, Add2 , A1 (This will I get From Table)
FileType=A1 (If Filetype=A1 then Create Data in below Format & put values in their respective positions.)
I have created below Fixed length code SELECT '637'
+ 'EMPLOYEEID' + + SPACE(9- LEN(RTRIM(LTRIM(ISNULL('EMPLOYEEID','')))))
+ '98'
+ CONVERT(VARCHAR(6), GETDATE(), 12)
+ SPACE(9)
+ 'A1'
+ ISNULL(LEFT('HomeAddr1',30),'') + SPACE(30- LEN(RTRIM(LTRIM(ISNULL(LEFT('HomeAddr1',30),'')))))
+ ISNULL(LEFT('HomeAddr2',30),'') + SPACE(30- LEN(RTRIM(LTRIM(ISNULL(LEFT('HomeAddr1',30),'')))))
+ SPACE(1)
+ SPACE(8) AS Data
August 22, 2018 at 9:45 am
I wouldn't do this in T-SQL. It looks like you're preparing a fixed width file, in which case you should use bcp.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 22, 2018 at 9:51 am
I don't have access to BCP. Can we do it using Procedure ??
August 22, 2018 at 10:00 am
I'm not quite sure what you are trying to accomplish here. I assume this is some formatting of the data all on one line. Is that correct?
Can you show a sample with a few rules of what you're trying to achieve?
August 22, 2018 at 10:02 am
sikka.sourav - Wednesday, August 22, 2018 9:51 AMI don't have access to BCP. Can we do it using Procedure ??
SSIS then? Otherwise, what ETL tool are you using/have access to? Drew is right, doing this within T-SQL is the the wrong choice, you should be doing to format definition in your ETL tool (whatever that is).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply