Solved

sql 2008  data import problem

Posted on 2010-09-15
4
210 Views
Last Modified: 2012-05-10
I am having issues with a daily import routine i have set up.
The idea is that our locations send their sales data back every day to our Head Office. This is dumped in a sql database. I then extract some of this data and import into another sql database which also houses our ERP solution. Our Erp package then processes the data and post sales invoices , stock used etc.
The problem occurs when there is a communication issue with any of the stores and we dont their data for a few days. My sql code just inserts yesterdays sales, which is fine if all the sales are in. In reality I have to run many small insert scripts during the week to insert data from stores that dont poll their data when they should.
I am trying to think of a more practical way to manage this. The first database gets updated automatically when a store sends their data, the second database doesn't. Instead of running my scruipt for yesterdays data, would it be possible to write a script to insert sales for any of the last 7 days if data has not been inserted already. This would allow for data coming in late (within a weelk anyway) Is this even possible?

Thanks for Reading

Noreen
INSERT INTO [Supermacs Live].[dbo].[Supermac_s - Live$Outlet Sales]
           ([Fiscal DateTime]
           ,[Restaurant Code]
           ,[Item Code]
           ,[PLU Code]
           ,[Description]
           ,[Qty]
           ,[Unit Price Inc_ VAT])
SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, FiscalDate))), 
           [Maitred].dbo.MDInvoice.RestaurantCode,
           [Maitred].dbo.MDInvoiceDetail.DescriptionMDID,
           ISNULL([Maitred].dbo.MDInvoiceDetail.CodePLU,0), 
           [Maitred].dbo.MDInvoiceDetail.Description, 
           SUM([Maitred].dbo.MDInvoiceDetail.Quantity) AS Qty, 
           ISNULL([Maitred].dbo.MDInvoiceDetail.UnitPrice,0)
FROM       [Maitred].dbo.MDInvoice INNER JOIN
                      [Maitred].dbo.MDInvoiceDetail ON [Maitred].dbo.MDInvoice.InvoiceId = [Maitred].dbo.MDInvoiceDetail.InvoiceId
WHERE FiscalDate >= DATEADD(DAY,DATEDIFF(DAY,0,CURRENT_TIMESTAMP)-1,0)
   AND FiscalDate < DATEADD(DAY,DATEDIFF(DAY,0,CURRENT_TIMESTAMP) +1,0) AND
      RestaurantCode in (0041,0042,0032,1019,0003,1026,0027,0045,0029,
0022,0005,0031,0012,0036,0039,0016,1057,0025,
0002,0037,0008,0006,0034,0007,1044,0040,0004,1053,1077,1093,1089,1064)         
GROUP BY [Maitred].dbo.MDInvoiceDetail.DescriptionMDID,
[Maitred].dbo.MDInvoice.FiscalDate, 
[Maitred].dbo.MDInvoice.RestaurantCode, 
[Maitred].dbo.MDInvoiceDetail.CodePLU, 
[Maitred].dbo.MDInvoiceDetail.Description, 
[Maitred].dbo.MDInvoiceDetail.UnitPrice

ORDER BY [Maitred].dbo.MDInvoice.RestaurantCode, [Maitred].dbo.MDInvoice.FiscalDate, [Maitred].dbo.MDInvoiceDetail.CodePLU

GO

Open in new window

0
Comment
Question by:jmauel
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
Priya Perumpilavil earned 500 total points
ID: 33681757
please try this

INSERT INTO [Supermacs Live].[dbo].[Supermac_s - Live$Outlet Sales]
           ([Fiscal DateTime]
           ,[Restaurant Code]
           ,[Item Code]
           ,[PLU Code]
           ,[Description]
           ,[Qty]
           ,[Unit Price Inc_ VAT])
SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, FiscalDate))),
           [Maitred].dbo.MDInvoice.RestaurantCode,
           [Maitred].dbo.MDInvoiceDetail.DescriptionMDID,
           ISNULL([Maitred].dbo.MDInvoiceDetail.CodePLU,0),
           [Maitred].dbo.MDInvoiceDetail.Description,
           SUM([Maitred].dbo.MDInvoiceDetail.Quantity) AS Qty,
           ISNULL([Maitred].dbo.MDInvoiceDetail.UnitPrice,0)
FROM       [Maitred].dbo.MDInvoice INNER JOIN
                      [Maitred].dbo.MDInvoiceDetail ON [Maitred].dbo.MDInvoice.InvoiceId = [Maitred].dbo.MDInvoiceDetail.InvoiceId
WHERE
      RestaurantCode in (0041,0042,0032,1019,0003,1026,0027,0045,0029,
0022,0005,0031,0012,0036,0039,0016,1057,0025,
0002,0037,0008,0006,0034,0007,1044,0040,0004,1053,1077,1093,1089,1064)        
GROUP BY [Maitred].dbo.MDInvoiceDetail.DescriptionMDID,
[Maitred].dbo.MDInvoice.FiscalDate,
[Maitred].dbo.MDInvoice.RestaurantCode,
[Maitred].dbo.MDInvoiceDetail.CodePLU,
[Maitred].dbo.MDInvoiceDetail.Description,
[Maitred].dbo.MDInvoiceDetail.UnitPrice
ORDER BY [Maitred].dbo.MDInvoice.RestaurantCode, [Maitred].dbo.MDInvoice.FiscalDate, [Maitred].dbo.MDInvoiceDetail.CodePLU

except

select ([Fiscal DateTime]
           ,[Restaurant Code]
           ,[Item Code]
           ,[PLU Code]
           ,[Description]
           ,[Qty]
           ,[Unit Price Inc_ VAT]) from [Supermacs Live].[dbo].[Supermac_s - Live$Outlet Sales]
0
 
LVL 9

Expert Comment

by:valkyrie_nc
ID: 33681778
Is there any way you could set up an SSIS package to do this?  You could use a Slowly Changing Dimension task in your dataflow to insert new and update (or ignore, depending on how you want to do it) existing data.

hth

valkyrie_nc
0
 
LVL 9

Expert Comment

by:valkyrie_nc
ID: 33681788
Although on review, I like PriyaPadman's solution. :)
0
 

Author Comment

by:jmauel
ID: 33683706
I am trying the code above but have removed the ORDER BY line. Hoever I am getting the following error

Cannot resolve the collation conflict between "Latin1_General_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the EXCEPT operation.


Any ideas.

Am i right in understanding it will compare the two databases and hust add whays new??


Thanks

Noreen
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

810 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