Access_Data_Migration/Part Insert.sql

select 'insert into "PartNumber"("partNumber1", "description", "comments", "primaryCustomerPartNumber", "primaryVendorPartNumber", "secondaryVendorPartNumber"'
 + ', "ppap", "file", "stdCost", "listPrice", "createDate", "createUserId", "updateDate", "updateUserId", "partCodeId", "statusId"'
 + ', "primaryCustomerId", "primaryVendorId", "secondaryVendorId", "pickingLocationId", "packingLocationId", "replacedById", "replacesId"'
 + ', "weight") values('
 + ''''
 + a.partNumber1
 +''''
 + case 
		when a.description IS NULL 
		then ', null' 
		else 
			', ' + ''''
			+a.description
			+ ''''
	end
 + case 
		when a.comments IS NULL 
		then ', null' 
		else 
			', ' + ''''
			+a.comments
			+ '''' 
	end
 + case 
		when a.primaryCustomerPartNumber IS NULL 
		then ', null' 
		else 
			', ' + ''''
			+a.primaryCustomerPartNumber 
			+ ''''
	end
 + case 
		when a.primaryVendorPartNumber IS NULL 
		then ', null' 
		else 
			', ' + ''''
			+a.primaryVendorPartNumber
			+ '''' 
	end
 + case 
		when a.secondaryVendorPartNumber IS NULL 
		then ', null' 
		else 
			', ' + ''''
			+a.secondaryVendorPartNumber
			+ '''' 
	end
 + ', ' 
 +  case when a.ppap = 1 then 'true' else 'false' end
 + case 
		when a.[file] IS NULL 
		then ', null' 
		else 
			', ' + ''''
			+a.[file]
			+ '''' 
	end
 + case 
		when a.stdCost IS NULL 
		then ', null' 
		else 
			', ' + ''''
			+a.stdCost
			+ '''' 
	end
 + case 
		when a.listPrice IS NULL 
		then ', null' 
		else 
			', ' + ''''
			+a.listPrice
			+ '''' 
	end
 + case when a.createDate IS null then ', null'
	else 
	', ' + ''''
	+ cast(a.createDate as varchar(max))
	+ ''''
	end 
+', '
 + cast(a.createUserId as varchar(max))
 + case when a.updateDate is null then ', null'
	else	
	', ' + ''''
	+ CAST(a.updateDate as varchar(max))
	+ ''''
	end
 + ', ' 
 + cast(a.updateUserId as varchar(max))
 + ', ' + cast(a.partCodeId as varchar(max))
 + ', ' + cast(a.statusId as varchar(max))
 + ', ' + case when a.primaryCustomerId is null then 'null' else cast(a.primaryCustomerId as varchar(max)) end
 + ', ' + case when a.primaryVendorId is null then 'null' else cast(a.primaryVendorId as varchar(max)) end
 + ', ' + case when a.secondaryVendorId is null then 'null' else cast(a.secondaryVendorId as varchar(max)) end
 + ', ' + case when a.pickingLocationId is null then 'null' else cast(a.pickingLocationId as varchar(max)) end
 + ', ' + case when a.packingLocationId is null then 'null' else cast(a.packingLocationId as varchar(max)) end
 + ', ' + case when a.replacedById is null then 'null' else cast(a.replacedById as varchar(max)) end
 + ', ' + case when a.replacesId is null then 'null' else cast(a.replacesId as varchar(max)) end
 + ', ' + case when a.weight is null then 'null' else '''' + a.weight + '''' end
 + ');'
from
(
select 
	[RPM Part Number] as partNumber1,
	case when LEN([Part Description]) = 0 then null else  replace([Part Description] , '''', '') end as description,
	case when LEN(Comments) = 0 then null else  replace(Comments, '''', '')end as comments,
	case when LEN([Customer Part Number]) = 0 then null else replace([Customer Part Number], '''', '') end as primaryCustomerPartNumber,
	case when len([Primary Vendor Part #]) = 0 then null else REPLACE([Primary Vendor Part #], '''', '') end as primaryVendorPartNumber,
	case when len([Secondary Vendor Part #]) = 0 then null else replace([Secondary Vendor Part #], '''', '') end as secondaryVendorPartNumber,
	[PPAP] as ppap,
	case when len([File]) = 0 then null else REPLACE([File], '''', '') end as [file],
	case when len(Cost) = 0 then null else replace(Cost, '''', '') end as stdCost,
	case when len(List) = 0 then null else replace(List, '''', '') end as listPrice,
	createDate as createDate,
	-1 as createUserId,
	createDate as updateDate,
	-1 as updateUserId,
	partCodeId as partCodeId,
	statusId as statusId,
	primaryCustomerId as primaryCustomerId,
	primaryVendorId as primaryVendorId,
	secondaryVendorId as secondaryVendorId,
	pickingLocationId as pickingLocationId,
	packingLocationId as packingLocationId,
	null as replacedById,
	null as replacesId,
	replace(Weight, '''', '') as weight
from z_parts
) a

--select Date, Signed from z_parts where createDate is null
--update z_parts set  createDate = CAST([Date] as datetime) where myid = 10061
--declare @int int
--set @int = 1
--while @int < 21668
--begin
--select CAST(Date as datetime), @int from z_parts where myid = @int
--select @int = @int + 1
--end