Insert data into new (or existing table) from another (T-SQL, PL/SQL, SQL Server, Oracle)

Often times we wish to copy data from one table into another.  The methods of doing this where the new table does not exist will be slightly different from the case in which it does exist.  Also I will show display the method of doing this in T-SQL and PL/SQL (just because I get confused all the time when I switch databases Smile ).

SQL copy tables to new table

When performing this command, a table will created using the same data types as the columns which as selected in the select statement.  This is usually very useful when making a copy of the existing table before making any alterations to the data.




SQL copy tables to existing table

These following when executed will insert data into already existing table.  It will match fields based on name if you choose to list the fields in the select clause.  This normally useful for when you need to reinsert data (don’t ask why… Smile)



T-SQL – Search Stored Procedure Code for Text

Often I have to search for stored procedures in SQL Server to find pieces of text.   The motivation for this is to find procedures which use a table or to look for code which the developer has done silly things like hardcoded directories (Boris….).  Luckly you can query sys.procedures to generally find the information that you need.  Below is an example of query that searches for stored procedures for the word ‘c:\*’.


Technet – sys.procedures

How to get the column names of a table in SQL Server (T-SQL)

The following query can be run (substitute the ‘TableName’ in the query with the table name you want column information on) to get the column names for any table in T-SQL.

To get information on all the columns for all tables in a database use the following query:

SQL Server also provides a stored procedure (‘sp_help’) which is able to display information on any object listed in sysobjects.   To use this to get information on a table execute the following:

%d bloggers like this: