Solved

additional varchar field using a select into statement

Posted on 2004-04-15
2
426 Views
Last Modified: 2006-11-17
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





0
Comment
Question by:DGWParker
2 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 50 total points
ID: 10833162
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
 

Author Comment

by:DGWParker
ID: 10833228
Thanks Tim
That is perfect
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now