SQL table INSERT

Hoyt81
Hoyt81 used Ask the Experts™
on
I am trying to insert the colums listed from one database(SANDBOX) into another database (MasterPriceList) on the same server. However when i try to execute, i get the following error:

"Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
The statement has been terminated."

PK = ID
I am able to query the Sandbox data.
The MasterPriceList PART table has been created, but there are no records.

Thanks!

INSERT 
	MasterPriceList.dbo.PART (
	ID, 
	DESCRIPTION, 
	PRODUCT_CODE, 
	COMMODITY_CODE, 
	UNIT_PRICE, 
	QTY_ON_HAND, 
	USER_8, 
	MODIFIED_DATE
)
SELECT		
	ID, 
	DESCRIPTION, 
	PRODUCT_CODE, 
	COMMODITY_CODE, 
	UNIT_PRICE, 
	QTY_ON_HAND, 
	USER_8, 
	MODIFIED_DATE
FROM SANDBOX.dbo.PART 

WHERE NOT EXISTS (
	SELECT 
	ID, 
	DESCRIPTION, 
	PRODUCT_CODE, 
	COMMODITY_CODE, 
	UNIT_PRICE, 
	QTY_ON_HAND, 
	USER_8, 
	MODIFIED_DATE
	FROM MasterPriceList.dbo.Part  
	WHERE MasterPriceList.dbo.Part.ID = SANDBOX.dbo.PART.ID
)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009
Commented:
check the length of the datatypes (both the source and destination )

Author

Commented:
nothing longer than a varchar(30)
First of all if ID column is the Primakry Key in MasterPriceList u cannot insert a row with ID value into the table without turning ON the identity insert on the table.
 try the following SQL

INSERT  
        MasterPriceList.dbo.PART (
        ID,  
        DESCRIPTION,  
        PRODUCT_CODE,  
        COMMODITY_CODE,  
        UNIT_PRICE,  
        QTY_ON_HAND,  
        USER_8,  
        MODIFIED_DATE
)
SELECT          
        ID,  
        DESCRIPTION,  
        PRODUCT_CODE,  
        COMMODITY_CODE,  
        UNIT_PRICE,  
        QTY_ON_HAND,  
        USER_8,  
        MODIFIED_DATE
FROM SANDBOX.dbo.PART  
WHERE ID NOT in ( SELECT   ID  FROM MasterPriceList.dbo.Part  )
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Thansk for the reply, but this sql results in same error.
What is the column type of Modified_Date

Author

Commented:
datetime
can you please post the table structure with datatypes of the both  tables.
Can u make sure that the columns types and lengths in the both the databases are the same.
It says that while inserting the data it cannot fit the data from source to destination and hence it fails to insert the data. U probably have longer data lengths in the source than in ur destination.

Author

Commented:
Destination:
INSERT INTO [MasterPriceList].[dbo].[PART]
           ([ID]
           ,[DESCRIPTION]
           ,[PRODUCT_CODE]
           ,[COMMODITY_CODE]
           ,[UNIT_PRICE]
           ,[QTY_ON_HAND]
           ,[USER_8]
           ,[MODIFIED_DATE])
     VALUES
           (<ID, varchar(30),>
           ,<DESCRIPTION, varchar(1),>
           ,<PRODUCT_CODE, varchar(15),>
           ,<COMMODITY_CODE, varchar(15),>
           ,<UNIT_PRICE, decimal(15,6),>
           ,<QTY_ON_HAND, decimal(14,4),>
           ,<USER_8, varchar(20),>
           ,<MODIFIED_DATE, datetime,>)




Thanks
Source:
	[SANDBOX].[dbo].[PART]
           ([ID]
           ,[DESCRIPTION]
           ,[STOCK_UM]
           ,[PLANNING_LEADTIME]
           ,[ORDER_POLICY]
           ,[ORDER_POINT]
           ,[SAFETY_STOCK_QTY]
           ,[FIXED_ORDER_QTY]
           ,[DAYS_OF_SUPPLY]
           ,[MINIMUM_ORDER_QTY]
           ,[MAXIMUM_ORDER_QTY]
           ,[ENGINEERING_MSTR]
           ,[PRODUCT_CODE]
           ,[COMMODITY_CODE]
           ,[MFG_NAME]
           ,[MFG_PART_ID]
           ,[FABRICATED]
           ,[PURCHASED]
           ,[STOCKED]
           ,[DETAIL_ONLY]
           ,[DEMAND_HISTORY]
           ,[TOOL_OR_FIXTURE]
           ,[INSPECTION_REQD]
           ,[WEIGHT]
           ,[WEIGHT_UM]
           ,[DRAWING_ID]
           ,[DRAWING_REV_NO]
           ,[PREF_VENDOR_ID]
           ,[PRIMARY_WHS_ID]
           ,[PRIMARY_LOC_ID]
           ,[BACKFLUSH_WHS_ID]
           ,[BACKFLUSH_LOC_ID]
           ,[INSPECT_WHS_ID]
           ,[INSPECT_LOC_ID]
           ,[MRP_REQUIRED]
           ,[MRP_EXCEPTIONS]
           ,[PRIVATE_UM_CONV]
           ,[AUTO_BACKFLUSH]
           ,[PLANNER_USER_ID]
           ,[BUYER_USER_ID]
           ,[ABC_CODE]
           ,[ANNUAL_USAGE_QTY]
           ,[INVENTORY_LOCKED]
           ,[UNIT_MATERIAL_COST]
           ,[UNIT_LABOR_COST]
           ,[UNIT_BURDEN_COST]
           ,[UNIT_SERVICE_COST]
           ,[BURDEN_PERCENT]
           ,[BURDEN_PER_UNIT]
           ,[PURC_BUR_PERCENT]
           ,[PURC_BUR_PER_UNIT]
           ,[FIXED_COST]
           ,[UNIT_PRICE]
           ,[NEW_MATERIAL_COST]
           ,[NEW_LABOR_COST]
           ,[NEW_BURDEN_COST]
           ,[NEW_SERVICE_COST]
           ,[NEW_BURDEN_PERCENT]
           ,[NEW_BURDEN_PERUNIT]
           ,[NEW_FIXED_COST]
           ,[MAT_GL_ACCT_ID]
           ,[LAB_GL_ACCT_ID]
           ,[BUR_GL_ACCT_ID]
           ,[SER_GL_ACCT_ID]
           ,[QTY_ON_HAND]
           ,[QTY_AVAILABLE_ISS]
           ,[QTY_AVAILABLE_MRP]
           ,[QTY_ON_ORDER]
           ,[QTY_IN_DEMAND]
           ,[USER_1]
           ,[USER_2]
           ,[USER_3]
           ,[USER_4]
           ,[USER_5]
           ,[USER_6]
           ,[USER_7]
           ,[USER_8]
           ,[USER_9]
           ,[USER_10]
           ,[NMFC_CODE_ID]
           ,[PACKAGE_TYPE]
           ,[WHSALE_UNIT_COST]
           ,[MRP_EXCEPTION_INFO]
           ,[MULTIPLE_ORDER_QTY]
           ,[ADD_FORECAST]
           ,[UDF_LAYOUT_ID]
           ,[PIECE_TRACKED]
           ,[LENGTH_REQD]
           ,[WIDTH_REQD]
           ,[HEIGHT_REQD]
           ,[DIMENSIONS_UM]
           ,[SHIP_DIMENSIONS]
           ,[DRAWING_FILE]
           ,[TARIFF_CODE]
           ,[TARIFF_TYPE]
           ,[ORIG_COUNTRY_ID]
           ,[NET_WEIGHT_2]
           ,[GROSS_WEIGHT_2]
           ,[WEIGHT_UM_2]
           ,[VOLUME]
           ,[VOLUME_UM]
           ,[EXCISE_UNIT_PRICE]
           ,[VAT_CODE]
           ,[DEMAND_FENCE_1]
           ,[DEMAND_FENCE_2]
           ,[ROLL_FORECAST]
           ,[CONSUMABLE]
           ,[PRIMARY_SOURCE]
           ,[LABEL_UM]
           ,[HTS_CODE]
           ,[DEF_ORIG_COUNTRY]
           ,[MATERIAL_CODE]
           ,[DEF_LBL_FORMAT_ID]
           ,[VOLATILE_LEADTIME]
           ,[LT_PLUS_DAYS]
           ,[LT_MINUS_DAYS]
           ,[STATUS]
           ,[USE_SUPPLY_BEF_LT]
           ,[QTY_COMMITTED]
           ,[intrastat_exempt]
           ,[CASE_QTY]
           ,[PALLET_QTY]
           ,[MINIMUM_LEADTIME]
           ,[LEADTIME_BUFFER]
           ,[EMERGENCY_STOCKPCT]
           ,[REPLENISH_LEVEL]
           ,[MIN_BATCH_SIZE]
           ,[EFF_DATE_PRICE]
           ,[ECN_REVISION]
           ,[REVISION_ID]
           ,[STAGE_ID]
           ,[ECN_REV_CONTROL]
           ,[IS_KIT]
           ,[YELLOW_STOCKPCT]
           ,[UNIV_PLAN_MATERIAL]
           ,[RLS_NEAR_DAYS]
           ,[SUGG_RLS_NEAR_DAYS]
           ,[MODIFIED_DATE])
     VALUES
           (<ID, varchar(30),>
           ,<DESCRIPTION, varchar(40),>
           ,<STOCK_UM, varchar(15),>
           ,<PLANNING_LEADTIME, smallint,>
           ,<ORDER_POLICY, char(1),>
           ,<ORDER_POINT, decimal(14,4),>
           ,<SAFETY_STOCK_QTY, decimal(14,4),>
           ,<FIXED_ORDER_QTY, decimal(14,4),>
           ,<DAYS_OF_SUPPLY, smallint,>
           ,<MINIMUM_ORDER_QTY, decimal(14,4),>
           ,<MAXIMUM_ORDER_QTY, decimal(14,4),>
           ,<ENGINEERING_MSTR, varchar(3),>
           ,<PRODUCT_CODE, varchar(15),>
           ,<COMMODITY_CODE, varchar(15),>
           ,<MFG_NAME, varchar(30),>
           ,<MFG_PART_ID, varchar(30),>
           ,<FABRICATED, char(1),>
           ,<PURCHASED, char(1),>
           ,<STOCKED, char(1),>
           ,<DETAIL_ONLY, char(1),>
           ,<DEMAND_HISTORY, char(1),>
           ,<TOOL_OR_FIXTURE, char(1),>
           ,<INSPECTION_REQD, char(1),>
           ,<WEIGHT, decimal(14,4),>
           ,<WEIGHT_UM, varchar(15),>
           ,<DRAWING_ID, varchar(30),>
           ,<DRAWING_REV_NO, varchar(8),>
           ,<PREF_VENDOR_ID, varchar(15),>
           ,<PRIMARY_WHS_ID, varchar(15),>
           ,<PRIMARY_LOC_ID, varchar(15),>
           ,<BACKFLUSH_WHS_ID, varchar(15),>
           ,<BACKFLUSH_LOC_ID, varchar(15),>
           ,<INSPECT_WHS_ID, varchar(15),>
           ,<INSPECT_LOC_ID, varchar(15),>
           ,<MRP_REQUIRED, char(1),>
           ,<MRP_EXCEPTIONS, char(1),>
           ,<PRIVATE_UM_CONV, char(1),>
           ,<AUTO_BACKFLUSH, char(1),>
           ,<PLANNER_USER_ID, varchar(20),>
           ,<BUYER_USER_ID, varchar(20),>
           ,<ABC_CODE, char(1),>
           ,<ANNUAL_USAGE_QTY, decimal(15,4),>
           ,<INVENTORY_LOCKED, char(1),>
           ,<UNIT_MATERIAL_COST, decimal(15,6),>
           ,<UNIT_LABOR_COST, decimal(15,6),>
           ,<UNIT_BURDEN_COST, decimal(15,6),>
           ,<UNIT_SERVICE_COST, decimal(15,6),>
           ,<BURDEN_PERCENT, decimal(5,2),>
           ,<BURDEN_PER_UNIT, decimal(15,6),>
           ,<PURC_BUR_PERCENT, decimal(6,3),>
           ,<PURC_BUR_PER_UNIT, decimal(15,6),>
           ,<FIXED_COST, decimal(15,2),>
           ,<UNIT_PRICE, decimal(15,6),>
           ,<NEW_MATERIAL_COST, decimal(14,4),>
           ,<NEW_LABOR_COST, decimal(14,4),>
           ,<NEW_BURDEN_COST, decimal(14,4),>
           ,<NEW_SERVICE_COST, decimal(14,4),>
           ,<NEW_BURDEN_PERCENT, decimal(5,2),>
           ,<NEW_BURDEN_PERUNIT, decimal(15,6),>
           ,<NEW_FIXED_COST, decimal(15,2),>
           ,<MAT_GL_ACCT_ID, varchar(30),>
           ,<LAB_GL_ACCT_ID, varchar(30),>
           ,<BUR_GL_ACCT_ID, varchar(30),>
           ,<SER_GL_ACCT_ID, varchar(30),>
           ,<QTY_ON_HAND, decimal(14,4),>
           ,<QTY_AVAILABLE_ISS, decimal(14,4),>
           ,<QTY_AVAILABLE_MRP, decimal(14,4),>
           ,<QTY_ON_ORDER, decimal(14,4),>
           ,<QTY_IN_DEMAND, decimal(14,4),>
           ,<USER_1, varchar(80),>
           ,<USER_2, varchar(80),>
           ,<USER_3, varchar(80),>
           ,<USER_4, varchar(80),>
           ,<USER_5, varchar(80),>
           ,<USER_6, varchar(80),>
           ,<USER_7, varchar(80),>
           ,<USER_8, varchar(80),>
           ,<USER_9, varchar(80),>
           ,<USER_10, varchar(80),>
           ,<NMFC_CODE_ID, varchar(15),>
           ,<PACKAGE_TYPE, varchar(5),>
           ,<WHSALE_UNIT_COST, decimal(15,6),>
           ,<MRP_EXCEPTION_INFO, varchar(80),>
           ,<MULTIPLE_ORDER_QTY, decimal(14,4),>
           ,<ADD_FORECAST, char(1),>
           ,<UDF_LAYOUT_ID, varchar(15),>
           ,<PIECE_TRACKED, char(1),>
           ,<LENGTH_REQD, char(1),>
           ,<WIDTH_REQD, char(1),>
           ,<HEIGHT_REQD, char(1),>
           ,<DIMENSIONS_UM, varchar(15),>
           ,<SHIP_DIMENSIONS, varchar(50),>
           ,<DRAWING_FILE, varchar(100),>
           ,<TARIFF_CODE, varchar(15),>
           ,<TARIFF_TYPE, varchar(20),>
           ,<ORIG_COUNTRY_ID, varchar(15),>
           ,<NET_WEIGHT_2, decimal(11,2),>
           ,<GROSS_WEIGHT_2, decimal(11,2),>
           ,<WEIGHT_UM_2, varchar(15),>
           ,<VOLUME, decimal(11,2),>
           ,<VOLUME_UM, varchar(15),>
           ,<EXCISE_UNIT_PRICE, decimal(15,6),>
           ,<VAT_CODE, varchar(15),>
           ,<DEMAND_FENCE_1, int,>
           ,<DEMAND_FENCE_2, int,>
           ,<ROLL_FORECAST, char(1),>
           ,<CONSUMABLE, char(1),>
           ,<PRIMARY_SOURCE, char(3),>
           ,<LABEL_UM, varchar(15),>
           ,<HTS_CODE, varchar(20),>
           ,<DEF_ORIG_COUNTRY, varchar(15),>
           ,<MATERIAL_CODE, varchar(25),>
           ,<DEF_LBL_FORMAT_ID, varchar(30),>
           ,<VOLATILE_LEADTIME, char(1),>
           ,<LT_PLUS_DAYS, int,>
           ,<LT_MINUS_DAYS, int,>
           ,<STATUS, char(1),>
           ,<USE_SUPPLY_BEF_LT, char(1),>
           ,<QTY_COMMITTED, decimal(14,4),>
           ,<intrastat_exempt, varchar(1),>
           ,<CASE_QTY, decimal(14,4),>
           ,<PALLET_QTY, decimal(14,4),>
           ,<MINIMUM_LEADTIME, smallint,>
           ,<LEADTIME_BUFFER, smallint,>
           ,<EMERGENCY_STOCKPCT, int,>
           ,<REPLENISH_LEVEL, decimal(14,4),>
           ,<MIN_BATCH_SIZE, decimal(14,4),>
           ,<EFF_DATE_PRICE, char(1),>
           ,<ECN_REVISION, char(1),>
           ,<REVISION_ID, varchar(8),>
           ,<STAGE_ID, varchar(15),>
           ,<ECN_REV_CONTROL, char(1),>
           ,<IS_KIT, char(1),>
           ,<YELLOW_STOCKPCT, int,>
           ,<UNIV_PLAN_MATERIAL, varchar(1),>
           ,<RLS_NEAR_DAYS, smallint,>
           ,<SUGG_RLS_NEAR_DAYS, smallint,>
           ,<MODIFIED_DATE, datetime,>)

Open in new window

DESCRIPTION, varchar(1),>     D<DESCRIPTION, varchar(40),>

Source table is longer than the target
there you go the source table is longer than the destination as I expected. U have to trim the source to fit into the destination

Author

Commented:
Once i matched up data source and destination datatype lengths, it worked perfectly.
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial