Using this script, you can update certain fields like special prices, update your keywords etc.
This will be very useful for proper pricing techniques.
requirements:
Excel older version 2007
SQL Server 2014 Import and Export Data (32-bit)
Coffee!
Backup database.
Remove Field name "Full Description" before import. Contains html code which excel makes errors on import.
Copy the code below and paste into new query. Save this as: Update product table with special prices
Enjoy.
/****** The Update SET statement selects updated data from your edited excel
dbo.ProductUpdated table and Updates certain fields into NopCommerce existing dbo.Product table v 3.8.
***Warning: Complete Backup FULL Metal and SQL backup Now! Works fastest up to 50 million Products.
==========================================================================================
This SQL Code Updates Fields: OldPrice,Price,SpecialPrice,SpecialPriceStartDateTimeUtc,
SpecialPriceEndDateTimeUtc,AdminComment,MetaKeywords,MetaDescription,MetaTitle,
DisplayStockAvailability,DisplayStockQuantity,AllowBackInStockSubscriptions.
This code is great for Sale Starts On date & ends on Date. Also, this script is great for
your "SpecialPrice" ending in .99, =ROUND(B2,0)-0.01 so use excel to round a price to end in .99
===========================================================================================
STEPS:
1)export out dbo.product using TASKS>EXPORT DATA>DATASOURCE SQL SERVER>DESTINATION MSEXCEL
(USE 32BIT VERSION IF MISSING EXCEL)SQL Server 2014 Import and Export Data (32-bit) ONLY!
2)Name your new exported ms excel file TAB dbo.ProductUpdated (this name will work with this script)
3)Watch out for ' in text fields you need it there to work.
4)UpdatedOnUtc MUST BE ADDED WITH PROPER VALUES - NO NULL ALLOWED so just add it.
5)Update field(s) one at a time then run this script each time. ***You may mess up that's why.
6)All 'CreatedOnUtc, 'UpdatedOnUtc time format is '2016-08-23 16:00:00.000 Look at dot at end.000
use this and fill down for start and end times for fields (other time fields too)
'SpecialPriceStartDateTimeUtc 'SpecialPriceEndDateTimeUtc
'2016-08-10 16:00:00.000 '2016-09-11 09:30:00.000 (never forget!)
Tested with 2003-2007 excel works with SQL Server 2014 Import and Export Data (32-bit).
******/
UPDATE [dbo].[Product]
SET [dbo].[Product].OldPrice = [dbo].[ProductUpdated].OldPrice,
[dbo].[Product].Price = [dbo].[ProductUpdated].Price,
[dbo].[Product].SpecialPrice = [dbo].[ProductUpdated].SpecialPrice,
[dbo].[Product].SpecialPriceStartDateTimeUtc = [dbo].[ProductUpdated].SpecialPriceStartDateTimeUtc,
[dbo].[Product].SpecialPriceEndDateTimeUtc = [dbo].[ProductUpdated].SpecialPriceEndDateTimeUtc,
[dbo].[Product].AdminComment = [dbo].[ProductUpdated].AdminComment,
[dbo].[Product].MetaKeywords = [dbo].[ProductUpdated].MetaKeywords,
[dbo].[Product].MetaDescription = [dbo].[ProductUpdated].MetaDescription,
[dbo].[Product].MetaTitle = [dbo].[ProductUpdated].MetaTitle,
[dbo].[Product].DisplayStockAvailability = [dbo].[ProductUpdated].DisplayStockAvailability,
[dbo].[Product].DisplayStockQuantity = [dbo].[ProductUpdated].DisplayStockQuantity,
[dbo].[Product].AllowBackInStockSubscriptions = [dbo].[ProductUpdated].AllowBackInStockSubscriptions
FROM [dbo].[Product], [dbo].[ProductUpdated]
WHERE [dbo].[Product].Sku = [dbo].[ProductUpdated].Sku