Go Premium for a chance to win a PS4. Enter to Win

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
?
357 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
  • 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

916 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