CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user1345260
user1345260

Reputation: 2259

INSERT INTO View Dynamically

I've a view created by referring to two synonyms. The current query I've to harvest my View Table is as below

SELECT  dbo.synonym1.JobNo
      , dbo.synonym1.Customer
      , dbo.synonym2.PostalService
      , dbo.synonym2.FirstDate
      , dbo.synonym2.SecondDate
FROM dbo.synonym1 
  INNER JOIN dbo.synonym2 ON dbo.synonym1.JobNo = sbo.synonym2.JobNo
WHERE dbo.synonym2.PostalService = 'UPS'

Now I would like to find the difference between dbo.synonym2.FirstDate and dbo.synonym2.SecondDate in each row and INSERT the same row again into the View Table as many times as there is a difference.

For example if the FirstDate is 2012-06-03 00:00:00.000 and SecondDate is 2012-06-05 00:00:00.000 then it should insert the same row twice.

I'm using SQL Server 2008 R2 and I'm a newbie to SQL. Please help me out

Sample data that gets populated currently with the above query in the view is as below,

JobNo  Customer PostalService FirstDate                 SecondDate  
1      ABC      UPS           2012-06-03 00:00:00.000   2012-06-03 00:00:00.000
2      DEF      UPS           2012-06-03 00:00:00.000   2012-06-06 00:00:00.000
3      XYZ      UPS           2012-06-03 00:00:00.000   2012-06-05 00:00:00.000

I would need a result as below

JobNo  Customer PostalService FirstDate                 SecondDate  
1      ABC      UPS           2012-06-03 00:00:00.000   2012-06-03 00:00:00.000
2      DEF      UPS           2012-06-03 00:00:00.000   2012-06-05 00:00:00.000
2      DEF      UPS           2012-06-03 00:00:00.000   2012-06-05 00:00:00.000
2      XYZ      UPS           2012-06-03 00:00:00.000   2012-06-05 00:00:00.000
3      XYZ      UPS           2012-06-03 00:00:00.000   2012-06-06 00:00:00.000
3      XYZ      UPS           2012-06-03 00:00:00.000   2012-06-06 00:00:00.000

If you see the difference between the dates for JobNo 2 is 3 so data is repeated three times. and for the JobNo 3 is 2 so data is repeated twice.

Upvotes: 0

Views: 176

Answers (3)

user1425057
user1425057

Reputation: 

Create Table #temp
(
    StartDate DateTime,
    EndDate DateTime
)

Insert into #temp(StartDate, EndDate)
                    Values('2012-06-03 00:00:00.000', '2012-06-03 00:00:00.000');
Insert into #temp(StartDate, EndDate)
                    Values('2012-06-03 00:00:00.000', '2012-06-06 00:00:00.000');
Insert into #temp(StartDate, EndDate)
                    Values('2012-06-03 00:00:00.000', '2012-06-05 00:00:00.000');

With DateRange As
(
    select Convert(DateTime, '2012-06-02 00:00:00.000') as StartDate, 
          DATEADD(dd, 1, Convert(DateTime, '2012-06-02 00:00:00.000')) as EndDate
    Union All
    select EndDate, DATEADD(dd, 1, EndDate) From DateRange 
                    Where EndDate <= Convert(DateTime, '2012-06-10 00:00:00.000')
)

Select T.* from #temp T
Left Join DateRange R on ((T.StartDate < R.EndDate and T.EndDate > R.StartDate) 
          or (T.StartDate = R.EndDate and T.EndDate = R.StartDate))
order  by T.EndDate

Drop table #temp

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Your request is a little confusing, because one doesn't generally insert into views.

However, the following will insert rows into a table

insert into ViewTable(JobNo, Customer, PostalService, FirstDate, SecondDate)
    SELECT dbo.synonym1.JobNo, dbo.synonym1.Customer, dbo.synonym2.PostalService,
           dbo.synonym2.FirstDate, dbo.synonym2.SecondDate
     FROM dbo.synonym1 INNER JOIN
          dbo.synonym2
          ON dbo.synonym1.JobNo = sbo.synonym2.JobNo
     WHERE dbo.synonym2.PostalService = 'UPS' and
           datediff(d, FirstDate, SecondDate) <> 0

You need to define ViewTable as an actual table though.

Upvotes: 1

user1437825
user1437825

Reputation:

it sounds like what you want is the union statement below to populate this temp table. unless i am misunderstanding what you are asking for. 

DECLARE @MyTable as TABLE(JobNo VARHCAR(50), Customer VARCHAR(50), 
                       Postalservice VARCHAR(50), MyDate DateTime)

INSERT into @MyTable 
SELECT  dbo.synonym1.JobNo 
  , dbo.synonym1.Customer 
  , dbo.synonym2.PostalService 
  , dbo.synonym2.FirstDate 

FROM dbo.synonym1  
INNER JOIN dbo.synonym2 ON dbo.synonym1.JobNo = sbo.synonym2.JobNo 
WHERE dbo.synonym2.PostalService = 'UPS'  
UNION
SELECT  dbo.synonym1.JobNo 
  , dbo.synonym1.Customer 
  , dbo.synonym2.PostalService 

  , dbo.synonym2.SecondDate 
FROM dbo.synonym1  
INNER JOIN dbo.synonym2 ON dbo.synonym1.JobNo = sbo.synonym2.JobNo 
WHERE dbo.synonym2.PostalService = 'UPS'

INSERT into your view from @MyTable

Upvotes: 0

Related Questions