Create table and update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Create table and update

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2011-08-01 : 11:30:55

Hi,

Just wanted to get some help/advice.

I have create a table, similar to a dimension in a warehouse;

create table table1 (
id int identity(1,1),
dwtime datetime not null,
code char(10),
name varchar(100))

insert into table1
select * from example

if i wanted to keep updating this table with newest records, from the example table every now and then.

What could I use? i tried playing with not exists and not in query but doesnt seem to work.

thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-08-01 : 11:39:01

NOT EXISTS works. So does LEFT OUTER JOIN on unique key WHERE left table is null. Post what you tried and we'll fix correct it.

Be One with the Optimizer
TG

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2011-08-01 : 11:48:45

Heres cut down version of my code:

set @dwtime = GETDATE()

CREATE TABLE [dbo].[CallsMade](
[CallsMadeSk] [int] IDENTITY(1,1) NOT NULL,
[dwTime] [datetime] NOT NULL,
[dwIsCurrent] [bit] NOT NULL,
[dwIsDeleted] [bit] NOT NULL,
[CallStartTime] datetime,
[CallEndTime] datetime,
[CallReasonCode] char(10),
[CallReasonDescription] varchar(100),
[CallCampaignCode] char(10),
[CallCampaignName] char(100),
[CallOutcomeCode] char(10),
[CallOutcomeName] char(100),
[UserRef] varchar(100)
)

SELECT
dwTime = @dwtime,
dwIsCurrent = 1,
dwIsDeleted = 0,
cm.StartTime,
cm.EndTime,
cm.ReasonCode,
rc.Description [Reason],
cm.CampaignID,
sc.CampaignName,
oc.OutcomeID,
oc.OutcomeText,
cm.UserRef
into #temp1
FROM Current cm
JOIN CallsList scl ON cm.CallsListID = scl.ID
JOIN Campaigns sc ON cm.CampaignID = sc.CampaignID
join Outcomes oc on cm.OutcomeCode = oc.OutcomeID
join ReasonCodes rc on cm.ReasonCode = rc.ReasonID
WHERE DATENAME(YEAR, StartTime) = '2011'
AND LEN(cm.UserRef) > 9
GROUP BY cm.StartTime, cm.EndTime, cm.ReasonCode, cm.CampaignID, cm.UserRef, sc.CampaignName,
oc.OutcomeID, sc.CampaignStatus, sc.CampaignType, oc.OutcomeText, rc.Description

insert into [dbo].[CallsMade]

select *
from #temp1 s
left join [dbo].[CallsMade] a
??

Thanks

quote:
Originally posted by TG

NOT EXISTS works. So does LEFT OUTER JOIN on unique key WHERE left table is null. Post what you tried and we'll fix correct it.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-08-01 : 11:58:14

You will need to identify what constitutes a unique "key" in you table. For this example let's assume it is CallStartTime and UserRef:


insert CallsMade
       ([dwTime]
       ,[dwIsCurrent]
       ,[dwIsDeleted]
       ,[CallStartTime]
       ,[CallEndTime]
       ,[CallReasonCode]
       ,[CallReasonDescription]
       ,[CallCampaignCode]
       ,[CallCampaignName]
       ,[CallOutcomeCode]
       ,[CallOutcomeName]
       ,[UserRef])
select [dwTime]
       ,[dwIsCurrent]
       ,[dwIsDeleted]
       ,[CallStartTime]
       ,[CallEndTime]
       ,[CallReasonCode]
       ,[CallReasonDescription]
       ,[CallCampaignCode]
       ,[CallCampaignName]
       ,[CallOutcomeCode]
       ,[CallOutcomeName]
       ,[UserRef]
from   #temp s
left outer join dbo.CallsMade a
       on a.CallStartTime = s.CallStartTime
       and a.UserRef = s.UserRef
where  a.callStartTime is null

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources