Stored Procedure Programming - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure Programming

Author

Topic

BLarche
Starting Member

21 Posts

Posted - 2014-04-03 : 21:26:17

I have a pretty lengthy script that is coded in classic ASP that does the following:

1) Reads a TXT file line by line and stores the data into a temporary SQL DB.
2) Reads a "link" DB table that tells the temporary table what fields to insert the values into into the active table.
3) Converts values from a string to a numeric ID based upon some lookup tables.
4) Inserts those values into the active table.

The temporary table is called tbl_DealerFeed_temp. The active table is called tbl_Vehicles.

There are several fields in both tables. My current ASP script is quite long. I can provided if necessary. It is very slow and it runs hundreds of lines of codes in around 10 different files and needs to process this. I continue to get the following SQL error when processing the files:

Microsoft OLE DB Provider for SQL Server error '80004005'

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

I am hoping that I can write a stored procedure to eliminate the SQL server issue, speed up the processing, and also set this on an automated nightly schedule.

I am completely clueless when it comes to stored procedures, but am knowledgeable in SQL server. Can anyone point me in the right direction or begin to walk me through the steps? I am not asking for anyone to write it for me, but help me navigate through what is needed to be done? Any help is appreciated!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-04 : 08:50:44

You should be able to use BULK INSERT to get the text file into the database table. There are plenty of examples with code both on MSDN and elsewhere on the web. Look for BULK INSERT.

Once you have the file in the database table, simplify the problem to figuring out how you will get one field into the destination table. More likely than not, it will involve a single query where you join the temp table and the link table for inserting/updating the destination table. If you post a simplified example, people would be able to post queries that are useful. In the absence of that, my guess is that you would need something like this:

INSERT INTO destinationTable
(col1, col2)
SELECT
	a.col1,
	b.col2
FROM
	temp_table a
	INNER JOIN link_table b ON
		a.col3 = b.col3;

BLarche
Starting Member

21 Posts

Posted - 2014-04-04 : 09:41:19

Here is the SQL statement I am currently using. There are two lookup tables to link the temp table and the active table.

The first table is tbl_DealerFeed_fields. This table specifies the fields in the active table, what data type they are, and what reference table it needs to look for the unique ID. For instance, field name v_make is specified in the tbl_VehicleMake table. The value from the TXT file for this file will come in as 'FORD'. My code needs to look in the tbl_VehicleMake table to find the unique ID for value 'FORD'. In this instance, it would return the ID of 10 and store that into the active table.

Here is how I am generating my SQL code in ASP to run the necessary SQL statement:

' -- Create SQL Statement --
strSQL = "SELECT dff.df_v_field_id, dff.df_f_field_id, dff.df_f_field_name, dff.df_f_datatype, dff.df_f_table " & _
	"FROM tbl_DealerFeed_fields dff " & _
	"LEFT JOIN tbl_DealerFeed_link dfl ON dff.df_f_id = dfl.df_f_id " & _
	"WHERE dfl.df_d_id = " & df_d_id & " AND dff.df_f_display = 1"
Set objRS = siteConn.Execute(strSQL)
If Not objRS.EOF Then
	Do While Not objRS.EOF
		strFields = strFields & objRS("df_v_field_id") & ", "
		df_f_field_id = objRS("df_f_field_id")
		If objRS("df_f_datatype") = "int" Then
			strValues = strValues & "CAST("
		End If
		If Not CheckBlank(df_f_field_id) Then
			strPrefix = Split(df_f_field_id, "_")(0)
			strPrefix = objRS("df_v_field_id")
			strValues = strValues & strPrefix & "." & df_f_field_id
			strJoin = strJoin & " LEFT JOIN " & objRS("df_f_table") & " " & strPrefix & " ON " & strPrefix & "." & objRS("df_f_field_name") & " = dft.df_t_" & objRS("df_v_field_id")
		Else
			strValues = strValues & "dft.df_t_" & objRS("df_v_field_id")
		End If
		If objRS("df_f_datatype") = "int" Then
			strValues = strValues & " AS INT), "
		Else
			strValues = strValues & ", "
		End If
	objRS.MoveNext
	Loop
End If
objRS.Close
Set objRS = Nothing

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources