-
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'
-
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
-
Forum Rules
|
|