Solved

sql 2008  data import problem

Posted on 2010-09-15
4
209 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now