Solved

Creating a module to import CSV into MS SQL

Posted on 2008-06-26
48
1,103 Views
Last Modified: 2012-08-13
Hello,

I have a pre-existing database named waiktrade and table named PRHOURS.  The data type of all fields is VARCHAR, and have been created from a CSV exported from Excel.  Upon using the "Import Data" function of the SQL Enterprise Manager, I was able to create the table PRHOURS.  Here are the fields;
HDRECIDN      HDHDRLNK      HDWRKDTE      HDCOMNUM      HDSTRNUM      HDSHFNUM      HDREGNUM      HDEMPNUM      HDREGHRS      HDOVTHRS      HDRECSTS      HDRECSRC      HDUSRCHG      HDDTECHG

What I would like to do is upload ANOTHER CSV file which has current data, then merge it into the pre-existing file in the SQL database.  The uploaded file, it sits locally in a folder on the webserver.  The name of this file is "upload_prhours.csv"  The field/columnames are in the first row.

A condition on merging...both the pre-existing and upload CSV files are the same in struction...the upload_prhours.csv has the most current information, therefore, we have to build this module to query the last record of the pre-existing database and confirm that we don't overlap data.  Ideally, to prop the new information in chronological order. HDDTECHG is the date/time and should be used to compare the data.

I need the SQL command to execute in my ASP application.

I"m using ASP and Microsoft SQL Server 2000.

Thank you,
irwinpks
0
Comment
Question by:irwinpks
  • 27
  • 14
  • 6
  • +1
48 Comments
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
Build a sp that uses BULK INSERT into your table.
0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@jmoss111

That would be an option.

I've provided fieldnames and parameters above so that I could get a drop and go solution.  I'm aware that you could use BULK INSERT.  Attached is an arbitrary example.  Apply the information above to arrive to a solution please.

Thank you,
irwinpks

BULK INSERT testBulkInsert

FROM     'd:\work\test.txt'

WITH (

    FORMATFILE='d:\work\testImport-f-n.Fmt',

    TABLOCK     

)
 

insert into testOpenRowset(c1, c2, c3, c4)

SELECT    t1.c1, t1.c2, t1.c3, t1.c4

FROM    OPENROWSET

    ( 

        BULK 'd:\work\test.txt', 

        FORMATFILE = 'd:\work\testImport-f-n.Fmt'

    ) AS t1(c1, c2, c3, c4);
 

exec master..xp_cmdshell 'bcp test.dbo.testBCP in d:\work\test.txt -T -b1000000 -fd:\work\testImport-f-n.Fmt -h"tablock"'

Open in new window

0
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
If the input file and the output file datatypes are the same and in the same order and both tables have the same amount of fileds you really don't need a formatfile.
BULK INSERT Aging from 'D:\APStaging\OracleAgingExtract.txt' with (FIELDTERMINATOR  = ',')

Open in new window

0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@jmoss111

I've provided the code as an ARBITRARY example.  What I'm looking for is the SQL command to execute that will work for me in my above parameters.

Thank you,
irwinpks
0
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
What is the delimiter?
0
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
From your code if the dleimiter is , then the snippet should work.
BULK INSERT testBulkInsert from 'd:\work\test.txt' with (FIELDTERMINATOR  = ',')

Open in new window

0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@jmoss111

It's a COMMA...see attached snippet for sample data from PRHOURS.
"HDRECIDN","HDHDRLNK","HDWRKDTE","HDCOMNUM","HDSTRNUM","HDSHFNUM","HDREGNUM","HDEMPNUM","HDREGHRS","HDOVTHRS","HDRECSTS","HDRECSRC","HDUSRCHG","HDDTECHG"

711033,69247,2008-04-20 00:00:00,"03","127","1","01","1080 ",4.5,0," ","S","IRWINPKS          ",2008-04-21 13:54:00

711089,69307,2008-04-20 00:00:00,"03","127","1","01","1753 ",1,0," ","S","IRWINPKS          ",2008-04-21 13:54:00

710981,69213,2008-04-20 00:00:00,"03","127","2","02","0218 ",3.5,0," ","S","IRWINPKS          ",2008-04-21 13:54:00

710985,69287,2008-04-20 00:00:00,"03","127","2","02","0268 ",2,0," ","S","IRWINPKS          ",2008-04-21 13:54:00

711001,69310,2008-04-20 00:00:00,"03","127","2","02","0516 ",3.5,0," ","S","IRWINPKS          ",2008-04-21 13:54:00

Open in new window

0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@jmoss111

What is "testBulkInsert"?  Is that the name of table that we are inserting into?  If so, then it is PRHOURS as described in my original question.

Also the uploaded file that we are using is C:\upload_prhours.csv

...and there is the condition that we need to filter any overlaps in the date & time

Please advise.

Thank you,
irwinpks
0
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
That was at the top of your example, and yes its the table name so you need to replace what needs to be replaced
0
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
So what you need to do is insert int a staging table and do an insert from there to handle your date range
0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@jmoss111

"That was at the top of your example, and yes its the table name so you need to replace what needs to be replaced"

As mentioned, we need to use my parameters and not the arbitrary example...therefore...

BULK INSERT testBulkInsert from 'd:\work\test.txt' with (FIELDTERMINATOR  = ',')

will be...
BULK INSERT PRHOURS from 'c:\upload_prhours.csv' with (FIELDTERMINATOR  = ',')

To reiterate part 2 of my question...
"A condition on merging...both the pre-existing and upload CSV files are the same in struction...the upload_prhours.csv has the most current information, therefore, we have to build this module to query the last record of the pre-existing database and confirm that we don't overlap data.  Ideally, to prop the new information in chronological order. HDDTECHG is the date/time and should be used to compare the data."

Instead of making a separate module, it would be more efficient to handle filtering when doing the insertion.  What would that be?

Thank you,
irwinpks


0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@jmoss111

Any other thoughts?

Thank you,
irwinpks
0
 
LVL 25

Expert Comment

by:kevp75
Comment Utility
if it's not answered by the morning, I will attempt to come up with something....

please let me know if I am in the wrong frame of mind..

you need something to update existing records based on what is in the CSV, or insert new records if they do not currently exist?
0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@kevp75

Thank you for stopping by...

Yes to both your statements...and that's based on the parameters above.  Rather than seek for guidance...I'm shooting for the exact answer.
Jmoss stated the BULK INSERT function (which I knew we already had to do that)...but to complete the question...we have to filter the recent uploaded CSV and compare it to the existing table PRHOURS.

Ideally as the CSV is being inserted into the database, I'm thinking there should be a condition in there to allow (or not) adding a new record.  HDDTECH is unique (date & time).

irwinpks
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Well, there is a few column definitions missing, and hazard a guess that the first column is an identity of some sort, so maybe needs to be bypassed...
Also, you mention HDDTECH as being unique, found the sample you posted to be HDTECHG and is not unique... so not sure how to / what to update in that case.
Also if it is truely comma delimited and quote encapsulated exactly as per the sample provided, then far better off using a format file (attached - save as c:\upload_prhours.fmt).

with quite a few assumptions, and defaulting to varchar(60) for everything, please have a look at the example below as a general outline... Can refine later once the basic approach is OK, and some of the ambiguity above is resolved.


if exists (select * from information_schema.tables where table_name = 'upload_PRHOURS') drop table upload_PRHOURS

go
 

create table upload_PRHOURS (HDRECIDN varchar(60),HDHDRLNK varchar(60),HDWRKDTE varchar(60),HDCOMNUM varchar(60),HDSTRNUM varchar(60),HDSHFNUM varchar(60),HDREGNUM varchar(60),HDEMPNUM varchar(60),HDREGHRS varchar(60),HDOVTHRS varchar(60),HDRECSTS varchar(60),HDRECSRC varchar(60),HDUSRCHG varchar(60),HDDTECHG varchar(60))

go
 

--BULK INSERT upload_PRHOURS from 'c:\upload_prhours.csv' with (FIELDTERMINATOR  = ',', firstrow=2)

--go
 

insert upload_prhours

select * from openrowset( BULK 'c:\upload_prhours.csv', FORMATFILE = 'c:\upload_prhours.fmt', MAXERRORS=10, firstrow = 2) AS a  

go
 

select * from upload_PRHOURS

go
 

update PRHOURS set HDCOMNUM = s.hdcomnum, HDSTRNUM = s.hdstrnum -- etc

from PRHOURS

INNER JOIN UPLOAD_PRHOURS s on prhours.HDDTECHG = convert(datetime,s.hddtechg,120) -- and prhours.HDEMPNUM = s.HDEMPNUM

go
 

INSERT PRHOURS (HDRECIDN,HDHDRLNK,HDWRKDTE,HDCOMNUM,HDSTRNUM,HDSHFNUM,HDREGNUM,HDEMPNUM,HDREGHRS,HDOVTHRS,HDRECSTS,HDRECSRC,HDUSRCHG,HDDTECHG)

SELECT HDRECIDN,HDHDRLNK,HDWRKDTE,HDCOMNUM,HDSTRNUM,HDSHFNUM,HDREGNUM,HDEMPNUM,HDREGHRS,HDOVTHRS,HDRECSTS,HDRECSRC,HDUSRCHG,HDDTECHG

from upload_PRHOURS

where not exists (select * from PRHOURS p where p.HDDTECHG = convert(datetime,upload_prhours.hddtechg,120)) -- and upload_prhours.HDEMPNUM = p.HDEMPNUM

go

Open in new window

upload-prhours.fmt.txt
0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@markwillis

You're right about HDDTECH...and "unique" wasn't what I should've used to describe this.  For better words...it's the column to look out for and compare against the already existing table.

The first column HDRECIDN (HD record ID number) is the unique field here.

I'll implement your code within the next 24 hours and will respond with my results.

Thank you!
irwinpks

0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@markwillis

This is what I get...
----------------------------------
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near 'go'.
/merge_database_prhours.asp, line 44
----------------------------------
(From my code) Line 44:  Set RS = RS_cmd.Execute

Below in the attach snippet, I've added your code and made changes to include the exact file location of the upload_prhours.csv & .fmt file.  In addition I've added the missing fields (Line 17 of your code above snippet).  These fields I did not add in:

HDRECIDN, HDHDRLNK, HDWRKDTE  (I wasn't sure if those were required as you started off with "HDCOMNUM = s.hdcomnum").

In your code above (line 7 & 8)
--BULK INSERT upload_PRHOURS from 'c:\upload_prhours.csv' with (FIELDTERMINATOR  = ',', firstrow=2)
--go

I removed the "--"  ...was that correct to do?

Please advise.

Thank you,
irwinpks
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>

<!--#include file="Conn.asp" -->

<%

Dim RS

Dim RS_cmd

Dim RS_numRows, SQL
 

Set RS_cmd = Server.CreateObject ("ADODB.Command")

RS_cmd.ActiveConnection = MM_Conn_STRING

%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Merge Database - PRHOURS</title>

</head>
 

<body>

<%
 

SQL = "if exists (select * from information_schema.tables where table_name = 'upload_PRHOURS') drop table upload_PRHOURS" & vbCrLf

SQL = SQL & "go" & vbCrLf

SQL = SQL & "create table upload_PRHOURS (HDRECIDN varchar(60),HDHDRLNK varchar(60),HDWRKDTE varchar(60),HDCOMNUM varchar(60),HDSTRNUM varchar(60),HDSHFNUM varchar(60),HDREGNUM varchar(60),HDEMPNUM varchar(60),HDREGHRS varchar(60),HDOVTHRS varchar(60),HDRECSTS varchar(60),HDRECSRC varchar(60),HDUSRCHG varchar(60),HDDTECHG varchar(60))" & vbCrLf

SQL = SQL & "go" & vbCrLf

SQL = SQL & "BULK INSERT upload_PRHOURS from 'c:\inetpub\clients\waiktrade\data\tempUploads\upload_prhours.csv' with (FIELDTERMINATOR  = ',', firstrow=2)" & vbCrLf

SQL = SQL & "go" & vbCrLf

SQL = SQL & "insert upload_prhours" & vbCrLf

SQL = SQL & "select * from openrowset( BULK 'c:\inetpub\clients\waiktrade\data\tempUploads\upload_prhours.csv', FORMATFILE = 'c:\inetpub\clients\waiktrade\data\tempUploads\upload_prhours.fmt', MAXERRORS=10, firstrow = 2) AS a" & vbCrLf

SQL = SQL & "go" & vbCrLf

SQL = SQL & "select * from upload_PRHOURS" & vbCrLf

SQL = SQL & "go" & vbCrLf

SQL = SQL & "update PRHOURS set HDCOMNUM = s.hdcomnum, HDSTRNUM = s.hdstrnum, HDSHFNUM = s.hdshfnum, HDREGNUM = s.hdregnum, HDEMPNUM = s.HDEMPNUM, HDREGHRS = s.hdreghrs, HDRECSTS = s.hdrecsts, HDRECSRC = s.hdrecsrc, HDUSRCHG = s.hdusrchg, HDDTECHG = s.hddtechg from PRHOURS" & vbCrLf

SQL = SQL & "INNER JOIN UPLOAD_PRHOURS s on prhours.HDDTECHG = convert(datetime,s.hddtechg,120) -- and prhours.HDEMPNUM = s.HDEMPNUM" & vbCrLf

SQL = SQL & "go" & vbCrLf

SQL = SQL & "INSERT PRHOURS (HDRECIDN,HDHDRLNK,HDWRKDTE,HDCOMNUM,HDSTRNUM,HDSHFNUM,HDREGNUM,HDEMPNUM,HDREGHRS,HDOVTHRS,HDRECSTS,HDRECSRC,HDUSRCHG,HDDTECHG)" & vbCrLf

SQL = SQL & "SELECT HDRECIDN,HDHDRLNK,HDWRKDTE,HDCOMNUM,HDSTRNUM,HDSHFNUM,HDREGNUM,HDEMPNUM,HDREGHRS,HDOVTHRS,HDRECSTS,HDRECSRC,HDUSRCHG,HDDTECHG from upload_PRHOURS where not exists (select * from PRHOURS p where p.HDDTECHG = convert(datetime,upload_prhours.hddtechg,120)) -- and upload_prhours.HDEMPNUM = p.HDEMPNUM" & vbCrLf

SQL = SQL & "go" & vbCrLf
 

Response.Write(SQL)
 

RS_cmd.CommandText = SQL 

RS_cmd.Prepared = true
 

Set RS = RS_cmd.Execute

RS_numRows = 0

RS.Close()

Set RS = Nothing

%>

</body>

</html>

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
the bulk insert was meant to be commented out - was really just there for show. The real import is via the openrowset ... So, need to remove those two lines.

and I think we need to resolve in a query window before anything else... Thought was we would wrap it up in a stored procedure, then simple exec that stored procedure from your code... otherwise, they are a series of seperate SQL commands (and consequently do not need the GO)...

but, first step is the select * from openrowset...   let's make sure it is reading the import file.
then
we will check the update / import from the upload file
then
we will make it a stored procedure...
then
you will change your code to run a stored proc (no parameters, so can be sql = "exec u_Upload_PRhours" vbCrLf )
0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@mark_wills

Ok..let me absorb this.  Gimme 30 minutes
0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@markwills

I nixxed the BULK INSERT comment, taken out the "go(s)" and cut the comparison section.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'BULK'.
/merge_database_prhours.asp, line 31
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>

<!--#include file="Conn.asp" -->

<%

Dim RS

Dim RS_cmd

Dim RS_numRows, SQL
 

Set RS_cmd = Server.CreateObject ("ADODB.Command")

RS_cmd.ActiveConnection = MM_Conn_STRING

%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Merge Database - PRHOURS</title>

</head>
 

<body>

<%
 

SQL = "if exists (select * from information_schema.tables where table_name = 'upload_PRHOURS') drop table upload_PRHOURS" & vbCrLf

SQL = SQL & "create table upload_PRHOURS (HDRECIDN varchar(60),HDHDRLNK varchar(60),HDWRKDTE varchar(60),HDCOMNUM varchar(60),HDSTRNUM varchar(60),HDSHFNUM varchar(60),HDREGNUM varchar(60),HDEMPNUM varchar(60),HDREGHRS varchar(60),HDOVTHRS varchar(60),HDRECSTS varchar(60),HDRECSRC varchar(60),HDUSRCHG varchar(60),HDDTECHG varchar(60))" & vbCrLf

SQL = SQL & "insert upload_prhours" & vbCrLf

SQL = SQL & "select * from openrowset( BULK 'c:\inetpub\clients\waiktrade\data\tempUploads\upload_prhours.csv', FORMATFILE = 'c:\inetpub\clients\waiktrade\data\tempUploads\upload_prhours.fmt', MAXERRORS=10, firstrow = 2) AS a"
 

Response.Write(SQL)
 

RS_cmd.CommandText = SQL 

RS_cmd.Prepared = true
 

Set RS = RS_cmd.Execute

RS_numRows = 0

RS.Close()

Set RS = Nothing

%>

</body>

</html>

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Ummm, errr, sorry about that - you are not running SQL 2005 are you ... Knew there was a reason to leave that BULK INSERT hanging around...

We will end up getting rid of openrowset, and running the bulk insert using the format file - so not too different...

OK, in a query window (do not change code yet), and then do :


if exists (select * from information_schema.tables where table_name = 'upload_PRHOURS') drop table upload_PRHOURS

go
 

create table upload_PRHOURS (HDRECIDN varchar(60),HDHDRLNK varchar(60),HDWRKDTE varchar(60),HDCOMNUM varchar(60),HDSTRNUM varchar(60),HDSHFNUM varchar(60),HDREGNUM varchar(60),HDEMPNUM varchar(60),HDREGHRS varchar(60),HDOVTHRS varchar(60),HDRECSTS varchar(60),HDRECSRC varchar(60),HDUSRCHG varchar(60),HDDTECHG varchar(60))

go
 
 

BULK INSERT upload_PRHOURS from 'c:\upload_prhours.csv' with (FORMATFILE = 'c:\upload_prhours.fmt', firstrow=2)

go

Open in new window

0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@markwills

I'll work on this in a few hours.  Thank you for the quick reply.

A little more background...  I'm running Windows 2003 Server with MS SQL 2000 SP4
0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@markwills

OK...I've got the code in there. No errors.
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>

<!--#include file="Conn.asp" -->

<%

Dim RS

Dim RS_cmd

Dim RS_numRows, SQL
 

Set RS_cmd = Server.CreateObject ("ADODB.Command")

RS_cmd.ActiveConnection = MM_Conn_STRING

%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Merge Database - PRHOURS</title>

</head>
 

<body>

<%

SQL = "if exists (select * from information_schema.tables where table_name = 'upload_PRHOURS') drop table upload_PRHOURS" & vbCrLf

SQL = SQL & "create table upload_PRHOURS (HDRECIDN varchar(60),HDHDRLNK varchar(60),HDWRKDTE varchar(60),HDCOMNUM varchar(60),HDSTRNUM varchar(60),HDSHFNUM varchar(60),HDREGNUM varchar(60),HDEMPNUM varchar(60),HDREGHRS varchar(60),HDOVTHRS varchar(60),HDRECSTS varchar(60),HDRECSRC varchar(60),HDUSRCHG varchar(60),HDDTECHG varchar(60))" & vbCrLf

SQL = SQL & "BULK INSERT upload_PRHOURS from 'c:\inetpub\client\waiktrade\data\tempUploads\upload_prhours.csv' with (FORMATFILE = 'c:\inetpub\client\waiktrade\data\tempUploads\upload_prhours.fmt', firstrow=2)"
 

Response.Write(SQL)
 

'RS_cmd.CommandText = SQL 

'RS_cmd.Prepared = true
 

'Set RS = RS_cmd.Execute

'RS_numRows = 0

'RS.Close()

'Set RS = Nothing

%>

</body>

</html>

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Did you try it in a query window first ?

ie, start up Query Analyser (can do that via enterprise manager, browse to you database then use the menu bar, tools, query analyser), when it fires up, just make sure you are looking at the database (drop down option on menu bar). paste the code  from my previous posting into the query window and press the F5 function key (keyboard shortcut to run the query).

then in a new window (use CTRL+N keyboard shortcut) do:

select * from upload_prhours

make sure it has loaded...

then in that same window, we can check the update and insert routines...

update PRHOURS set HDCOMNUM = s.hdcomnum, HDSTRNUM = s.hdstrnum, HDSHFNUM = s.hdshfnum, HDREGNUM = s.hdregnum, HDEMPNUM = s.HDEMPNUM, HDREGHRS = s.hdreghrs, HDRECSTS = s.hdrecsts, HDRECSRC = s.hdrecsrc, HDUSRCHG = s.hdusrchg, HDDTECHG = s.hddtechg
from PRHOURS
INNER JOIN UPLOAD_PRHOURS s on prhours.HDDTECHG = convert(datetime,s.hddtechg,120) and s.hdrecidn = prhours.hdrecidn
GO

INSERT PRHOURS  (HDRECIDN, HDHDRLNK, HDWRKDTE, HDCOMNUM, HDSTRNUM, HDSHFNUM, HDREGNUM, HDEMPNUM, HDREGHRS, HDOVTHRS, HDRECSTS, HDRECSRC, HDUSRCHG, HDDTECHG)
SELECT HDRECIDN, HDHDRLNK, HDWRKDTE, HDCOMNUM, HDSTRNUM, HDSHFNUM, HDREGNUM, HDEMPNUM, HDREGHRS, HDOVTHRS, HDRECSTS, HDRECSRC, HDUSRCHG, HDDTECHG
from upload_PRHOURS where not exists (select * from PRHOURS p where p.HDDTECHG = convert(datetime,upload_prhours.hddtechg,120) and p.hdrecidn <> upload_prhours.hdrecidn)
GO
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@mark_wills
I'm a novice at using the Query Analyzer.  I'll work on this tonight and will get back to you in the morning.

Thank you for being patient.
irwinpks
0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@mark_willis

Still working on this.... sorry for the delay...going through a learning curve with the SQL analyzer

irwinpks
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
No Problems...
0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@mark_wills

Everything went OK, until "#3" please review the image.  It's a syntax error, but I'm not sure what it causing it.

irwinpks
EE-Help-22055993.jpg
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
OK, it is complaining about the column hdtechg from the upload_hours table in the convert. Cannot see that column in your screen dump...

basically, all date in that column need to be of the format yyyy-mm-dd hh:mm:ss - the same way that hdwrkdate looks in the screen shot above.

can you attach the actual upload csv file ?  and in the meantime, have a look at the upload_prhours table scrolling through and see if you can notice anything non-date like in that column...
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Oh, by the way, also assume that the prhours column hddtechg is also a datetime... Can you please check ?
0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@mark_wills

Couldn't send zip of CSV (and it is not allowed).  Hopefully you have Excel?

HDDTECHG
6/9/2008  12:56:00 PM (you can see that in the file)
upload-prhours.xls
0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@mark_wills

here is that section of the screendump that shows HDDTECHG
query-part-4.bmp
0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@mark_wills

Was there anything else you need from me?
irwinpks
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
ummm, yep, to double check that column, in the PRHours table...

or do this... and post the results...

select * from information_schema.columns where table_name like '%prhours%'

I can import and post that file just fine... so there is obviously some data, or table definition that is different...
0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@mark_wills

Here is the results you requested.

irwinpks
Q-23519113-sql-excel.xls
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Ahhh, I see, they are all varchar...


we have a choice, either convert PRHOURS.HDDTECHG to datetime as well, or, not convert the upload_prhours.hddtechg - so long as they are in the same format, and we are just checking for equality, then we do not have to convert....

try this:

select *
from PRHOURS
INNER JOIN UPLOAD_PRHOURS s on prhours.HDDTECHG = s.hddtechg and s.hdrecidn = prhours.hdrecidn
GO

and if that is OK, then, the queries previously posted do not have to convert to datetime...
0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@mark_wills

All I get is a listing of column (fieldnames).  No errors.  Was that the result I should expect?
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Yep, it simply means we are now comparing varchar with varchar.... by converting one of them to datetime, the other one was also trying to be a datetime and unless it is in the format yyyymmdd hh:mm:ss it will not be able to dynamically convert to a datetime...

so...

we are now back to the updates / inserts... I am a bit worried now about how we are handling these - there really must be some kind of "unique" condition... which I guess is hdrecidn, in which case, why are we checking date as well ?


update PRHOURS set HDCOMNUM = s.hdcomnum, HDSTRNUM = s.hdstrnum, HDSHFNUM = s.hdshfnum, HDREGNUM = s.hdregnum, HDEMPNUM = s.HDEMPNUM, HDREGHRS = s.hdreghrs, HDRECSTS = s.hdrecsts, HDRECSRC = s.hdrecsrc, HDUSRCHG = s.hdusrchg, HDDTECHG = s.hddtechg
from PRHOURS
INNER JOIN UPLOAD_PRHOURS s on prhours.HDDTECHG = s.hddtechg and s.hdrecidn = prhours.hdrecidn
GO

INSERT PRHOURS  (HDRECIDN, HDHDRLNK, HDWRKDTE, HDCOMNUM, HDSTRNUM, HDSHFNUM, HDREGNUM, HDEMPNUM, HDREGHRS, HDOVTHRS, HDRECSTS, HDRECSRC, HDUSRCHG, HDDTECHG)
SELECT HDRECIDN, HDHDRLNK, HDWRKDTE, HDCOMNUM, HDSTRNUM, HDSHFNUM, HDREGNUM, HDEMPNUM, HDREGHRS, HDOVTHRS, HDRECSTS, HDRECSRC, HDUSRCHG, HDDTECHG
from upload_PRHOURS where not exists (select * from PRHOURS p where p.HDDTECHG = upload_prhours.hddtechg and p.hdrecidn = upload_prhours.hdrecidn)
GO
0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@mark_wills

Progress!  But to comment on the "unique" condition.  After looking at the table again, you're right on HDRECIDN...it's the only unique field.  I believe I brainfarted when I was thinking that HDDTECHG was the ideal field...it's not because there are multiple rows with the exact same information.  This would eliminate duplicate rows from the bulk insertion.

The result I got was 2255 records affected, instead of the 2836 I should have gotten.  I reran the query, and then "no records affected".  Could you please revise your code to seek on HDRECIDN instead?  I think we got a winner once that is done.

Thank you,
irwinpks
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Already have... You did run it. There must have been legitimately 500 odd records pre-existing, and thats why there were "no records affected" the second time (in the insert that is).

If you look at the code, you will see that I have included "and p.hdrecidn = upload_prhours.hdrecidn" in anticipation that it was correct - should have told you...
0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@mark_wills

Hmm...OK on the query, but this is what I have.

In the table PRHOURS (original), the row reports HDRECIDN as:
712492

In the PRHOURS.CSV XLS file, HDRECIDN has the following entries (2835 records)
716909 - 724065

I've restored my original table in the database and reapplied your code.  The last record (with the highest HDRECIDN number) is:
718005

The result should be 724065.

Are we filtering something else out?
irwinpks
PS...I know we're almost there :-)
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
it is not neccessarily sorted into sequence, so, try this:

select max(HDRECIDN) from prhours
0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@mark_wills

718005 is the result
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
Comment Utility
Hmmm...

I just ran a quick test (or two), and came up with the expected results :


INSERT PRHOURS  (HDRECIDN, HDHDRLNK, HDWRKDTE, HDCOMNUM, HDSTRNUM, HDSHFNUM, HDREGNUM, HDEMPNUM, HDREGHRS, HDOVTHRS, HDRECSTS, HDRECSRC, HDUSRCHG, HDDTECHG) 

SELECT HDRECIDN, HDHDRLNK, HDWRKDTE, HDCOMNUM, HDSTRNUM, HDSHFNUM, HDREGNUM, HDEMPNUM, HDREGHRS, HDOVTHRS, HDRECSTS, HDRECSRC, HDUSRCHG, HDDTECHG 

from upload_PRHOURS where not exists (select * from PRHOURS p where p.HDDTECHG = upload_prhours.hddtechg and p.hdrecidn = upload_prhours.hdrecidn) 

GO
 

select max(hdrecidn) from prhours    -- returns (for my test) 811089

GO
 

select max(hdrecidn) from upload_prhours  -- same as above

GO
 

update upload_prhours set hdrecidn = convert(varchar(10),convert(int, hdrecidn) + 100000)

GO
 

select max(hdrecidn) from upload_prhours -- now shows 911089

GO
 

INSERT PRHOURS  (HDRECIDN, HDHDRLNK, HDWRKDTE, HDCOMNUM, HDSTRNUM, HDSHFNUM, HDREGNUM, HDEMPNUM, HDREGHRS, HDOVTHRS, HDRECSTS, HDRECSRC, HDUSRCHG, HDDTECHG) 

SELECT HDRECIDN, HDHDRLNK, HDWRKDTE, HDCOMNUM, HDSTRNUM, HDSHFNUM, HDREGNUM, HDEMPNUM, HDREGHRS, HDOVTHRS, HDRECSTS, HDRECSRC, HDUSRCHG, HDDTECHG 

from upload_PRHOURS where not exists (select * from PRHOURS p where p.HDDTECHG = upload_prhours.hddtechg and p.hdrecidn = upload_prhours.hdrecidn) 

GO
 

select max(hdrecidn) from prhours -- now shows 911089

GO

Open in new window

0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@mark_wills

OK...I streamlined the process

*** [Create the table] ***
create table upload_PRHOURS (HDRECIDN varchar(60),HDHDRLNK varchar(60),HDWRKDTE varchar(60),HDCOMNUM varchar(60),HDSTRNUM varchar(60),HDSHFNUM varchar(60),HDREGNUM varchar(60),HDEMPNUM varchar(60),HDREGHRS varchar(60),HDOVTHRS varchar(60),HDRECSTS varchar(60),HDRECSRC varchar(60),HDUSRCHG varchar(60),HDDTECHG varchar(60))
go

*** [Insert the records into a temp SQL table "upload_PRHOURS] ***
BULK INSERT upload_PRHOURS from 'c:\inetpub\client\waiktrade\data\tempuploads\upload_prhours.csv' with (FORMATFILE = 'c:\inetpub\client\waiktrade\data\tempuploads\upload_prhours.fmt', firstrow=2)
go

*** [Compare "upload_PRHOURS" to "PRHOURS", anything NOT in "PRHOURS" will be inserted] ***
INSERT PRHOURS  (HDRECIDN, HDHDRLNK, HDWRKDTE, HDCOMNUM, HDSTRNUM, HDSHFNUM, HDREGNUM, HDEMPNUM, HDREGHRS, HDOVTHRS, HDRECSTS, HDRECSRC, HDUSRCHG, HDDTECHG)
SELECT HDRECIDN, HDHDRLNK, HDWRKDTE, HDCOMNUM, HDSTRNUM, HDSHFNUM, HDREGNUM, HDEMPNUM, HDREGHRS, HDOVTHRS, HDRECSTS, HDRECSRC, HDUSRCHG, HDDTECHG
from upload_PRHOURS where not exists (select * from PRHOURS p where p.HDDTECHG = upload_prhours.hddtechg and p.hdrecidn = upload_prhours.hdrecidn)
GO
0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@mark_wills

Here is a bonus question for you.
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23594533.html

We got the procedure running, but I don't know how to execute this puppy from the ASP page without breaking apart the procedue into small strings to execute.  Is there an easier way?

Thank you for your help!!
irwinpks
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Thanks for the points, and thanks for the bonus question - was expecting to do it here - that is most generous of you...
0
 
LVL 30

Author Comment

by:irwinpks
Comment Utility
@mark_wills

you're welcome!  Thanks for being patient....much appreciated.  No problem on the other question...you kinda worked hard on this one.

irwinpks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now