Copying One Table To Another
Want to copy all the data including the structure (with the exceptions of the constraints) to a new database?
Insert Into NewDatabaseName
Select * From OldDatabaseName
Want to just copy the structure without the rows?
Insert Into NewDatabaseName
Select * From OldDatabaseName
Where 1 = 2
What could be easier.
Unions
Unions are pretty cool. That said if you're using text, ntext, or image types you need to use a Union All. If you don't you'll get an ugly error like this:
Server: Msg 8163, Level 16, State 4, Line 1
The text, ntext, or image data type cannot be selected as DISTINCT.
Stored Procedure Output Parameters
If you're running a stored procedure that returns a data set you'll need to call SqlCommand.ExecuteReader(). If you also have an output parameter specified in the procedure you might be tempted to do something like this:
sqlDataReader = command.ExecuteReader();
string outputParam = command.Parameters["foo"].Value;
while (reader.Read()) {
//read your rows
}
reader.Close();
command.Connection.Close();
The above code will compile fine but you'll get strange exceptions on the line where you're reading out the param value. If you investigate you'll find that even though command.Parameters["foo"] isn't null command.Parameters["foo"].Value is. That's because when you're using a sql reader you need to close the reader before you can read the output param(s):
sqlDataReader = command.ExecuteReader();
while (reader.Read()) {
//read your rows
}
reader.Close();
string outputParam = command.Parameters["foo"].Value;
command.Connection.Close();
Now you know. And knowing is half the battle.
