Constant in SQL for DTS to Textfile

Question

Post reply

Constant in SQL for DTS to Textfile

Al Komarek

SSC Rookie

Points: 43
More actions
July 15, 2005 at 10:04 am

#167588

In an effort to automate a process, I am trying to populate a csv textfile with data from a SQL Server 2000 database that will be imported by a proprietary database; however, not all of the data required to go into the textfile is available in the source db. Fortunately, the data I'm needing has constant values for the fields that I want to populate, i.e Lab Name. Whereas, the Destination database will receive data from other labs but not via this source.
Is it possible to use a constant rather than a db field within the SQL query to populate one of the textfile fields. A portion of my present statement is:
Select LEFT([SAMPNAME], 4) AS IUNUM, RIGHT(LEFT([SAMPNAME], 8), 3) AS SITENUM, convert(varchar,[SAMPDATE], 112) as SAMPDATE, [BDL] AS "SAMPNUM", [ANALYTE], (CASE [STARTDATE]-[SAMPDATE] WHEN 0 THEN '2' ELSE '1' END) AS METHOD, convert(varchar,[STARTDATE], 112) as STARTDATE, [FINAL], [BDL], [NOTES1], [SAMPLER], [ORDNO], [UNITS]
From [CUSTOMER]
As you can see, I have already done a lot of formatting within the statemnt but would appreciate someone's SQL expertise to tell me if using a constant is possible or not.
Thanks,
Al

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

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

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 1

sure, add a new column in the proper place

select x

, y

, 3 'scalar'

, z

...

Al Komarek SSC Rookie Points: 43 More actions · Answer 2

I tried that but it just renames the column. I want to populate the field for every record picked up with a constant value.

Thanks

Al

Al Komarek SSC Rookie Points: 43 More actions · Answer 3

I just figured it out! I originally created an empty field in my source table, named BDL and am using it for all of the extra fields I need to populate the textfile. In my original code, as follows:

Select LEFT([SAMPNAME], 4) AS IUNUM, RIGHT(LEFT([SAMPNAME], 8), 3) AS SITENUM, convert(varchar,[SAMPDATE], 112) as SAMPDATE, [BDL] AS "SAMPNUM", [ANALYTE], (CASE [STARTDATE]-[SAMPDATE] WHEN 0 THEN '2' ELSE '1' END) AS METHOD, convert(varchar,[STARTDATE], 112) as STARTDATE, [FINAL], [BDL] as SAMPNAME, [BDL] = 'LABNAME', [NOTES1], [SAMPLER], [ORDNO], [UNITS] From [CUSTOMER]

Just setting the empty field = to the constant I wanted, with single quotes, works.

Al Komarek SSC Rookie Points: 43 More actions · Answer 4

I found out something else about using constants in a DTS to textfile transfer. You don't need to have an existing empty field in the source database for reference, all you need to do in the SQL statement is act like it does exist, as in the following example where there is no source field named "LABNAME", i.e. Select [X], [Y], [LABNAME] = 'COT-Northside Lab', [Z] FROM TABLENAME It will output the field into the textfile as if the field actually existed in the source db.