October 15, 2008 at 9:30 am
I am currently developing a SQL 2005 DB and an an application using (VB.Net). The purpose of the application is to store data returned by a tool (This is in CSV text format) into the DB for storage and analysis. I have mastered the import of this data into a dbo.ImportTable where all values are varchar(50). However I have not yet found the right way of comparing the data before inserting it into the table. I need to do this so I am not adding the same data over and over as the Tools do not recreate the CSV file, they only append to it. Can someone suggest a path that I may take to approach this problem?
Thanks:cool:
October 15, 2008 at 10:37 am
Here's a start:SELECT * FROM StagingTable S
Where Not Exists( Select * From TargetTable T
Where T.PK1 = Cast(S.PK1 as int)
And T.PK2 = Cast(S.PK2 as datetime)
)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 15, 2008 at 10:52 am
Does the data have any data elements that uniquely identifies each row?
October 15, 2008 at 11:49 am
Here is the app side.
Private Sub ProcessFile(ByVal fileName As String)
' MsgBox("Data is now available for import!", MsgBoxStyle.OkOnly, "Monitor")
Try
'Open the file to read from.
Dim sr As StreamReader = File.OpenText(fileName)
Dim MEAS As String
Dim MEASARRY As String()
Using sqlcon As New System.Data.SqlClient.SqlConnection("Data Source=PBELTERLT\SQLEXPRESS;Initial Catalog=WestportRoanoke;Integrated Security=True")
'Open the DB connection.
sqlcon.Open()
Dim cmd As New SqlClient.SqlCommand("dbo.DataFill", sqlcon) 'Idetnifies the stored procedure
Do While sr.Peek() >= 0
MEAS = sr.ReadLine() 'Sets the value as a string.
Console.WriteLine(MEAS) 'For debug only.
MEASARRY = MEAS.Split(",") 'Sets the value as a string array.
With cmd
.CommandType = CommandType.StoredProcedure
.Parameters.AddWithValue("@MEASARRY0", MEASARRY(0)) 'sets the parameter of the procedure to the parsed value of MEAS Date
.Parameters.AddWithValue("@MEASARRY1", MEASARRY(1)) 'Time
.Parameters.AddWithValue("@MEASARRY2", MEASARRY(2)) 'Torque
.Parameters.AddWithValue("@MEASARRY3", MEASARRY(3)) 'TorqueAngle
.Parameters.AddWithValue("@MEASARRY4", MEASARRY(4)) 'Volvo Line Set
.Parameters.AddWithValue("@MEASARRY5", MEASARRY(5)) 'Specifiaction ID number
.ExecuteNonQuery() 'inserts, updates, and deletes do not return data so use the nonquery execute
.Parameters.Clear()
End With
Loop
sr.Close()
If sqlcon.State <> ConnectionState.Closed Then
sqlcon.Close()
End If
End Using
Catch ex As Exception
MsgBox("Error", MsgBoxStyle.OkOnly)
End Try
End Sub
Here is the stored procedure side:
ALTER PROCEDURE DataFill
-- Add the parameters for the stored procedure here
@MEASARRY0 varchar(50) = 0,
@MEASARRY1 varchar(50) = 0,
@MEASARRY2 varchar(50) = 0,
@MEASARRY3 varchar(50) = 0,
@MEASARRY4 varchar(50) = 0,
@MEASARRY5 varchar(50) = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Insert Into dbo.ImportTable(Date, Tstamp, VolvoLS, Torque, TorqueAngle, SpecID)
Values (@MEASARRY0, @MEASARRY1, @MEASARRY4, @MEASARRY2, @MEASARRY3, @MEASARRY5)
END
My thoughts are to compare the second parameter at each loop, if the value allready exists in the dump table, reset the parameters and begin the looped process again.
October 15, 2008 at 12:58 pm
Thanks, but that doesn't answer my question. Is there anything in the data files that will uniquely identify each row in the import file?
😎
October 15, 2008 at 1:13 pm
Yes,
The tools insert a date and time that I could use to establish where I need to begin inserting the data, This is a sample of the CSV file from one of the tools:
(Where you have Date, Time, Torque, Torque Angle, Line Set and finally the specification ID.)
File Name: SP2NutRunner1.csv
9/18/2008,3:18:30 AM,554.05,33.5,V001008,13
9/18/2008,3:20:19 AM,553.31,36,V001008,14
9/18/2008,3:22:50 AM,553.31,62.8,V001008,15
9/18/2008,3:23:35 AM,553.31,43.9,V001008,16
My dump table has these fields. I do not treat any column in this table as a Key Field as this is only a look up table. The data type is varchar(50).
Does this answer your question?
Thanks,
Paul
October 15, 2008 at 1:21 pm
Helps, but I have one more question. What is the DDL for the final destination table?
😎
October 15, 2008 at 1:35 pm
I'm sorry if I seem a little slow, are you referring to what triggers the process. I use the System file Watcher to monitor the directory where the csv files are written to. I monitor the Date And Name of the file as I will have to process 22 tools running simultaneously. (Not an easy task for me anyway.) Does that help or am I losing you?
Thanks,
Paul:unsure:
October 15, 2008 at 1:55 pm
Take a look at the following code. Hopefully it will help you determine what you need to do accomplish.
/*
Create temporary tables for testing
*/
create table #MyImportTable (
MEASARRY0 varchar(50),
MEASARRY1 varchar(50),
MEASARRY2 varchar(50),
MEASARRY3 varchar(50),
MEASARRY4 varchar(50),
MEASARRY5 varchar(50)
);
create table #MyDestTable (
MeasureDateTime datetime,
Torque decimal(8,2),
TorqueAngle decimal(5,1),
VolvoLS varchar(50),
SpecID int
);
/*
Insert some test data into the Import table
*/
insert into #MyImportTable
select '9/18/2008','3:18:30 AM','554.05','33.5','V001008','13' union all
select '9/18/2008','3:20:19 AM','553.31','36','V001008','14';
/*
Insert the data into the destination table
*/
with DataImport (
MeasureDateTime,
Torque,
TorqueAngle,
VolvoLS,
SpecID
) as (
select
cast(MEASARRY0 + ' ' + MEASARRY1 as datetime),
cast(MEASARRY2 as decimal(8,2)),
cast(MEASARRY3 as decimal(5,1)),
MEASARRY4,
cast(MEASARRY5 as int)
from
#MyImportTable
)
insert into #MyDestTable (
MeasureDateTime,
Torque,
TorqueAngle,
VolvoLS,
SpecID
)
select
di.MeasureDateTime,
di.Torque,
di.TorqueAngle,
di.VolvoLS,
di.SpecID
from
DataImport di
left outer join #MyDestTable mdt
on (di.MeasureDateTime = mdt.MeasureDateTime)
where
mdt.MeasureDateTime is null;
/*
Display the data in both tables
*/
select * from #MyImportTable;
select * from #MyDestTable;
/*
truncate the import table and reinsert test data
*/
truncate table #MyImportTable;
/*
Insert test data into import table
*/
insert into #MyImportTable
select '9/18/2008','3:18:30 AM','554.05','33.5','V001008','13' union all
select '9/18/2008','3:20:19 AM','553.31','36','V001008','14' union all
select '9/18/2008','3:22:50 AM','553.31','62.8','V001008','15' union all
select '9/18/2008','3:23:35 AM','553.31','43.9','V001008','16';
/*
Insert data into final destination table -- no dups allowed
*/
with DataImport (
MeasureDateTime,
Torque,
TorqueAngle,
VolvoLS,
SpecID
) as (
select
cast(MEASARRY0 + ' ' + MEASARRY1 as datetime),
cast(MEASARRY2 as decimal(8,2)),
cast(MEASARRY3 as decimal(5,1)),
MEASARRY4,
cast(MEASARRY5 as int)
from
#MyImportTable
)
insert into #MyDestTable (
MeasureDateTime,
Torque,
TorqueAngle,
VolvoLS,
SpecID
)
select
di.MeasureDateTime,
di.Torque,
di.TorqueAngle,
di.VolvoLS,
di.SpecID
from
DataImport di
left outer join #MyDestTable mdt
on (di.MeasureDateTime = mdt.MeasureDateTime)
where
mdt.MeasureDateTime is null;
/*
Display the data in both tables
*/
select * from #MyImportTable;
select * from #MyDestTable;
/*
drop test tables
*/
drop table #MyImportTable;
drop table #MyDestTable;
😎
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply