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
340 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 500 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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 …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

617 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