have you ever had a database table that you needed to clone? you can use any number of convoluted methods such as: manually coping and pasting rows in the database manager, running lots of queries/stored procedures, or writing a script to cycle though each row copying data from one table to the next. all of these methods are inferior and far more complex then the method im going to show you.

introducing the select into query...
this method should be used if the table you want to clone exists, but the new table douse not. the select into query will create the new table for you, copying all the column names, properties, and data. if you want to make an exact clone of the table you can select * into the new table. but if you only want a few specific columns, you can declare only the ones you want. the syntax is elegantly simple...

SQL procedure

SELECT *
INTO [new-table]
FROM [old-table]

this is a great solution for cloning a table. it's fast and lightweight since its all executed on the database end, not straddled between middleware.

now here's another situation...
let's say we have a simple web-form that is submitting 4 pieces of data: username, date, value1, and value2. this form has been collecting data for a long time, and all of a sudden you realize that somehow your save procedure has swapped the data for value1 and value2! some people might just dive into the table and start modifying rows. but im paranoid. i like to validate my SQL procedures before i destroy an entire table's worth of data. we can use the same clone table technique above to make a copy of the table, but we can go a step further and also swap the data in value1 and value2 in the same procedure.

SQL procedure

SELECT *
INTO [new-table]
FROM [old-table]
   
DECLARE @temp AS decimal
UPDATE [new-table]
SET @temp=value1, value1=value2, value2=@temp

basically we have 2 SQL statements in one here. the first will clone old-table into new-table. the second declares a temporary variable and uses the temp var to switch the data in value1 and value2 in the new-table.

if you only need to switch values for a smaller portion of the dataset, say all rows with an id value less than a certain number, you can simply add a where clause to the end of your statement.

SQL procedure

SELECT *
INTO [new-table]
FROM [old-table]
   
DECLARE @temp AS decimal
UPDATE [new-table]
SET @temp=value1, value1=value2, value2=@temp
WHERE id < 806

this will copy the entire table into the new one, but only swap the values for ids less than 806. all other values will be cloned exactly.

the world of database programming can be a dark and treacherous journey. when you first set out, your quest seems manageable. but as time progresses your start to realize you need more queries, more tables, etc, etc, to achieve your goal. well today im going teach you a little trick that might help you need one less stored procedure then you thought...

sometimes its true, you actually do need two queries to get the job done. but other times you can circumvent this by using something like a "join" or "inner join" query to merge two tables together and pull your results from both.

but still, sometimes this isnt enough...

so lets say you need to first get an automatically generated ID number from a table and you need two parameters to get this data. then you need a subsequent query to update that record.

historically, i probally would have said that you need to run two separate SQL procedures to achieve this goal. but if i would say that now, id be a liar...

take a look at this:

SQL stored procedure

CREATE PROCEDURE [dbo].[TESTING-UPDATE]
   (@who            [nvarchar](50),
    @which          [nvarchar](50)) 
 
AS UPDATE [dbo].[table-name]
SET someValue = 'new value'
FROM (
        SELECT TOP 1 (autoid) as nuID 
        FROM [dbo].[table-name]
        WHERE name = @user AND num = @which
        ORDER BY autoid DESC
      ) as tested
WHERE autoid = tested.nuID
GO

as you can see, im actually running two queries in one!
the traditional FROM [table-name] statement has been replaced by an "inline SQL query". what happens is the database will see the FROM () parenthetical notation, and immediately execute the syntax between them. that statement then returns its result set into a named object (in this case its called "tested"). then the rest of the procedure will execute as normal, but with the additional data we got from our inline query.

this technique can be extremely powerful! i just learned about it this week and i have already removed 10 now unnecessary SQL queries from the database for a project at work. so keep that one in you SQL lexicon for later ;D

visual studio 2005 has a tool for connecting to a database called a "sqlDataSource". while this tool works, i find myself wanting to create the connection and build or execute stored procedures within my own C# code. this tutorial will show you how to connect to a sql database 3 different ways in asp.net

the first thing is creating a connection with your database. if you understand how connection strings are built, write your own other wise will can use the visual studio database tool to do that for us.

drag a new sqlDataSource onto the canvas, highlight it, then click the little arrow that appears to reveal configure datasource. in the windows that follow you can create a new database connection (or use an existing one). refresh the server list and select your database server (or localhost). then chose if you want to use windows integrated security (where the server is the database user) or sql server security (where you supply the sql username and password). then select the database you want to connect to on the server, and test your connection.

press ok, and you will return to the previous window, click the little plus sign to expand and show you the connection string that visual studio generated for you. copy it, and click cancel. now you can delete the sqlDataSource and switch to code view.

now the first thing we need to do is create a database connection in our code

asp.net c# code

SqlConnection con = new SqlConnection("Data Source=DEVELOPMENT;Initial Catalog=rss;Integrated Security=True");
 

hardcoded procedure

asp.net c# code

int id = 13;
con.Open();
//-create procedure
string select = "SELECT * FROM 'table' WHERE 'id' = @ID"; 
SqlCommand command = new SqlCommand(select, Con); 
//-paramerter adding
command.Parameters.Add("@ID", SqlDbType.Int).Value = id;
//-make a data reader
SqlDataReader reader = command.ExecuteReader(); 
while (reader.Read()) 
{ 
   String data = reader["thing"].ToString();
}
//-close connection
con.Close();

using a stored procedure with one parameter
(ie: "DELETE 'tabelname' WHERE 'id' = @ID; ")

asp.net c# code

int id = 42;
con.Open();
//-setup query
SqlCommand commando = new SqlCommand("deleteUser", con);
commando.CommandType = CommandType.StoredProcedure;
//-paramerter adding
commando.Parameters.Add("@ID", SqlDbType.Int).Value = id;
//-execute the query
commando.ExecuteNonQuery();
con.Close();

using a stored procedure without any parameters
(ie: "SELECT * FROM 'tabelname' ;")

asp.net c# code

con.Open();
//-select a stored procedure
SqlCommand commander = new SqlCommand("getFilename", con);
commander.CommandType = CommandType.StoredProcedure;
//-make a data reader
SqlDataReader SQLreader;
SQLreader = commander.ExecuteReader();
while (SQLreader.Read())
{
   //-select data by array index
   String data  = SQLreader[0].ToString();
   //-select data by sql collumn name
   String data2 = SQLreader["name"].ToString();
}
//-close connections
SQLreader.Close();
con.Close();

here's a simple SQL script that returns the last row created. this is great for when you add a new record that has an autogenerated id and you need that number.

sql server

CREATE PROCEDURE [dbo].[getLastLogin]
AS
SELECT TOP 1 *
FROM dbo.login
ORDER BY id DESC
GO

or if your an open source guy

mySql

SELECT * FROM `login` 
ORDER BY `id` DESC
LIMIT 1,1;