Udating existing table - 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 2005 Forums

Transact-SQL (2005)

Udating existing table

Author

Topic

tefu
Starting Member

6 Posts

Posted - 2008-10-29 : 20:22:21

Hi all,

I created a temp table and uploaded the data from csv or txt or xls. that works fine, now from the temp table, I need to append the record into 'REFERENCE' table, in this table, it requires to have refseq (the sequence number). The sequence is not in the temp table.

here is the code that I am using

--creating temp table
use mydatabase
go
create table #tempreference
(
trefcode varchar(30),
treftype varchar(30),
trefdate nvarchar(20),
trefdescription varchar(30),
trefmemo varchar(200),
trefsourceid varchar (30))
go

--upload the csv to the temp table
bulk insert #tempreference
from 'C:\testdata.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

now i am stuck

there are 5 fields in the tempreference table, need to update those to reference table.

I tried to use this to get the next seq number
Select max(refseq)+1 as maxseq from reference

but not sure how to get that in a loop for each record in the temp table.

Also in the reference table, I only want to update 5 fields out of 30 fields available, so I need to specify them, this is what I used

insert into reference (refcode,reftype,refdate,refdescription,refmemo,refsourceid)
select trefcode,treftype,trefdate,trefdescription,trefmemo,trefsourceid
from #tempreference

If the refseq not required, the above works ok but I need the refseq in the update.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-30 : 05:35:45

Hi Tefu,

This is the wrong forum for your question (this is for helpful scripts)

I'm a bit confused. I don't know if you want to INSERT new data into reference or update some data in reference.

Is there no 1 to 1 relationship you can make with #tempreference and reference?

I think you should post some sample data for both tables and explain exactly what you need the end product to look like.

-------------
Charlie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 06:09:14

cant you make use of row_number() to generate the sequence?

tefu
Starting Member

6 Posts

Posted - 2008-10-30 : 18:30:30

thanks guys, I worked it out just need to figure out to do a loop on temp table

Here what I did

use mydatabase
go
create table #tempreference
(
trefcode varchar(30),
treftype varchar(30),
trefdate nvarchar(23),
trefdescription varchar(30),
trefmemo varchar(max),
trefsourceid varchar (30))
go

--upload the csv to the temp table
bulk insert #tempreference
from 'C:\testdata.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

-- updates the reference table
declare @refseq INT
declare @refid varchar(30)
declare @trefcode varchar(30)
declare @treftype varchar(30)
declare @trefdate nvarchar(23)
declare @trefdescription varchar(30)
declare @trefmemo varchar(max)
declare @trefsourceid varchar (30)
declare @reffile varchar (30)

Select @refseq=max(refseq)+1 from reference
select @trefcode=trefcode,@treftype=treftype,@trefdate=CONVERT(VARCHAR(23), trefdate, 121),@trefdescription=trefdescription,@trefmemo=trefmemo,@trefsourceid=trefsourceid
from #tempreference where trefcode='POD'
select @refid='ref'+convert(varchar,@refseq)
select @reffile=@refid+'.gif'

insert into reference (refseq,refid,refstatus,refcode,reftype,refdate,refdescription,refmemo,refsourceid,reffile)
select @refseq,@refid,'L',@trefcode,@treftype,@trefdate,@trefdescription,@trefmemo,@trefsourceid,@reffile

This will update the reference table but only for one record. just need to do a loop on reading the temp table so I can generate the refseq (which is unique)

By the way, does anyone knows the script or DB provider to read straight from Visual Fox Pro database?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 00:25:11

cant you use something like below

use mydatabase
go
create table #tempreference
(
trefcode varchar(30),
treftype varchar(30),
trefdate nvarchar(23),
trefdescription varchar(30),
trefmemo varchar(max),
trefsourceid varchar (30))
go

--upload the csv to the temp table
bulk insert #tempreference
from 'C:\testdata.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

alter table #tempreference add rowseq int identity(1,1)



Select @refseq=max(refseq) from reference

insert into reference (refseq,refid,refstatus,refcode,reftype,refdate,refdescription,refmemo,refsourceid,reffile)
select @refseq+rowseq
,'ref'+convert(varchar(10),@refseq+rowseq)
,'L'
,trefcode
,treftype
,DATEADD(dd,DATEDIFF(dd,0,trefdate),0)
,trefdescription
,trefmemo
,trefsourceid 
, 'ref'+convert(varchar(10),@refseq+rowseq)
+'.gif'
from #tempreference where trefcode='POD'
drop table #tempreference

tefu
Starting Member

6 Posts

Posted - 2008-11-02 : 22:09:51

I'll give that a go, thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-02 : 23:36:19

cheers

let me know how you got on with it..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources