Inserting from text files to existing table

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)

Inserting from text files to existing table

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2002-09-17 : 04:08:39

Hi,

We have a web based application where the user can post his requirements, these requirements go sit in 2 different tables jobadvt and jobcat, where the jobadvt table holds the requirement details and the jobcat holds the details of the job category for that requirement.The jobadvt has a column jobadvtid and the jobcat has the jobadvtid related to the jobadvt table.So far the things are going good with the web based application as we can post only one requirement at a time. Now we want to insert datas straight into the tables from the text file, Since the datas has to go to 2 different tables and the tables are related to each other on a common column, I'am very confused how to go abt this.Any help will be greatfull.

Karunakaran

Don't wait for things to happen,Make them to happen...

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-17 : 08:29:45

First, rather than writing prose about your table structures, its much more efficient to provide the DDL of the tables including the definition of you fk constraints ...

Second, I usually think the best thing to do is use DTS/BCP/BULK INSERT to populate a "staging" table with the data in the file and then from there populate your production table ...

Jay White
{0}

karuna
Aged Yak Warrior

582 Posts

Posted - 2002-09-23 : 05:20:19

The Table has following columns is as follows:

Advt_id
Company
Email
Positions
Company_Address
Company_Phone
Company_profile
Ad_reference ----- This has to be unique
Apply_to ----- email
Job_location
Designation
Experience 3 to 5 --- It has to be in this format
AgeRange 30 to 32 --- It has to be in this format
Education --- can be text
AreaOfExpertise
Job_description
Date_stamp
Client_id
Company_Url
Enabled --- Default Column with value 1
mailstatus --- Default Column with value 1
IsHotJob --- Default Column with value 0
IsFree --- Default Column with value 0
Job_Type
hhhotjob --- Default Column with value 0

Job Category has the following 2 columns
advt_id
category_id

We get the details from the webpage by means of a form,for all these columns except advt_id,the following is code which used to create the advt_id and to insert these datas.

function jobadvtid
sql="select lastid FROM tablekey (HOLDLOCK TABLOCK) WHERE tablename='Job_Advertisement'" &_
"UPDATE tablekey set Lastid =Lastid plus 1 WHERE tablename='Job_Advertisement'"
set rsjob=cn.execute(sql)
jobadvtid=rsjob(0)
end function
-------
This is used to insert datas to job_advt table
sub savejobads
jobadid=clng(jobadvtid) plus 1 (--the plus symbol didnt came in preview so its in words)
sqljads="insert into Job_Advertisement(JobAdvt_Id,Company,Email,Positions,Company_Address,Company_Phone,Company_profile,"&_ "Ad_reference,Apply_to,Job_location,Designation,Experience,AgeRange,Education,"&_ "AreaOfExpertise,Job_description,Date_stamp,Client_id,Company_Url,"&_"Enabled,IsFree) values("&jobadid&",'"&checkchar(request.form("txtcompany"))&"',"&_"'"&checkchar(request.form("txtemail"))&"',"&request.form("txtposition")&","&_ "'"&checkchar(request.form("txtarcompanyaddr"))&"','"&request.form("Companyphone")&"',"&_"'"&checkchar(request.form("txtarcompanyprof"))&"','"&checkchar(request.form("txtaddref"))&"',"&_"'"&checkchar(request.form("txtapplyto"))&"','"&checkchar(request.form("txtjoblocation"))&"',"&_"'"&checkchar(request.form("txtdesignation"))"','"&experience&"',"&_"'"&age&"','"&checkchar(request.form("Education1"))&"',"&_ "'"&checkchar(request.form("txtarexpertise"))&"','"&checkchar(request.form"txtarjobdescription"))&"',"&_"getdate(),"&clientid&",'"&checkchar(request.form("txtCompany_URL"))&"',"& Enabled & ",1)"

cn.execute(sqljads)
----
To Insert datas to another table jobcategory.
----
myarray=split(request.form("selcategory"),",")
i=0
for each i in myarray
sqlcategory="insert into jobadvt_category values("&jobadid&","&i&")"
cn.execute(sqlcategory)
next

Now I want this operations to be done from the datas of text file.The problem is the datas has to go to two different tables.Whether I need 2 different text files or one file will do?...how to insert datas to jobcategory table, since i need to find out the advt_id from the jobadvt table....If some one tell me how to go abt this it would be very very helpfull.....Since I havent worked on writing DTS codes I dont have any ideas of this.....

Karunakaran

Don't wait for things to happen,Make them to happen...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources