sql 2008 data import problem

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

jmauelAsked:
Who is Participating?
 
Priya PerumpilavilSoftware EngineerCommented:
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
 
valkyrie_ncCommented:
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
 
valkyrie_ncCommented:
Although on review, I like PriyaPadman's solution. :)
0
 
jmauelAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.