Insert table data from .NET dataset into SAS data file via OLEDB

Question

I have C# .NET code with a DataSet (System.Data.DataSet) containing one table of data, and I'm trying to create a new SAS data table on a SAS server and then push the data into it using C#.

Right now, I can create the new table on the SAS server, but I'm not sure how to transfer the data. I tried using this insert command (exportData is the DataSet):

"INSERT INTO SASUSER." + SAStableName + " SELECT * FROM " + exportData.Tables[0].TableName

But it thinks that the exportData table name is supposed to be a SAS library name. All I get when searching the web is examples that only take data out of SAS, which is easy with a TableDirect command type and the OLEDB fill method. It seems to be not so easy to reverse the process.

Note: I'm using the sas.IOMProvider provider in my connectionstring.

Thanks

EDIT: After all the searching I've been doing, I assume you can't really do this. I tried using SQL parameterization, but the SAS provider doesn't support that. I finally decided to use a simple for loop to add the values since all my source and target tables contain the same columns. Semi-pseudo code:

DateTime time = (System.DateTime)exportTable.Rows[i]["time"];
object value = exportTable.Rows[i]["value"];
"INSERT INTO SASUSER." + tableName + " (time, value) VALUES (" + time.Ticks + " , " + value + ")"

I'm still having trouble with loading dates into a new SAS table using an SQL INSERT clause (see code above). Not sure how they're supposed to be formatted. The time values in the source table (exportTable) are DateTime objects, so the cast is valid.

Consider listing the columns on your target table. Don't use *, but rather an explicit list of columns on your source table. i.e. INSERT INTO MyTable (Col1, Col2) SELECT ColA, ColB FROM OtherTable; — p.campbell
@p.campbell I've actually done this in my updated workaround code (see edit), but the problem is that when I try to specify the .NET DataSet data in the SQL clause, it thinks the DataSet is a table and throws an error. — Clayton

Chains Chains · Accepted Answer · 2011-08-02T18:36:35

Instead of trying to do it as an INSERT from the dataset, can you approach it from the opposite direction do it as an EXPORT to to the table (SASUSER.tablename)?

http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a003102702.htm

Insert table data from .NET dataset into SAS data file via OLEDB

1 Answers