Still trying to understand an old post from page47

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 2000 Forums

SQL Server Development (2000)

Still trying to understand an old post from page47

Author

Topic

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 12:25:24

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=17184

theres the reference...

select
Resource_entity_id,
convert(int,'') as Resource_data_seq,
Resource_data_type,
New_Interpretation_Date_Time,
Applicable_Strat_Unit,
New_Revised_Date_Time
into
#resource_admin_temp
from resource_data_profile_txt
order by Resource_data_seq

declare @new int, @last varchar(5000)
select @new = 1, @last = 0

update #resource_admin_temp
set
@new = Resource_data_seq = case when @last = Resource_entity_id then @new + 1 else 1 end,
@last = Resource_entity_id

theres what I altered it to. It appears to work correctly however I'm getting about 8800 records (out of 800000) that have duplicates on Resource_data_seq and Resource_entity_id. I'm currently trouble shooting right now, but I'm not getting anywhere fast, have I modified page47's code there wrong ?

-----------------------
Take my advice, I dare ya

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-02 : 12:32:06

Yeah, bud, you've mucked it all up

M.E. post your DDL for resource_data_profile_txt real quick, and I'll straighten you out.

<O>

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 12:32:23

I'm thinking the error is in my order by statement. I'll play around with that for a bit I guess... Any other ideas would still be helpful

[edit] Just scripting right now.. thing takes a bit... I hate editting code I barely understand in the first place [/edit]
-----------------------
Take my advice, I dare ya

Edited by - M.e. on 07/02/2002 12:34:27

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 12:35:38

Heres what enterprise thinks it should look like. All columns populated by a DTS package

CREATE TABLE [dbo].[Resource_Data_Profile_txt] (
[Resource_Entity_Id] [varchar] (20) NOT NULL ,
[Resource_Data_Seq] [int] NULL ,
[Resource_Entity_Type] [varchar] (20) NOT NULL ,
[Resource_Data_Source] [varchar] (30) NOT NULL ,
[Resource_Data_Type] [varchar] (30) NOT NULL ,
[Interpretation_Method] [varchar] (20) NULL ,
[Applicable_Strat_Name_Set] [varchar] (30) NULL ,
[Applicable_Strat_Unit] [varchar] (40) NULL ,
[Interpretation_Remarks] [varchar] (2000) NULL ,
[Interpretation_Date_Time] [varchar] (50) NULL ,
[Interpretation_By] [varchar] (20) NULL ,
[Revised_Date_Time] [varchar] (50) NULL ,
[Revised_By] [varchar] (20) NULL ,
[Well_UWI] [varchar] (13) NULL ,
[Oil_Sands_Hole_Id] [varchar] (20) NULL ,
[Resource_Study_Id] [int] NULL ,
[Coal_Hole_Id] [varchar] (20) NULL ,
[Excavation_Approval_Number] [varchar] (20) NULL ,
[Field_Station_Id] [varchar] (20) NULL ,
[UWI] [varchar] (13) NULL ,
[Bwddrl_dbkey] [numeric](18, 0) NULL ,
[Bwdgfrm_DBkey] [numeric](18, 0) NULL ,
[BWdgtmk_DBkey] [numeric](18, 0) NULL ,
[new_revised_date_time] [datetime] NULL ,
[new_interpretation_date_time] [datetime] NULL
)

-----------------------
Take my advice, I dare ya

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-02 : 12:36:28

what's your pk?

in other words, I take it resource_entity_id is duplicated and you are trying to fill in Resource_data_seq with a numbering. but how will you know what order to put the _data_seq in? or does it matter?

<O>

Edited by - Page47 on 07/02/2002 12:39:09

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 12:38:40

hmm, let me reword... it really doesn't matter what order the data_seq is in just as long as it can be combined with entity_ID to be unique.

The top 5 columns on the _txt table is defined as it's primary key
-----------------------
Take my advice, I dare ya

Edited by - M.e. on 07/02/2002 12:42:35

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-02 : 12:44:58

M.E. you can't define a composite PK when a member column is nullable....(resource_data_seq)...however, if the order doesn't matter, its real simple....


select 
    Resource_entity_id,
    convert(int,'') as Resource_data_seq, 
    Resource_Entity_type,
    Resource_Data_Source,
    Resource_Data_Type
into 
    #resource_admin_temp 
from
    resource_data_profile_txt 
order by
    Resource_entity_id,
    Resource_Entity_type,
    Resource_Data_Source,
    Resource_Data_Type

declare @new int, @last varchar(5000) 
select @new = 1, @last = 0 

update #resource_admin_temp 
set 
    @new = Resource_data_seq = case when @last = Resource_entity_id then @new + 1 else 1 end, 
    @last = Resource_entity_id

update
    resource_data_profile_text
set 
    resource_data_seq = t.resource_data_seq
from
    resource_data_profile_text r
    inner join #resource_admin_temp t
        on (r.Resource_entity_id = t.Resource_entity_id and
            r.Resource_Entity_type = t.Resource_Entity_type and
            r.Resource_Data_Source = t.Resource_Data_Source and
            r.Resource_Data_Type = t.Resource_Data_Type)

<O>

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 12:54:59

quote:
M.E. you can't define a composite PK when a member column is nullable....(resource_data_seq)...however, if the order doesn't matter, its real simple....

actually, a second step in this is

Insert into resource_data_seq_administration
select Resource_entity_id,
Resource_data_seq,
'idms',
Resource_data_type,
convert(smalldatetime,New_Interpretation_Date_Time),
Applicable_Strat_Unit,
convert(smalldatetime,New_Revised_Date_Time),null,'well class a'
from #resource_admin_temp

Which pretty much takes all the data in the temp table (including the newly assigned resource_data_seq) and puts it into a new permanent table. The only reason the temp table is there is to populate the data_seq

the _txt table is used to populate 3 other tables as well.

-----------------------
Take my advice, I dare ya

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-02 : 12:57:45

ic ic . . . well either way, Resource_data_seq is going to be null when you start, so you don't need it in the order by of the select into ....

so r u squared away?

<O>

Edited by - Page47 on 07/02/2002 12:58:07

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 13:02:14

Looks great now... Just running it now, it takes about 15 mins for all 870k records to be moved from the txt file and for a few data formatting queries to run against it.. So I'll have to wait a bit before I can definately say yes or no.

-----------------------
Take my advice, I dare ya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources