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
 Import/Export (DTS) and Replication (2000)
 SQL - Unique Key !!!! I NEED HELP !!!!!

Author  Topic 

Indyyan
Starting Member

31 Posts
Posted - 2004-07-08 : 05:44:39
Hello!!

I have to import Textfiles from 94-2004. The table name is Orderposreg and the name of the textfile is also Orderposreg.txt.
The name of the Primary Key is Order_o_Pos
The row from 1994 looks like this:
12345 | 12 | 13 | 14 | 15 | ... (first field is the Primary Key)
and the row from 95 looks like this:
12345 | 11 | 12 | 13 | 15 |

I want to overwrite the old row with the new one and import new rows which don´t exist!!

Can someone help me??

Thanks a lot

Jan

Wanderer
Master Smack Fu Yak Hacker

1168 Posts
Posted - 2004-07-08 : 05:52:36
Hmm - looks like you need to do a bit of logic here:

Perhaps dump your textfile into a temporary (or staging) table.

Then update columns where temp_table PK id = target table PK id. Unfortunately (since I hate all column updates), I suspect you may have to update pretty much very column EXCEPT the PK, of course.

Then, insert into you Target table where temp table PKid not in target table.

Then either cleanup (truncate) or drop you staging table.

That should work, methinks

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

Indyyan
Starting Member

31 Posts
Posted - 2004-07-08 : 06:32:49
Thanks!!

I have just started my SQL career and im pretty bad in programming SQL

What would I have to do if my Temp_table is Orderposreg1 and my target_table is Orderposreg
both located in the "tempdb".

Is it necessary to use a If...Else statement to clarify if SQL should use the Update or Insert command.

still looking for help

Jan
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts
Posted - 2004-07-08 : 07:21:18
Hi Jan,

We all start somewhere - and there is ALWAYS much more to learn!

Your target table should NOT be in tempdb. Tempdb gets dropped and recreated every time SQL starts, and is for temporary data only.

You should have some data on the server where you want to inport this data to - that is where the ORDERPOSREG table should exist.

You could create the ORDERPOSREG1 table as a temporary table.

How are you importing the data? DTS , BCP, OPENDATASOURCE ? ANyway, that besides the point.

So, you have imported you data into a table called #ORDERREGPOS1 (this is your temporary table, which SQL will automaGically create in the tempdb).

It sounds like to are talking about using a cursor, then looping through the #ORDERREGPOS1 table, and then checking the PK, and then choosing to insert or update. That would work, but would not be the best way from a performance point of view.

Rather, consider something like:

declare @error,@rowcount int

begin transaction
update ORDERREGPOS
set ORDERREGPOS.col2 = #orderregpos1.col2,
ORDERREGPOS.col3 = #orderregpos1.col3,
...,
ORDERREGPOS.lastcol = #orderregpos1.lastcol
where orderregpos.PK_ID = #orderregpos1.PK_ID
select @error = @@error,@rowcount=@@rowcount
if @error <> 0
  begin
  print ' Error updating - Error number '+@error+'. Rolling back'
  --this reverses your updates
  rollback
  --this jumps to the end
  goto exitpoint
end

print @rowcount+' rows updated'
insert into ORDERREGPOS
(pk_id,col2,col3,....,lastcol)
select 
(pk_id,col2,col3,....,lastcol)
from #ORDERREGPOS1 O1
where O1.pk_id not in (select pk_id from ORDERREGPOS)
select @error = @@error,@rowcount=@@rowcount
if @error <> 0
  begin
  print ' Error inserting - Error number '+@error
  print ' Rolling back updates and inserts'
  --this reverses your updates and inserts
  rollback
  --this jumps to the end
  goto exitpoint
end

print @rowcount ' rows were inserted'
--this saves your data
commit

exitpoint:



I've just typed this, it could do with soem testing, and might have somesyntax error (obviously, you'll need to change the column names to the proper values).

you could also consider using:
where not exists (select pk_id from ORDERREGPOS)
instead of
where O1.pk_id not in (select pk_id from ORDERREGPOS)

I'm sure one of the other forum regulars will post a better solution, but there it is, for what it is worth

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

Indyyan
Starting Member

31 Posts
Posted - 2004-07-08 : 09:22:48
Thanks a million!!!

Unbelievable you saved me ;)

There is just a little problem:

Orderposreg.Ordernr = #Temp.Ordernr,
Orderposreg.Pos = #Temp.Pos,
Orderposreg.Produktnr = #Temp.Produktnr,
Orderposreg.Artbenämn = #Temp.Artbenämn,
Orderposreg.Artikelgrupp = #Temp.Artikelgrupp,
Orderposreg.Förs_pris = #Temp.Förs_pris,
Orderposreg.Kvant = #Temp.Kvant,
Orderposreg.Total_Intäkt = #Temp.Total_Intäkt,
Orderposreg.SSort = #Temp.Sort,
Orderposreg.Datum = #Temp.Datum,
Orderposreg.Utskriven = #Temp.Utskriven,
Orderposreg.Levtid_Ö = #Temp.Levtid_Ö,
Orderposreg.Levtid_L = #Temp.Levtid_L,
.....
Orderposreg.lastcol = #Temp.lastcol

For every row i have this failure message

Msg 107, Level 16, State 2, Line 1
The column prefix '#Temp' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix '#Temp' does not match with a table name or alias name used in the query

This is the text from Microsoft Help

Use the column prefix that corresponds to the exposed name of the table.
Rewrite any queries where column names are qualified with the table name. Use the table alias instead.

But it don´t help me??
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts
Posted - 2004-07-08 : 10:18:41
Hmm - Are you inserting into a table called #temp, or a table called #orderposreg1?

Are you doing all this work in the same process? A #temp table will be dropped as soon as the process ends, unless explicitly dropped before then. Either way, it *sounds* like that your temporary table that you imported the data into, is either gone, or has a different name.

Do a quick test, just before the transaction:

select count(*) from #temp
if (@@error <> 0)
begin
Print 'An error occurred reading the temporary import table'
goto exitscript
end

Let me know what that tells you

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

Indyyan
Starting Member

31 Posts
Posted - 2004-07-08 : 10:33:55
First i tried with #Orderposreg1 but i thought maybe those Tables should not have a name which is almost equal. And therefore i renamed the table.

Server: Msg 133, Level 15, State 1, Line 103
A GOTO statement references the label 'exitscript' but the label has not been declared.

This is the message i get if i copy your "quick test" into the transaction, after declaring the variables and befort the transaction starts
Go to Top of Page

Indyyan
Starting Member

31 Posts
Posted - 2004-07-08 : 11:03:37
I don´t understand the failure message. It seems that the Query doesn´t find the Table #temp i also tried [tempdb].dbo.temp its the same...

Then I tried to change the name of the temporary table in the Query. I doesn´t make a difference it´s the same failure message...

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'Tempdb.dbo.Whatthef' does not match with a table name or alias name used in the query.

...just with another table name. But the table #Temp does exist exactly with the same Field names as Orderposreg!!
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts
Posted - 2004-07-08 : 11:06:59
Doh - sorry - my bad. Should have been exitpoint

How are you doing the import into that table, btw? Care to post the code so I can see if that is addign to your complications...

c4n

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

Indyyan
Starting Member

31 Posts
Posted - 2004-07-08 : 11:20:56
I use the DTS Import/Export Wizard to import the texfiles!!
I renamed the table again to Temp1:
The source table Orderposreg is here: [DWH Långshyttan].dbo.Orderposreg
But here the code:

declare @error int, @rowcount int

begin transaction
update Orderposreg
set Orderposreg.Ordernr = [Tempdb].dbo.Whatthef.Ordernr,
Orderposreg.Pos = [Tempdb].dbo.Temp1.Pos,
Orderposreg.Produktnr = [Tempdb].dbo.Temp1.Produktnr,
Orderposreg.Artbenämn = [Tempdb].dbo.Temp1.Artbenämn,
Orderposreg.Artikelgrupp = [Tempdb].dbo.Temp1.Artikelgrupp,
Orderposreg.Förs_pris = [Tempdb].dbo.Temp1.Förs_pris,
Orderposreg.Kvant = [Tempdb].dbo.Temp1.Kvant,
Orderposreg.Total_Intäkt = [Tempdb].dbo.Temp1.Total_Intäkt,
Orderposreg.SSort = [Tempdb].dbo.Temp1.Sort,
Orderposreg.Datum = [Tempdb].dbo.Temp1.Datum,
Orderposreg.Utskriven = [Tempdb].dbo.Temp1.Utskriven,
Orderposreg.Levtid_Ö = [Tempdb].dbo.Temp1.Levtid_Ö,
Orderposreg.Levtid_L = [Tempdb].dbo.Temp1.Lev_kvant,
Orderposreg.Inköpskostnad = [Tempdb].dbo.Temp1.Inköpskostnad,
Orderposreg.Vikt_tol = [Tempdb].dbo.Temp1.Vikt_tol,
Orderposreg.AntalSt = [Tempdb].dbo.Temp1.AntalSt,
Orderposreg.Spec_nr = [Tempdb].dbo.Temp1.Spec_nr,
Orderposreg.Spec_utgåva = [Tempdb].dbo.Temp1.Spec_utgåva,
Orderposreg.ID_Beräknad = [Tempdb].dbo.Temp1.ID_Beräknad,
Orderposreg.Bredd = [Tempdb].dbo.Temp1.Bredd,
Orderposreg.Tjocklek = [Temp].dbo.Temp1.Längd,
Orderposreg.ValsnGrad_kod = [Tempdb].dbo.Temp1.ValsnGrad_kod,
Orderposreg.Kant_Kod = [Tempdb].dbo.Temp1.Kant_Kod,
Orderposreg.Planhets_kod = [Tempdb].Temp1.Yt_kod,
Orderposreg.LevForm_kod = [Tempdb].dbo.Temp1.LevForm_kod,
Orderposreg.Rakhets_kod = [Tempdb].dbo.Temp1.Rakhets_kod,
Orderposreg.BreddTol_kod = [Tempdb].dbo.Temp1.Breddtol_kod,
Orderposreg.TjocklTol_kod = [Tempdb].dbo.Temp1.TjockTol_kod,
Orderposreg.LängdTol_kod = [Tempdb].dbo.Temp1.LängdTol_kod,
Orderposreg.Stål_kod = [Tempdb].dbo.Temp1.Stäl_kod,
Orderposreg.TotaltSek = [Tempdb].dbo.Temp1.TotaltSek,
Orderposreg.Tullstatnr = [Tempdb].dbo.Temp1.Tullstatnr,
Orderposreg.Fakturera = [Tempdb].dbo.Temp1.Fakturera,
Orderposreg.Leveransstatus = [Tempdb].dbo.Temp1.Leveransstatus,
Orderposreg.Momsbelopp = [Tempdb].dbo.Temp1.Momsbelopp,
Orderposreg.Total_inkl_moms = [Tempdb].dbo.Temp1.Total_inkl_moms,
Orderposreg.KloMPS = [Tempdb].dbo.Temp1.KloMPS,
Orderposreg.ShopFloor = [Tempdb].dbo.Temp1.ShopFloor,
Orderposreg.Status = [Tempdb].dbo.Temp1.Status,
Orderposreg.Stålkod = [Tempdb].dbo.Temp1.Stålkod,
Orderposreg.Kontonyckel = [Tempdb].dbo.Temp1.Kontonyckel,
Orderposreg.PlanLevDat = [Tempdb].dbo.Temp1.PlanLevDat,
Orderposreg.Legtillägg = [Tempdb].dbo.Temp1.Legtillägg,
Orderposreg.StålGrp = [Tempdb].dbo.Temp1.StålGrp,
Orderposreg.KundNr = [Tempdb].dbo.Temp1.KundNr,
Orderposreg.FKundNr = [Tempdb].dbo.Temp1.FKundNr,
Orderposreg.Godsmärke = [Tempdb].dbo.Temp1.Godsmärke,
Orderposreg.LagerMtr = [Tempdb].dbo.Temp1.LagerMtr,
Orderposreg.Sortkvant = [Tempdb].dbo.Temp1.Sortkvant,
Orderposreg.Sortpris = [Tempdb].dbo.Temp1.Sortpris,
Orderposreg.KundsProdNr = [Tempdb].dbo.Temp1.KundsProdNr,
Orderposreg.Godsmärke2 = [Tempdb].dbo.Temp1.Godsmärke2,
Orderposreg.RegDatum = [Tempdb].dbo.Temp1.RegDatum,
Orderposreg.PlanBetDag = [Tempdb].dbo.Temp1.PlanBetDag,
Orderposreg.Säkring = [Tempdb].dbo.Temp1.Säkring
where Orderposreg.Ordernr_o_pos = [TempDb].Temp1.ordernr_o_pos
select @error = @@error,@rowcount=@@rowcount
if @error <> 0
begin
print ' Error updating - Error number '+@error+'. Rolling back'
--this reverses your updates
rollback
--this jumps to the end
goto exitpoint
end

print @rowcount+' rows updated'
insert into Orderposreg
(Ordernr, Pos, Ordernr_o_pos, Produktnr, Artbenämn, Artikelgrupp, Förs_pris, Kvant, Total_Intäkt, Sort, Datum, Utskriven, Levtid_Ö, Levtid_L, Lev_kvant, Inköpskostnad, Vikt_tol, AntalSt, Spec_nr, Spec_utgåva, ID_Beräknad, Bredd, Tjocklek, Längd, ValsnGrad_kod, Kant_kod, Planhets_kod, Yt_kod, LevForm_kod, Rakhets_kod, BreddTol_kod, TjocklTol_kod, LängdTol_kod, Stål_kod, TotaltSek, Tullstatnr, Fakturera_kvant, Leveransstatus, Fri_text, Momsbelopp, Total_inkl_moms, KloMPS, ShopFloor, Status, Stålkod_text, Kontonyckel, PlanLevDat, Legtillägg, StålGrp, KundNr, FKundNr, Godsmärke1, LagerMtr, Sortkvant, Sortpris, KundsProdNr, Godsmärke2, RegDatum, PlanBetDag, Säkring)
select
Ordernr, Pos, Ordernr_o_pos, Produktnr, Artbenämn, Artikelgrupp, Förs_pris, Kvant, Total_Intäkt, Sort, Datum, Utskriven, Levtid_Ö, Levtid_L, Lev_kvant, Inköpskostnad, Vikt_tol, AntalSt, Spec_nr, Spec_utgåva, ID_Beräknad, Bredd, Tjocklek, Längd, ValsnGrad_kod, Kant_kod, Planhets_kod, Yt_kod, LevForm_kod, Rakhets_kod, BreddTol_kod, TjocklTol_kod, LängdTol_kod, Stål_kod, TotaltSek, Tullstatnr, Fakturera_kvant, Leveransstatus, Fri_text, Momsbelopp, Total_inkl_moms, KloMPS, ShopFloor, Status, Stålkod_text, Kontonyckel, PlanLevDat, Legtillägg, StålGrp, KundNr, FKundNr, Godsmärke1, LagerMtr, Sortkvant, Sortpris, KundsProdNr, Godsmärke2, RegDatum, PlanBetDag, Säkring
from [Tempdb].dbo.Temp1
where [Tempdb].dbo.Temp1.Ordernr_o_pos not in (select Ordernr_o_pos from Orderposreg)
select @error = @@error,@rowcount=@@rowcount
if @error <> 0
begin
print ' Error inserting - Error number '+@error
print ' Rolling back updates and inserts'
--this reverses your updates and inserts
rollback
--this jumps to the end
goto exitpoint
end

print @rowcount+' rows were inserted'
--this saves your data
commit

exitpoint:
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts
Posted - 2004-07-08 : 11:23:27
so, let me try and get this straight:

your process is:

1> Import data from text file into a table named #temp

2> Do my test select from #temp - you get rows (yes?)

3> start transaction, get error "Server: Msg 107"

Correct?

Plese confirm the process you are using to import, and that step 2 is sucessful


*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts
Posted - 2004-07-08 : 11:33:53
Sorry - missed you post while typing.

Ok - you DTS into a table called temp1. That is a "proper" table, not a temporary table. Can you confirm what database you are creating that table in? It should not be the tempdb - see my previous comments re: that. Imho it should be in [DWH Långshyttan].

Given that, you should be referring to [dbname].dbo.temp1.columnname, but I will refer to tempdb as you do, for now.

Ordernr_o_pos is your PK, right?

See bold - you should remove the whatthef (even if it does feel appropriate )


declare @error int, @rowcount int

select count(*) from [Tempdb].dbo.temp1
if (@@error <> 0)
  begin
  Print 'An error occurred reading the temporary import table'
  goto exitscript
end

begin transaction

update Orderposreg
--remove the whatthef reference
set Orderposreg.Ordernr = [Tempdb].dbo.temp1.Ordernr,
Orderposreg.Pos = [Tempdb].dbo.Temp1.Pos,
Orderposreg.Produktnr = [Tempdb].dbo.Temp1.Produktnr,
Orderposreg.Artbenämn = [Tempdb].dbo.Temp1.Artbenämn,
Orderposreg.Artikelgrupp = [Tempdb].dbo.Temp1.Artikelgrupp,
Orderposreg.Förs_pris = [Tempdb].dbo.Temp1.Förs_pris,
Orderposreg.Kvant = [Tempdb].dbo.Temp1.Kvant,
Orderposreg.Total_Intäkt = [Tempdb].dbo.Temp1.Total_Intäkt,
Orderposreg.SSort = [Tempdb].dbo.Temp1.Sort,
Orderposreg.Datum = [Tempdb].dbo.Temp1.Datum,
Orderposreg.Utskriven = [Tempdb].dbo.Temp1.Utskriven,
Orderposreg.Levtid_Ö = [Tempdb].dbo.Temp1.Levtid_Ö,
Orderposreg.Levtid_L = [Tempdb].dbo.Temp1.Lev_kvant,
Orderposreg.Inköpskostnad = [Tempdb].dbo.Temp1.Inköpskostnad,
Orderposreg.Vikt_tol = [Tempdb].dbo.Temp1.Vikt_tol,
Orderposreg.AntalSt = [Tempdb].dbo.Temp1.AntalSt,
Orderposreg.Spec_nr = [Tempdb].dbo.Temp1.Spec_nr,
Orderposreg.Spec_utgåva = [Tempdb].dbo.Temp1.Spec_utgåva,
Orderposreg.ID_Beräknad = [Tempdb].dbo.Temp1.ID_Beräknad,
Orderposreg.Bredd = [Tempdb].dbo.Temp1.Bredd,
Orderposreg.Tjocklek = [Temp].dbo.Temp1.Längd,
Orderposreg.ValsnGrad_kod = [Tempdb].dbo.Temp1.ValsnGrad_kod,
Orderposreg.Kant_Kod = [Tempdb].dbo.Temp1.Kant_Kod,
Orderposreg.Planhets_kod = [Tempdb].Temp1.Yt_kod,
Orderposreg.LevForm_kod = [Tempdb].dbo.Temp1.LevForm_kod,
Orderposreg.Rakhets_kod = [Tempdb].dbo.Temp1.Rakhets_kod,
Orderposreg.BreddTol_kod = [Tempdb].dbo.Temp1.Breddtol_kod,
Orderposreg.TjocklTol_kod = [Tempdb].dbo.Temp1.TjockTol_kod,
Orderposreg.LängdTol_kod = [Tempdb].dbo.Temp1.LängdTol_kod,
Orderposreg.Stål_kod = [Tempdb].dbo.Temp1.Stäl_kod,
Orderposreg.TotaltSek = [Tempdb].dbo.Temp1.TotaltSek,
Orderposreg.Tullstatnr = [Tempdb].dbo.Temp1.Tullstatnr,
Orderposreg.Fakturera = [Tempdb].dbo.Temp1.Fakturera,
Orderposreg.Leveransstatus = [Tempdb].dbo.Temp1.Leveransstatus,
Orderposreg.Momsbelopp = [Tempdb].dbo.Temp1.Momsbelopp,
Orderposreg.Total_inkl_moms = [Tempdb].dbo.Temp1.Total_inkl_moms,
Orderposreg.KloMPS = [Tempdb].dbo.Temp1.KloMPS,
Orderposreg.ShopFloor = [Tempdb].dbo.Temp1.ShopFloor,
Orderposreg.Status = [Tempdb].dbo.Temp1.Status,
Orderposreg.Stålkod = [Tempdb].dbo.Temp1.Stålkod,
Orderposreg.Kontonyckel = [Tempdb].dbo.Temp1.Kontonyckel,
Orderposreg.PlanLevDat = [Tempdb].dbo.Temp1.PlanLevDat,
Orderposreg.Legtillägg = [Tempdb].dbo.Temp1.Legtillägg,
Orderposreg.StålGrp = [Tempdb].dbo.Temp1.StålGrp,
Orderposreg.KundNr = [Tempdb].dbo.Temp1.KundNr,
Orderposreg.FKundNr = [Tempdb].dbo.Temp1.FKundNr,
Orderposreg.Godsmärke = [Tempdb].dbo.Temp1.Godsmärke,
Orderposreg.LagerMtr = [Tempdb].dbo.Temp1.LagerMtr,
Orderposreg.Sortkvant = [Tempdb].dbo.Temp1.Sortkvant,
Orderposreg.Sortpris = [Tempdb].dbo.Temp1.Sortpris,
Orderposreg.KundsProdNr = [Tempdb].dbo.Temp1.KundsProdNr,
Orderposreg.Godsmärke2 = [Tempdb].dbo.Temp1.Godsmärke2,
Orderposreg.RegDatum = [Tempdb].dbo.Temp1.RegDatum,
Orderposreg.PlanBetDag = [Tempdb].dbo.Temp1.PlanBetDag,
Orderposreg.Säkring = [Tempdb].dbo.Temp1.Säkring
where Orderposreg.Ordernr_o_pos = [TempDb].Temp1.ordernr_o_pos

select @error = @@error,@rowcount=@@rowcount
if @error <> 0
begin
  print ' Error updating - Error number '+@error+'. Rolling back'
  --this reverses your updates
  rollback
  --this jumps to the end
  goto exitpoint
end

print @rowcount+' rows updated'

insert into Orderposreg
(Ordernr, Pos, Ordernr_o_pos, Produktnr, Artbenämn, Artikelgrupp, Förs_pris, Kvant, Total_Intäkt, Sort, Datum, Utskriven, Levtid_Ö, Levtid_L, Lev_kvant, Inköpskostnad, Vikt_tol, AntalSt, Spec_nr, Spec_utgåva, ID_Beräknad, Bredd, Tjocklek, Längd, ValsnGrad_kod, Kant_kod, Planhets_kod, Yt_kod, LevForm_kod, Rakhets_kod, BreddTol_kod, TjocklTol_kod, LängdTol_kod, Stål_kod, TotaltSek, Tullstatnr, Fakturera_kvant, Leveransstatus, Fri_text, Momsbelopp, Total_inkl_moms, KloMPS, ShopFloor, Status, Stålkod_text, Kontonyckel, PlanLevDat, Legtillägg, StålGrp, KundNr, FKundNr, Godsmärke1, LagerMtr, Sortkvant, Sortpris, KundsProdNr, Godsmärke2, RegDatum, PlanBetDag, Säkring)
select 
Ordernr, Pos, Ordernr_o_pos, Produktnr, Artbenämn, Artikelgrupp, Förs_pris, Kvant, Total_Intäkt, Sort, Datum, Utskriven, Levtid_Ö, Levtid_L, Lev_kvant, Inköpskostnad, Vikt_tol, AntalSt, Spec_nr, Spec_utgåva, ID_Beräknad, Bredd, Tjocklek, Längd, ValsnGrad_kod, Kant_kod, Planhets_kod, Yt_kod, LevForm_kod, Rakhets_kod, BreddTol_kod, TjocklTol_kod, LängdTol_kod, Stål_kod, TotaltSek, Tullstatnr, Fakturera_kvant, Leveransstatus, Fri_text, Momsbelopp, Total_inkl_moms, KloMPS, ShopFloor, Status, Stålkod_text, Kontonyckel, PlanLevDat, Legtillägg, StålGrp, KundNr, FKundNr, Godsmärke1, LagerMtr, Sortkvant, Sortpris, KundsProdNr, Godsmärke2, RegDatum, PlanBetDag, Säkring
from [Tempdb].dbo.Temp1
where [Tempdb].dbo.Temp1.Ordernr_o_pos not in (select Ordernr_o_pos from Orderposreg)
select @error = @@error,@rowcount=@@rowcount
if @error <> 0
  begin
  print ' Error inserting - Error number '+@error
  print ' Rolling back updates and inserts'
  --this reverses your updates and inserts
  rollback
  --this jumps to the end
  goto exitpoint
end

print @rowcount+' rows were inserted'
--this saves your data
commit

exitpoint:



What does that do?

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

Indyyan
Starting Member

31 Posts
Posted - 2004-07-08 : 11:36:47
Yes, thats my process!!!

OK, if I use your quick test i got the same failure message as before
But if I use only the first row of your test: "select count(*) from [master].dbo.Temp1"
I get this result:
(No column name)
1 20949
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2004-07-08 : 11:41:27
Looks like your trying to do a deltas process...

Update records where they exists...insert new records...

What about deletes?

Check this out:

http://weblogs.sqlteam.com/brettk/archive/2004/04/23/1281.aspx



Brett

8-)
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts
Posted - 2004-07-08 : 11:46:59
And there's your problem

quote:
select count(*) from [master].dbo.Temp1

gives you are response, whereas
quote:
select count(*) from [tempdb].dbo.Temp1

complains that the table doesn't exist.

1st things first - creating the table in master is almost as bad as using tempdb - go back to your DTS and change it to create a table in your database [DWH Långshyttan] - I think it was.

change your tempdb references to master, and test again. I tihnk that will solve it.


*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

Indyyan
Starting Member

31 Posts
Posted - 2004-07-08 : 11:54:00
OK i have now imported the Temp1 into the [master].dbo.temp1 and changed all [tempdb] values from the script to [master]

But in both cases i had the same failure message as before!
I will restart the computer, perhaps it´s the sollution ;)
Go to Top of Page

Indyyan
Starting Member

31 Posts
Posted - 2004-07-08 : 12:13:10
I also got a result with [tempdb] if i used count, it was my fault that i copied the row with [master]

Once again, I have droped Temp1 from [master] and imported Temp1 into [DWH Långshyttan] is that right??
And afterward changed the [master] values to [DWH Långshyttan]?
I tried again, but unfortunately it´s the same failure again!!
Go to Top of Page

Indyyan
Starting Member

31 Posts
Posted - 2004-07-08 : 13:01:52
Could this be the reason, that all fields in both tables has "varchar / 255 " as data type????
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts
Posted - 2004-07-09 : 05:36:07
no - that should not be a problem, unless your rows exceed 8060 bytes.

Let's try a different tack.

Let's explicitly create a proper table in the [DWH Långshyttan] database.
Create table Staging_Orderposreg - the structure must be exactly like Orderposreg from the column data types and collations etc.
Create an unique index on the Ordernr_o_pos column on Staging_Orderposreg .

Make sure that your DTS inserts into this table.

Then change the code to get the data from here.

NB - since we now have a permanent staging table, you will need to do cleanup. I propose that right at the end, after a successful run, you :

truncate table Staging_Orderposreg

Otherwise you will have issues the next time you run.

See if that helps...



*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

Indyyan
Starting Member

31 Posts
Posted - 2004-07-12 : 08:16:15
I just pointed out the problem, you forgot to define a Inner Join between Temp1 and Orderposreg

[Code]
dbo.Orderposreg.Säkring = dbo.Temp1.Säkring
from Orderposreg
join dbo.temp1 on Orderposreg.Ordernr_o_pos = temp1.Ordernr_o_pos
where dbo.Orderposreg.Ordernr_o_pos = dbo.Temp1.ordernr_o_pos
[/Code]

Next time when you visit Austria or Sweden I will pay for some rounds of beer!!!

Thanks

Jan
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts
Posted - 2004-07-12 : 11:43:39
Aha - good catch - that must have been frustrating finding it.

Jan - I'll be sure to take you up on that! Would love to get back to Austria - had some amazing Skiing in Saalbach in Jan 2002.

Keep on S-Q-L-ing...

*grin*

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site