SELECT INTO define columns datatype

  • Thread starter Thread starter thomasamillergoogle
  • Start date Start date
T

thomasamillergoogle

SELECT [Main(DPI)].CaseNumber, [Main(DPI)].Title AS CaseTitle, ' ' AS
Recommendations, ' ' AS Conclusions , ' ' AS RelatedToAllegation, ' '
AS RelatedToManagementControls, ' ' AS ReviewCloseDate, ' ' AS
CaseComments1

INTO [Legacy]
FROM [SUBJ/REC(DPI)] RIGHT JOIN [Main(DPI)] ON
[SUBJ/REC(DPI)].SubjectID = [Main(DPI)].SubjectID
WITH OWNERACCESS OPTION;



When this Access SQL statement is executed the resulting table [Legacy]
has a datatype of TEXT for the CaseComments1 column. I would like to
define it explicitly as MEMO. How can this be accomplished?
 
SELECT [Main(DPI)].CaseNumber, [Main(DPI)].Title AS CaseTitle, ' ' AS
Recommendations, ' ' AS Conclusions , ' ' AS RelatedToAllegation, ' '
AS RelatedToManagementControls, ' ' AS ReviewCloseDate, ' ' AS
CaseComments1

INTO [Legacy]
FROM [SUBJ/REC(DPI)] RIGHT JOIN [Main(DPI)] ON
[SUBJ/REC(DPI)].SubjectID = [Main(DPI)].SubjectID
WITH OWNERACCESS OPTION;



When this Access SQL statement is executed the resulting table
[Legacy] has a datatype of TEXT for the CaseComments1 column. I would
like to define it explicitly as MEMO. How can this be accomplished?
Click to expand...

You can't do it with a SELECT INTO statement. I'd recommend you use two
SQL statements in succession: first a CREATE TABLE statement, which
will let you define the data type of every field in the output table,
and then an INSERT statement to select records and append them to the
new table.
 
Back
Top