How can I SELECT INTO with SqlCommand? (with casting, rounding, and isnull)

Question

I wrote this SQL statement and tested it with SQL Server Management Studio. However I'm not sure how to do this with C#. I have tried with SqlCommand.ExecuteNonQuery(); and it didn't work. What am I missing?

C#:

string query = "SELECT ISNULL(CAST(CustId AS INT), 0) AS AccountNumber, 
                       ISNULL(CAST(CompanyName AS NVARCHAR),'') AS Company,
                       ISNULL(CAST(CustName AS NVARCHAR),'') AS FirstName, 
                       ISNULL(CAST(LastName AS NVARCHAR),'') AS LastName, 
                       ISNULL(CAST(Email AS NVARCHAR),'') AS EmailAddress,  
                       ISNULL(CAST(ROUND(CustLoyaltyPoints,0),0) AS NVARCHAR) AS CustomText1 
                INTO db2.dbo.CustomerTemp 
                FROM db1.dbo.Customer;";

connection = new SqlConnection(strConnect); //connection already defined above
SqlCommand command = new SqlCommand(query, connection); 

command.ExecuteNonQuery();

There is a [question/anweres here you could take a look](http://stackoverflow.com/questions/1449646/how-can-i-execute-a-sql-from-c) — bonCodigo, Nov 12 '12 at 20:14
Are you getting an error? Are you connecting with different credentials in your app than SSMS? The credentials you are using for you app, does it have permission to your 2nd DB? More info = More and Better Help. We aren't psychics =P — TyCobb, Nov 12 '12 at 20:15
my login credentials are the same for both databases however my connection string is set for db1. @bonCodigo I checked that out earlier, but I suppose I'll have another look — Dave_R, Nov 12 '12 at 20:22
@Sohail is correct. You forgot to open the connection looking at your code. This should cause exceptions to be thrown. You aren't blindly catching and ignoring exceptions around this piece of code are you? — TyCobb, Nov 12 '12 at 20:31
This only works if the target table doesn't exist yet - could that be the problem? If the target table `db2.dbo.CustomerTemp` already exist - it will not work - not even in SQL Server Mgmt Studio — marc_s, Nov 12 '12 at 20:44

score 0 · Answer 1 · answered Nov 12 '12 at 20:46

You need to add this line before you ExecuteNonQuery:

connection.Open();

So the code should look like:

connection = new SqlConnection(strConnect); //connection already defined above
connection.Open();

SqlCommand command = new SqlCommand(query, connection); 
command.ExecuteNonQuery();

If you're still getting errors, post the specific exception & message that's getting raised. HTH...

How can I SELECT INTO with SqlCommand? (with casting, rounding, and isnull)

1 Answers1