Results 1 to 2 of 2

Thread: select,cast into table using one step

  1. #1
    Join Date
    Sep 2002
    Posts
    218

    select,cast into table using one step

    Is it possible to do a select and a cast at the same time into a table.l'm currently doing it in two steps
    l would like to do this in one step.Is it feasible or l should do it in two steps....

    When casting this field l cant get it to convert the pcode to char (4)
    ltrim(rtrim(CAST(CAST(POS100 AS varchar(20)) AS decimal(10,0)))) AS PCode

    SELECT
    ltrim(rtrim(DEA049)) As LoanRef
    ,ltrim(rtrim(IDN100)) As IDNo
    ,ltrim(rtrim(TIT100)) As Title
    ,ltrim(rtrim( SUR100 )) As Surname
    ,ltrim(rtrim(FN1100 )) As FirstName_1

    ,ltrim(rtrim(FN2100)) As FirstName_2
    ,ltrim(rtrim(AD1100)) As Address_1
    ,ltrim(rtrim(AD2100)) As Address_2
    ,ltrim(rtrim(AD3100)) As Address_3
    ,ltrim(rtrim(AD4100)) As Address_4

    ,ltrim(rtrim(CAST(CAST(POS100 AS varchar(20)) AS decimal(10,0)))) AS PCode
    ,ltrim(rtrim(CAS049)) As Case_No
    ,ltrim(rtrim(Left(ODT049,8))) As Order_Date_Granted
    ,ltrim(rtrim(CVN049)) As Court_Venue
    ,ltrim(rtrim(AOT049 )) As Admin_Order_Type

    ,ltrim(rtrim(ADN049)) As Administrator_Name
    ,ltrim(rtrim(ADC049)) As Administrator_Tel
    ,ltrim(rtrim(AOD049)) As Admin_Order_Description
    ,CAST(CAST(BAL049 AS varchar(20)) AS decimal(10,2)) AS Tot_OS_Bal
    ,ltrim(rtrim(COD043)) As Reason_Code
    ,ltrim(rtrim(left(CDT043,8))) As Reason_Code_Date
    Into AdminBureau_1
    FROM AdminBureau
    Go

    ------
    ALTER TABLE AdminBureau_1 ALTER COLUMN LoanRef VARCHAR(20)
    Go
    ALTER TABLE AdminBureau_1 ALTER COLUMN IDNo VARCHAR(13)
    Go
    ALTER TABLE AdminBureau_1 ALTER COLUMN Title CHAR(3)
    Go
    ALTER TABLE AdminBureau_1 ALTER COLUMN Surname VARCHAR(22)
    Go
    ALTER TABLE AdminBureau_1 ALTER COLUMN FirstName_1 VARCHAR(30)
    Go
    Print 'Phase2 Of Altering Columns Completed Successfully'

    ------------------------------------------------------------------------
    -- ALTER TABLE AdminBureau_1 ALTER COLUMN PCode int
    -- Go
    ALTER TABLE AdminBureau_1 ALTER COLUMN Case_No VARCHAR(10)
    Go
    ALTER TABLE AdminBureau_1 ALTER COLUMN Order_Date_Granted CHAR(8)
    Go
    Print 'Phase3 Of Altering Columns Completed Successfully'

    -------------------------------------------------------------------------
    -- ALTER TABLE AdminBureau_1 ALTER COLUMN CAST(CAST(Tot_OS_Bal AS varchar(20)) AS decimal(10,2))
    -- Go
    ALTER TABLE AdminBureau_1 ALTER COLUMN Reason_Code_Date CHAR(8)
    Go
    Print 'Phase4 Of Altering Columns Completed Successfully'

  2. #2
    Join Date
    Dec 2002
    Posts
    181
    Sleazy,
    There's no reason you can't do the cast at the same time as the select into:

    select cast(ltrim(rtrim(DEA049)) as varchar(20)) As LoanRef Into AdminBureau_1 FROM AdminBureau

    The above will produce the same result without the alter table commands. Also, if this is a fairly common operation, you might consider creating a staging table to avoid the select into operation.


    Jeff

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •