SQL select into statement
code :: copy and swap database tables in MSSQL
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 i'm 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...
SELECT * INTO [new-table] FROM [old-table]
this is a great solution for cloning a table. it's fast and lightweight since it's 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 i'm 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.
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.
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.