One of the reasons why I started this blog was so I could gather my code stuff in one place. I have been using Google Docs (now Google Drive) for them but where’s the fun in that?
They are a lot easier to share using a blog, right?
So here goes one coding tip.
On some occasions I’m working on a new feature or change that needs some demo data. Some data that is as close to the current version of the live data as possible.
The difficult way of achieving that would be to script up the old table, create a new table using that script but with a different name, and then insert a lot of data into it through some, for sake of demonstration, ridiculously difficult process.
Or we could use SELECT INTO.
By using SELECT INTO we both create a table and populate it with data in one fell swoop.
|
1 2 3 4 |
-- We can select all the columns of a table into the new table: SELECT * INTO [DatabaseName].[SchemaName].[NewTableName] FROM old_tablename; |
As stated before what this does is create a new table with columns identical to the columns of the old table and populate the new table with all the data from the old table.
Neat huh?
The next one is too:
|
1 2 3 4 |
-- We can also select only the columns we want into the new table: SELECT column_name(s) INTO [DatabaseName].[SchemaName].[NewTableName] FROM old_tablename |
Same trick as before but now you’re selecting which columns to create in the new table along with copying the data that belongs to those columns.
I should warn you:
This script doesn’t copy over any indexes (indices) or triggers or any complimacated doowackies. Only data.
My database engine of choice is Microsoft’s SQL Server which you can get here for the nifty price of free (the express version that is).
I haven’t tested this script on any other database server and I don’t know if it’s ANSI compliant. 🙂
Fair enough?
Enjoy