I have recently started on a new project. The goal is to insert some data generated in Excel into a table in a SQL database. Once that is done, I need a function that, based on an input of mine, returns some of that data to excel.
My only problem is that I have no experience with SQL. Well, first things first. Getting the data into the database.
I have created a table in a SQL database. I contains of 651 columns. The first one is named 'stat' and is the primary key. The remaining columns are named 'T1', 'T2', 'T3' ... 'T650'. It holds about 240 rows.
I can upload data from excel with the following:
Sub SetLevel(stat As String, value1 As Double, value2 As Double, value3 As Double)
' Dim rst As ADODB.Recordset
Dim rst As Object
Set rst = CreateObject("ADODB.Recordset")
Dim Cmd As New ADODB.Command
.ActiveConnection = oConn 'is set before sub is called
.CommandText = "dbo.SetLevels"
.CommandType = adCmdStoredProc
.Parameters("@stat") = stat
.Parameters("@level1") = value1
.Parameters("@level2") = value2
.Parameters("@level3") = value3
Set rst = .Execute
ALTER PROCEDURE [dbo].[SetLevels]
SET NOCOUNT ON;
SET T1 = @level1,
T2 = @level2,
T3 = @level3
WHERE stat = @stat
It works great for the first three columns, and surely I could just add another 648 input variables to the stored procedure and it would work just as well. I'm guessing however, that there is a much easier solution to the problem. I just don't know of it, and the examples found with google has not pushed me in the right direction.
Anyone able to help?