How to write a stored procedure that use a temp table created from c#

Question

I have an application developped in C# that follows this worflow:

Read CSV files
Create a temp table
Send the CSV data to the temp table with SqlBulkCopy
Call a stored procedure to MERGE the temp table in the real table

Delete the temp table

using (var dbConnection = new SqlConnection(_connectionString))
{
    dbConnection.Open();

    using (var cmd = new SqlCommand("CREATE TABLE [#UpdatedData]([Path] [nvarchar](50))", dbConnection))
    {
        cmd.ExecuteNonQuery();
    }

    using (var bulkCopy = new SqlBulkCopy(dbConnection))
    {
        bulkCopy.DestinationTableName = "[#UpdatedData]";

        bulkCopy.WriteToServer(dt);
    }

    using (var cmd = new SqlCommand("usp_MergeData", dbConnection))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.ExecuteNonQuery();
    }

    using (var cmd = new SqlCommand("DROP TABLE [#UpdatedData]", dbConnection))
    {
        cmd.ExecuteNonQuery();
    }
}

My problem is I'm creating the temp table in C#, and I can't write the SQL Server stored procedure because it doesn't find the temp table. Is there any workaround?

I'm using localdb for dev and SQL Azure for production.

Why not just create a table and then simply truncate it when you are done. Sometimes the simple solution is the best. — Mark Kram, Apr 12 '13 at 11:36
It would be helpful if you explain why you need to use a temp table. The logic you described is very common in the ETL area, but there's no need to use a temp table, just use a permanent one. — Pondlife, Apr 12 '13 at 14:28
@MarkKram could you please give an example, I'm interested in your solution! thanks — JuChom, Apr 12 '13 at 14:36
@Pondlife I have to merge big CSV files in my database and EF cannot handle it, so I'm looking to do it with SQL Server directly — JuChom, Apr 12 '13 at 14:38
SQL Azure [supports](http://msdn.microsoft.com/en-us/library/windowsazure/ee621784.aspx) bcp and SSIS, so those could both be options for what you want to do. And it's always best to describe your real problem rather than your solution. In this case, a better question would be "How can I load large .csv files into SQL Azure?" See also [this question](http://stackoverflow.com/questions/6352660/bulk-load-files-into-sql-azure). — Pondlife, Apr 12 '13 at 14:49
Just create a table before hand and then execute a TRUNCATE TABLE — Mark Kram, Apr 12 '13 at 14:52

qujck · Answer 1 · 2013-04-12T10:26:47.230

1

You can create a global temp table:

using (var cmd = new SqlCommand(
    "CREATE TABLE [##UpdatedData]([Path] [nvarchar](50))"
    , dbConnection))

Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).

Edit

As you cannot have global temp tables in SQL Azure you'll probably have to create a real table.

edited Apr 12 '13 at 10:26

answered Apr 12 '13 at 10:03

qujck

14,388
4
45
74

Unfortunately global temp tables are not available in SQL Azure. Thanks anyway :) – JuChom Apr 12 '13 at 10:09
@Swell oh well (you learn something new every day) ... I guess you'll have to go with a permanent table – qujck Apr 12 '13 at 10:24

score 0 · Accepted Answer · answered Apr 12 '13 at 14:33

0

I finally made it without permanent table or global table!

Instead of trying to create a MERGE stored procedure in SQL Server, I wrote a big MERGE function in a string in C# and execute it as normal SQL Query!

answered Apr 12 '13 at 14:33

JuChom

5,717
5
45
78

How to write a stored procedure that use a temp table created from c#

2 Answers2