How to define the type (int) for a new field in SQL SELECT INTO statement in MS Access

Question

I want to define the new field in a select into statement as integer. However, the NewField ends up as binary. How can I accomplish this?

SELECT ExistingField1, ExistingField2, NewField
INTO NewTable
FROM ExistingTable

I searched a lot but couldn't find a solution yet.

Edit 1: I am performing the SELECT INTO statement from within the Microsoft Access application itself. (it is not a table in Access that points to a SQL Server table)

Edit 2: NewField is created with the SELECT INTO statement. It does not exist in a pre-existing table.

This question is currently tagged as both 'sql-server' and 'ms-access'. Are you performing the SELECT INTO statement from within the Microsoft Access application itself? Is [ExistingTable] a linked table in Access that points to a SQL Server table? Where does `NewField` come from? Please edit your question to add these details. — Gord Thompson, Nov 18 '13 at 09:03

SchmitzIT · Accepted Answer · 2013-11-19T07:50:24.503

15

The reason it ends up as a binary is because the field in the existing table most likely is a binary field.

You could try doing something like this:

SELECT ExistingField1, ExistingField2, CAST(NewField AS int)
INTO NewTable
FROM ExistingTable

CAST does not work in MsAccess. However, this should work:

SELECT ExistingField1, ExistingField2, cInt(Field1 + Field2) AS NewField
INTO NewTable
FROM ExistingTable

edited Nov 19 '13 at 07:50

answered Nov 18 '13 at 08:38

SchmitzIT

9,227
9
65
92

Thank you for your help. However, I got an error with this: "Syntax error (missing operator) in query expression 'CAST(NewField AS int)'." Addtionally, NewField is not an existing field. It is created with the SELECT INTO statement along with the NewTable. Do I miss sth very basic? – Onur T Nov 19 '13 at 06:47
The new field would still be a result of some kind of operation you perform on existing columns, right? I just Googled and found `CAST` won't work in Access. See my edit. – SchmitzIT Nov 19 '13 at 07:49

score 3 · Answer 2 · answered Jan 29 '14 at 17:30

3

For Access I would use this, then the parameter dialog does not show.

SELECT ExistingField1, cInt(0) AS NewField
into NewTable1
FROM Table1

For SQL Server

CAST(null AS int) AS NewField

answered Jan 29 '14 at 17:30

27k1

2,212
1
22
22

score 0 · Answer 3 · answered Nov 18 '13 at 09:55

0

If you have structure of NewTable already defined you can try:

Insert Into NewTable 
Select 
      ExistingField1,
      ExistingField2,
      cast(NewField as int) as NewField,
From ExistingTable

answered Nov 18 '13 at 09:55

Deepshikha

9,896
2
21
21

How to define the type (int) for a new field in SQL SELECT INTO statement in MS Access

3 Answers3