Aleks
asked on
Select into another table (Set field lenght and type)
I have the sql below. It successfully copies data from one table to another. The problem is that it will copy the exact data type and length from table A to table B. Because I will use this to move over to another database with different data lengths I get errors saying data may be truncated. I am not worried about it because sometimes the data type is only 5 characters shorter and the data is not even close to the maximum.
Is there a way I can define the data type and length of the field that should be created on the new table ?
This is my query:
--- Export Users (contacts)---
SELECT
IndivID AS useridtracker ,
'1' AS Firmid ,
'contact' AS usertype ,
IFirst AS FirstNm ,
IMiddle AS MiddleNm ,
ILast AS LastNM ,
'1' AS contactstatus ,
'1' AS FirmaddressIdcon ,
'0' AS ordernum ,
ITitle AS Position ,
EmpID AS EmployerID ,
OfficeAddress AS MailStr ,
OfficeApt AS MailApt ,
OfficeCity AS MailCity ,
OfficeState AS MailState ,
OfficeZip MailZip ,
OfficeCountry MailCntry ,
HomeAttn AS ResCareOf ,
HomeAddress AS ResStr ,
HomeApt AS ResApt ,
HomeCity AS ResCity ,
HomeState AS ResState ,
HomeZip AS ResZip ,
HomeCountry AS ResCntry ,
ForeignAddress AS AbrdStr ,
ForeignApt AS AbrdApt ,
ForeignCity AS AbrdCity ,
ForeignState AS AbrdState ,
ForeignZip AS AbrdZip ,
ForeignCountry AS AbrdCntry ,
HomePhone AS EvePhone ,
OfficePhone AS DayPhone ,
OfficeFax AS Fax ,
OfficeEMail AS email ,
CellPhone AS celphone ,
Nationality AS Nationality ,
dob AS Dob ,
dob AS DobD ,
MainStaffContact AS ParalegalID ,
Atty AS Attyid ,
Active AS Archivedcont ,
INotes AS Notes ,
PublicNotes AS Notes2 ,
BirthCountry AS POBCountry ,
CurrentStatus AS NiStatus ,
StatusExpires AS ExpiresOn ,
StatusExpires AS ExpiresOnD ,
I797Expires AS I797Date ,
I797Expires AS I797DateD ,
VisaExpires AS VisaExp ,
VisaExpires AS VisaExpD ,
VisaMax AS NIVMaxStatus ,
EADExpires AS EadDate ,
EADExpires AS EadDateD ,
APExpires AS ApDate ,
APExpires AS ApDateD ,
EADAPExpires AS AddAPExpires ,
PriorityDate AS AddPriorityDate ,
PrefNbr AS Notes3 ,
Salutation AS Title,
'1' AS Imported
INTO BlueDotUsersMaincontacts
FROM dbo.Individual;
So for example on the fourth item: IFirst AS FirstNm ,
I want to set FirstNm to be a varchar (30) on the target table (BlueDotUsersMaincontacts)
How can I do this ?
Is there a way I can define the data type and length of the field that should be created on the new table ?
This is my query:
--- Export Users (contacts)---
SELECT
IndivID AS useridtracker ,
'1' AS Firmid ,
'contact' AS usertype ,
IFirst AS FirstNm ,
IMiddle AS MiddleNm ,
ILast AS LastNM ,
'1' AS contactstatus ,
'1' AS FirmaddressIdcon ,
'0' AS ordernum ,
ITitle AS Position ,
EmpID AS EmployerID ,
OfficeAddress AS MailStr ,
OfficeApt AS MailApt ,
OfficeCity AS MailCity ,
OfficeState AS MailState ,
OfficeZip MailZip ,
OfficeCountry MailCntry ,
HomeAttn AS ResCareOf ,
HomeAddress AS ResStr ,
HomeApt AS ResApt ,
HomeCity AS ResCity ,
HomeState AS ResState ,
HomeZip AS ResZip ,
HomeCountry AS ResCntry ,
ForeignAddress AS AbrdStr ,
ForeignApt AS AbrdApt ,
ForeignCity AS AbrdCity ,
ForeignState AS AbrdState ,
ForeignZip AS AbrdZip ,
ForeignCountry AS AbrdCntry ,
HomePhone AS EvePhone ,
OfficePhone AS DayPhone ,
OfficeFax AS Fax ,
OfficeEMail AS email ,
CellPhone AS celphone ,
Nationality AS Nationality ,
dob AS Dob ,
dob AS DobD ,
MainStaffContact AS ParalegalID ,
Atty AS Attyid ,
Active AS Archivedcont ,
INotes AS Notes ,
PublicNotes AS Notes2 ,
BirthCountry AS POBCountry ,
CurrentStatus AS NiStatus ,
StatusExpires AS ExpiresOn ,
StatusExpires AS ExpiresOnD ,
I797Expires AS I797Date ,
I797Expires AS I797DateD ,
VisaExpires AS VisaExp ,
VisaExpires AS VisaExpD ,
VisaMax AS NIVMaxStatus ,
EADExpires AS EadDate ,
EADExpires AS EadDateD ,
APExpires AS ApDate ,
APExpires AS ApDateD ,
EADAPExpires AS AddAPExpires ,
PriorityDate AS AddPriorityDate ,
PrefNbr AS Notes3 ,
Salutation AS Title,
'1' AS Imported
INTO BlueDotUsersMaincontacts
FROM dbo.Individual;
So for example on the fourth item: IFirst AS FirstNm ,
I want to set FirstNm to be a varchar (30) on the target table (BlueDotUsersMaincontacts)
How can I do this ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER