Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Is there a way to rewrite an INSERT INTO ...  SELECT clause in which a target field is smaller than the target field ?

Posted on 2009-05-04
3
Medium Priority
?
352 Views
Last Modified: 2013-12-05
I am developing an application using SQL Server 2000 as the back end database.

Is there a way to rewrite an INSERT INTO ...  SELECT clause in which the target table dbo.LOCATION_ALL has a CITY field defined as VARCHAR 15
while the source table dbo.LOCATION_ALL_MOD has a CITY field defined as VARCHAR 50 ?

If I can avoid doing so, I don't want to change the field lengths for the field CITY.

I currently get the error:
Server: Msg 8152, Level 16, State 9, Line 3
String or binary data would be truncated.
The statement has been terminated.


 


INSERT INTO dbo.LOCATION_ALL
      ([Location_ID], [Description], [Address1], [Address2], [City], [State], [Zip], [CostCenter],
            [PropertyStatus], [LeasedSpace], [LeaseStatus], [EndDate])
SELECT [LocationID], [Description], [Address1], [Address2], [City], [State], [Zip], [Cost Center],
            [Property Status], [LeasedSpace], [Lease Status], [EndDate]
FROM dbo.LOCATION_ALL_MOD
0
Comment
Question by:zimmer9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 6

Expert Comment

by:bokist
ID: 24297698
Try this

NSERT INTO dbo.LOCATION_ALL
      ([Location_ID], [Description], [Address1], [Address2], [City], [State], [Zip], [CostCenter],
            [PropertyStatus], [LeasedSpace], [LeaseStatus], [EndDate])
SELECT [LocationID], [Description], [Address1], [Address2], left([City],1,15), [State], [Zip], [Cost Center],
            [Property Status], [LeasedSpace], [Lease Status], [EndDate]
FROM dbo.LOCATION_ALL_MOD
0
 
LVL 2

Expert Comment

by:alfil_28
ID: 24297719
Hi
Maybe using al sql functin might help.
Have you tried using the
LEFT ( character_expression , integer_expression )
on the select clause that might give the size you are looking for.

Hope this helps
0
 
LVL 6

Accepted Solution

by:
bokist earned 2000 total points
ID: 24297780
Oops, small mistake in LEFT function
This is right way :
INSERT INTO dbo.LOCATION_ALL
      ([Location_ID], [Description], [Address1], [Address2], [City], [State], [Zip], [CostCenter],
            [PropertyStatus], [LeasedSpace], [LeaseStatus], [EndDate])
SELECT [LocationID], [Description], [Address1], [Address2], left([City],15), [State], [Zip], [Cost Center],
            [Property Status], [LeasedSpace], [Lease Status], [EndDate]
FROM dbo.LOCATION_ALL_MOD

you can use also substring function :

INSERT INTO dbo.LOCATION_ALL
      ([Location_ID], [Description], [Address1], [Address2], [City], [State], [Zip], [CostCenter],
            [PropertyStatus], [LeasedSpace], [LeaseStatus], [EndDate])
SELECT [LocationID], [Description], [Address1], [Address2], substring([City],1,15), [State], [Zip], [Cost Center],
            [Property Status], [LeasedSpace], [Lease Status], [EndDate]
FROM dbo.LOCATION_ALL_MOD



0

Featured Post

Give Your Engineering Team a Productivity Boost

Learn why container technology is so powerful and how it can provide your team with productivity gains and other benefits.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

730 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