herschellrd
asked on
Need to insert CSV file into multiple tables using bulk insert and stored procedure and relate data in tables
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!
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!
may be you can use dts to do that and configure a job to run the dts.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
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.
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.
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
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
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...
which shouldn't have the patno as an identity column...