Creating temporary SQL tables

[BryanM67]

I currently use SQL 2000 Server (MSDE version) with my Visual Basic.Net application. Every database is predefined.

Is it possible to create a "temporary" SQL table at run-time, fill it with records, report on it using Crystal Reports, then delete the table? If so, what code would I need to create the table?

I appreciate any suggestions on this.

 

[DavidT_macktool]

Yes it is possible.

Here's the order I have used sucessfully. I start by dropping a dataadapter on the form. I set it up with a table that is near the design I want for the temporary dataset. If my temp dataset has a lot of "Time" fields in it I set up the dataadapter with the Time File. Then I create the dataset and name it 'TEMPSomething'. This creates most of the sql code for add, update and delete. Then I Update the schema to include all the fields I want in the TEMP dataset (update the SQL statements to match.) The select Statement is not important since you will not be loading the dataset from the database.

In a public variables MODULE:

Public tblTempDailyTimeTotals As DataTable

Load up a form with all the data you want to include in the TEMP dataset; I load up hidden datagrids for each table so I can loop through them and get all the data at once. You could be getting data from several different tables.

set the public datatable to the TEMPSomething dataset

tblTempDailyTimeTotals = DsTEMPSomething1.Tables("Time")
tblTempDailyTimeTotals .Clear()

Add data to the TEMP dataset

Dim drCurrentTEMP As DataRow
drCurrentTEMP = tblTempDailyTimeTotals .NewRow()
drCurrentTEMP ("Employee") = EmployeeGrid.Columns("Employee").Value
drCurrentTEMP ("Hours") = TimeGrid.Columns("TotHours").Value
drCurrentTEMP ("Date") = TimeGrid.Columns("Date").Value
drCurrentTEMP ("Date Added") = dt
drCurrentTEMP ("Cost") = 0.0
tblTempDailyTimeTotals .Rows.Add(drCurrentTC) 'Add the new Row

When you are done you will not Update the dataset to the SQLServer. The data will only reside in the Temp DataTable.

Create your Crystal report using the TEMPDataset in Project Data and use this code on the Crystal Reports Viewer Form.

Dim DLReport As New rptDirectLabor
DLReport.SetDataSource(tblTempDailyTimeTotals )
CrystalReportViewer1.ReportSource = DLReport
CrystalReportViewer1.Refresh()

That should be enough to get you started, Ask specific questions if you have problems.

 

[TechGnome]

Over kill.

Since you've already stated that you are working with SQL Server, I can answer that simply, "Yes."

There are two ways to do it. One is to use actual temp tables, and the other is using temp table variables.

How to use temp tables:

CREATE TABLE #tmpMyTable (Field1 int NOT NULL,
  Field2 varchar(50) NULL,
  Field3 datetime NULL,
...
  FieldN int NULL)

Not that different from a normal CREATE TABLE, the difference is the # infront of the table name. That's what makes it a temp table. But there's a catch. Because this method actualy creates a table in the tempdb database, only one can exist at a time. That means two people can't run the same query at the same time (or at least as long as the temp table is in existance - which is based on the connection, or if you call a DROP TABLE.)

Good news there's a solution to this. The answer is to use table variables.

DECLARE @tmpMyTable TABLE (Field1 int NOT NULL,
  Field2 varchar(50) NULL,
  Field3 datetime NULL,
...
  FieldN int NULL)

Now the cool thing about table variables is that they are memory only, so they are fast. The side effect of this is that you don't have collision with other users. And you don't need to DROP TABLE it. The moment the SP is done, just like any other variable, it goes away.

No matter which method you use, you can insert into them, select from them, update them, just like they were any normal, ordinary table in the database.

Tg