additional varchar field using a select into statement

Could someone please show me the syntax for the following:

I create a new table from an existing table, but want to create an additional varchar field:

select * into table2 from (select f1,f2, ' ' as NewField from table1) s

The newfield now has data type of varchar(1).

If I now want to update this field with data from another table, I receive an error as the updating data is of a length greater than one?

I do not first want to create table2 and then add the field, eg
select * into table2 from (select f1,f2 from table1) s and then
ALTER table2 ADD NewField varchar(25)

OR
I do not want to go into Design Table and then change the  data type once table2 has been created.

OR
select * into table2 from (select f1,f2, '                  ' as NewField from table1) s


Is there sytax something like this?
select * into table2 from (select f1,f2, NewField  as varchar(25) from table1) s





DGWParkerAsked:
Who is Participating?
 
TimCotteeConnect With a Mentor Commented:
Hi DGWParker,

Use

Select * Into Table2 From (Select f1,f2,Space(25) As NewField From Table1) s

Tim Cottee
Brainbench MVP for Visual Basic
http://www.brainbench.com
0
 
DGWParkerAuthor Commented:
Thanks Tim
That is perfect
0
All Courses

From novice to tech pro — start learning today.