Solved

Need to insert CSV file into multiple tables using bulk insert and stored procedure and relate data in tables

Posted on 2006-10-29
6
2,070 Views
Last Modified: 2008-01-09
I want to import a CSV file into 3 SQL tables - pat_06, sam_06, result_06. The CSV file will be uploaded regularly by users at ad hoc times so I have set up an ASP bulk insert command which triggers the bulk insert when the page is visited.
I have also created a Stored Procedure that bulk inserts the CSV file into a temporary table (temp_pat) and then inserts the data into the different tables. The problem I have is that I need the IDENTITY (patno) of each row of data after it is inserted into the first table, which is then to be inserted along with the other data into tables 2 and 3 so that each row remains related (patno = patno_sam and patno_res respectively). However, with the way I have the Stored Procedure set up at present the @@IDENTITY only copies across, of all the rows of data, the last row's IDENTITY and consequently I have no association between the tabled data.
I am sure I need to do some kind of loop but am not sure how. Can anyone help me out and show me sample code as I am a newbie and as a first stored procedure this has been a steep learning curve.

Copy of my stored procedure -

CREATE PROCEDURE ps_pat_Import
@PathFileName varchar(100),
@patno integer,
@FileType tinyint
AS

DECLARE @SQL varchar(2000)
IF @FileType = 1
 BEGIN
   SET @SQL = "BULK INSERT temp_pat FROM '"+@PathFileName+"' WITH (FIRSTROW = 2, FIELDTERMINATOR = '"",""') "
 END
ELSE
 BEGIN
  SET @SQL = "BULK INSERT temp_pat FROM '"+@PathFileName+"' WITH (FIRSTROW = 2, FIELDTERMINATOR = ',') "
 END

--Step 2: Execute BULK INSERT statement

EXEC (@SQL)

INSERT pat_06 (sname, sampleday, samplemonth, sampleyear)
SELECT sname, sampleday, samplemonth, sampleyear
FROM temp_pat
SET @patno = @@IDENTITY       --ID of the last inserted record
INSERT sam_06 (sampletime, timeu, localid, patno_sam)
SELECT sampletime, timeu, localid, @patno
FROM temp_pat
SET @patno = @@IDENTITY       --ID of the last inserted record
INSERT result_06 (acode, lim, conc, units, patno_res)
SELECT acode, lim, conc, units, @patno
FROM temp_pat

TRUNCATE TABLE temp_pat

Thanks!
0
Comment
Question by:herschellrd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 3

Expert Comment

by:mahe2000
ID: 17831725
may be you can use dts to do that and configure a job to run the dts.
0
 
LVL 11

Accepted Solution

by:
regbes earned 500 total points
ID: 17831908
Hi herschellrd,

Couple of thinsgs to try here

1. add an identity col to the table temp_pat so when the bulk insert populates it it is populated and you can use that as patno in each of your 3 tables
2. the 3 tables look like they have a one to one relationship? if so why not just combine them into one table?


HTH

R.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17832647
What is the identity field in pat_06? If it is patno_pat then do this:

INSERT pat_06 (sname, sampleday, samplemonth, sampleyear)
SELECT sname, sampleday, samplemonth, sampleyear
FROM temp_pat

INSERT sam_06 (partno,sampletime, timeu, localid, patno_sam)
SELECT sampletime, timeu, localid, patno_pat

... ensuring that partno_pat IS an IDENTITY column and patno_sam IS NOT.


But think about what regbes says... why do you have three tables with idetical data?
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17832657
Sorry, last line should have been:

INSERT sam_06 (partno,sampletime, timeu, localid, patno_sam)
SELECT sampletime, timeu, localid, patno_pat
FROM pat_06


or even better, follow all of regbes instructions and assign the identity in the staging table.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 17833229
please clarify:
the code will make all inserted recoreds to have same "patno_res" - is it what you want?
--
INSERT result_06 (acode, lim, conc, units, patno_res)
SELECT acode, lim, conc, units, @patno
FROM temp_pat
---
if 'yes'
<--ID of the last inserted record>
do you mean last 'patno_res' or MAximum #?
if 'yes'
try:
select  @patno = max(patno_res) from result_06      --ID of the last inserted record
INSERT result_06 (acode, lim, conc, units, patno_res)
SELECT acode, lim, conc, units, @patno
FROM temp_pat
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17834116
consider having a trigger on the intial table do the work of inserting the rows to the other tables...
which shouldn't have the patno as an identity column...

0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question