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 
).
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.
T-SQL:
|
1 2 3 |
SELECT * INTO Sales.CustomerBackup FROM Sales.Customer; |
PL/SQL:
|
1 2 3 |
CREATE TABLE CustomerBackup AS SELECT * FROM Customer; |
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… )
T-SQL:
|
1 2 |
INSERT INTO Sales.CustomerBackup (PersonID, StoreID, ModifiedDate, AccountNumber, rowguid) SELECT PersonID, StoreID, ModifiedDate, AccountNumber, rowguid FROM Sales.Customer; |
PL/SQL:
|
1 2 |
INSERT INTO CustomerBackup SELECT * FROM Customer; |
About Carlos Ferreira
i have 33 tables for 1 day for example c1_00,c1_01,…
i must find all from all_tables then create temp_table and insert them to new table
please help me
Hi Mahboobe-
Thanks for visiting! Its tough to give direct advice without the specifics but I hope the the following will help:
CREATE TABLE #test
(
col1 varchar(max)
)
exec sp_MSforeachtable
@command1 = ‘INSERT INTO #test select col1 from ?’,
@Whereand = ‘ and o.name like ”c1_%”’,
@postcommand = ‘INSERT into result (col1) SELECT col1 from #test; drop table #test’
Just to explain this a bit further: The sp_Msforeachtable procedure will iterate thru all the tables in the current database and dynamically build an sql statement that is listed in @command1. It will substitute the table name where you will see ‘?’. As it is iterating over the tables, it is extracting the data from each table and inserting it into a single temporary table. The @whereand provides a filter on the table names that this command is executed on. In this case is is filtering the tables to the names that look like ‘c1_’. Finally when everything is completed it executes the command in the postcommand variable. It will in essence copy the data from the temporary table into the final resulting table and then drop the temporary table.
I hope this helps!
Carlos
i want to insert my data in sql server with plsql ……..kindly give me some idea how i insert plsql code running on
table name is data_table
.
Declare
std_id char:=’4′;
std_name varchar2(200):=’Smith’;
std_location varchar2(200):=’England’;
begin
insert into data_table(data_id,data_name,data_location) values (std_id,std_name,std_location)
end;
Hi Muhammad – try something like this:
CREATE TABLE data_table(
std_id char NOT NULL,
std_name varchar2(200) NOT NULL,
std_location varchar2(200) NOT NULL
);
insert into data_table(std_id, std_name, std_location) values (‘4’, ‘Smith’, ‘England’);
select * from data_table