Import to table from CSV & calculated value

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)

Import to table from CSV & calculated value

Author

Topic

sgnerd
Starting Member

6 Posts

Posted - 2003-05-23 : 00:26:10

Hi Friends,

I am using sql 2K on win2k server. I have a table of 10 columns, out of which 8 columns to be filled from the CSV file and another 2 columns to be filled by a query (it has to select value from other table)

I would like to insert each and every record simultaneously from csv file and from the query.

Anybody could advise me on this, please.

Thanks in advance.

Regards
kumar

macka
Posting Yak Master

162 Posts

Posted - 2003-05-23 : 03:17:46

Kumar,

How about this ?

1. Import the CSV into a temp table.
2. Insert the records from temp table joined with other table into target table.
3. Drop the temp table.

macka

--
There are only 10 types of people in the world - Those who understand binary, and those who don't.

sgnerd
Starting Member

6 Posts

Posted - 2003-05-23 : 06:10:13

thank you macha.......but is there another way to do this.....because i am familier with DTS and it options

Regards
Kumar

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-23 : 10:40:04

You can do it with dts just have to consider if the file name changes. If so then you need an activex task to modify the dts package dynamically that would be the first step in the task.

The package would contain the following task

Step1.ActiveX to modify name of file in Step3. (This may not be necessary if file name does not change)

Step2.Execute SQL Task To Truncate stagging Table

TRUNCATE TABLE staggingTable

Step3.Transform Data Task to import file into stagging table

Step4.Execute SQL Task to update live table by joining stagging stagging table with 2 column query.

Update liveTable
SET liveTable.col1 = staggingTable.col1,
SET liveTable.col2 = query.col1
FROM
liveTable
INNER JOIN staggingTable ON liveTable.[ID] = staggingTable.[ID]
INNER JOIN query ON liveTable.[ID] = query.[ID]

Another way would be
Write a stored procedure that takes in a file name and creates a linked server. Then replace the portion with stagging table with an openquery call or use a 4 part name to reference the linked server.

Here's an example from bol on adding a text file as a linked server

--Create a linked server.
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\data\distqry',
NULL,
'Text'
GO

--Set up login mappings.
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL
GO

--List the tables in the linked server.
EXEC sp_tables_ex txtsrv
GO

--Query one of the tables: file1#txt
--using a 4-part name.
SELECT *
FROM txtsrv...[file1#txt]

Edited by - ValterBorges on 05/23/2003 10:59:53

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources