SQL table INSERT
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!
"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
)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 )
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 )
ASKER
Thansk for the reply, but this sql results in same error.
What is the column type of Modified_Date
ASKER
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.
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.
ASKER
Destination:
INSERT INTO [MasterPriceList].[dbo].[P
([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
INSERT INTO [MasterPriceList].[dbo].[P
([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,>)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Once i matched up data source and destination datatype lengths, it worked perfectly.
Thanks
Thanks
ASKER