queries in Import Export Wizard??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

queries in Import Export Wizard???

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2002-09-30 : 07:46:40

Hi,

I need to import few datas from the text file to a table and for a particular column in the table i need to insert the data from the other table...Is it possible?....

Karunakaran

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

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-30 : 08:08:40

Try importing into a staging table and then executing an SP to join to the table for the extra da to insert into the final table.
In this way the import is not dependent on the databae structure nor on the method of import.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

karuna
Aged Yak Warrior

582 Posts

Posted - 2002-09-30 : 09:47:15

Hi,

The issue is the database is a live database.The tables structure involved in this are as follows:

Table Tablekey
Tablename - This contains the table name
Last id - This contains the last id no generated for that table

Table JobAdvt
Jobadvt_id -
Company
Address
Ref.No
....

When some one submits the posting in the web, the lastid from tablekey for jobadvt is retrieved and 1 is added to that and the value is inserted as Jobadvt_id, Now I have to create jobadvt_id for the datas inserted from the text file.And the no of records in the text file we recieve from the client may vary everyday.The only unique value in the text datas is the Ref No.Since the database is live on web, the jobadvt_id shouldnt be duplicated.

Karunakaran

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

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-30 : 10:06:09

create a staging table intbl with an identity + same structure as live table
Create a view on table which includes fields to insert from file
insert file into staging table view with - bcp, dts, whatever

then
declare @i int, @firstID int
select @i = (select count(*) from intbl

update LastID set id = id + @i, @firstID = id + 1

update intbl set Jobadvt_id = id - (select min(id) from intbl) + @firstID

then just insert from here into your production table knowing that the IDs have been reserved.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

karuna
Aged Yak Warrior

582 Posts

Posted - 2002-10-01 : 09:25:47

Hi,

When I Import the text file to the table the DTS code is something like this

'**********************************************************************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************************************
Function Main()
DTSDestination("Company_name") = DTSSource("Col001")
DTSDestination("Email") = DTSSource("Col002")
DTSDestination("Positions") = DTSSource("Col003")
DTSDestination("Company_Address") = DTSSource("Col004")
DTSDestination("Company_Phone") = DTSSource("Col005")
DTSDestination("Company_profile") = DTSSource("Col006")
DTSDestination("Ad_reference") = DTSSource("Col007")
DTSDestination("Apply_to") = DTSSource("Col008")
DTSDestination("Job_location") = DTSSource("Col009")
DTSDestination("Designation") = DTSSource("Col010")
DTSDestination("Experience") = DTSSource("Col011")
DTSDestination("AgeRange") = DTSSource("Col012")
DTSDestination("Education") = DTSSource("Col013")
DTSDestination("AreaOfExpertise") = DTSSource("Col014")
DTSDestination("Job_description") = DTSSource("Col015")
DTSDestination("Date_stamp") = DTSSource("Col016")
DTSDestination("Client_id") = DTSSource("Col017")
DTSDestination("Company_Url") = DTSSource("Col018")
DTSDestination("Enabled") = DTSSource("Col019")
DTSDestination("mailstatus") = DTSSource("Col020")
DTSDestination("IsHotJob") = DTSSource("Col021")
DTSDestination("IsFree") = DTSSource("Col022")
DTSDestination("Job_Type") = DTSSource("Col023")
DTSDestination("hhhotjob") = DTSSource("Col024")
Main = DTSTransformStat_OK
End Function

I want to execute the following query and insert add this line to the dts code

select lastid from tablekey where tablename='jobadvt'
update lastid=lastid+1 where tablename='jobadvt'

DTSDestination("Jobadvt_id") = DTSSource("lastid")

Is there any way to do this????

If I can do this, then it will solve all the issues...

Karunakaran

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

karuna
Aged Yak Warrior

582 Posts

Posted - 2002-10-08 : 06:53:39

I tried creating a stagging table with identity col, and when I tried
to import the text file to the table i got an error stating that the id column doesnt allow nulls and so table cannot be created. so what can be done now...

I found out this article but still I couldnt figure out how to go

http://www.ddart.net/mssql/sql2000/html/dtssql/dts_addf_ax_06er.htm

Karunakaran

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

karuna
Aged Yak Warrior

582 Posts

Posted - 2002-10-22 : 07:14:27

Hi All,

Finally I got the solution for my problem, I thought it would be fine to share with you all.One of my friend gave me this solution.The solution goes like this, a dts script in which u call a function which in turn calls a stored proc and value is returned by the function is inserted in the jobadvt_id column. The codes are :

'**********************************************************************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************************************

Function Main()
DTSDestination("Test_id") = GetNewVal
DTSDestination("Company_name") = DTSSource("Col001")
DTSDestination("Email") = DTSSource("Col002")
DTSDestination("Positions") = DTSSource("Col003")
DTSDestination("Company_Address") = DTSSource("Col004")
DTSDestination("Company_Phone") = DTSSource("Col005")
DTSDestination("Company_profile") = DTSSource("Col006")
DTSDestination("Ad_reference") = DTSSource("Col007")
DTSDestination("Apply_to") = DTSSource("Col008")
DTSDestination("Job_location") = DTSSource("Col009")
DTSDestination("Designation") = DTSSource("Col010")
DTSDestination("Experience") = DTSSource("Col011")
DTSDestination("AgeRange") = DTSSource("Col012")
DTSDestination("Education") = DTSSource("Col013")
DTSDestination("AreaOfExpertise") = DTSSource("Col014")
DTSDestination("Job_description") = DTSSource("Col015")
DTSDestination("Date_stamp") = DTSSource("Col016")
DTSDestination("Client_id") = DTSSource("Col017")
DTSDestination("Company_Url") = DTSSource("Col018")
DTSDestination("Enabled") = DTSSource("Col019")
DTSDestination("mailstatus") = DTSSource("Col020")
DTSDestination("IsHotJob") = DTSSource("Col021")
DTSDestination("IsFree") = DTSSource("Col022")
DTSDestination("Job_Type") = DTSSource("Col023")
DTSDestination("hhhotjob") = DTSSource("Col024")
Main = DTSTransformStat_OK
End Function

Function GetNewVal()
dim oComm
dim strConn

strConn = "Provider=sqloledb.1;Persist Security Info=False;Data Source=karuna;initial catalog=test;User Id=sa;Password=;"

set oComm = createobject("Adodb.command")

oComm.ActiveConnection = strConn
oComm.CommandType = 4
oComm.CommandText = "gsp_InsTablekey"
oComm.CommandTimeout = 0

oComm.Parameters.Append oComm.CreateParameter("@parTableName", 200, 1, 255, "jobadvt")
oComm.Parameters.Append oComm.CreateParameter("@parlast_id", 3, 2, 4)

oComm.Execute
GetNewVal = oComm.Parameters(1).Value
set oComm = nothing

End Function

******************************************************************************

The Stored Proc:

CREATE Procedure gsp_InsTablekey
(
@parTableName varchar,
@parlast_id Integer Output
)
As
Update Tablekey set @parlast_id = last_id + 1, last_id = last_id +1 where lower(TableName) = lower(@parTableName)
GO

Hope this will help for some one in future.

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