Following three questions are many times asked on this blog.
How to insert data from one table to another table efficiently?
How to insert data from one table using where condition to another table?
How can I stop using cursor to move data from one table to another table?
There are two different ways to implement inserting data from one table to another table. I strongly suggest to use either of the methods over the cursor. Performance of following two methods is far superior over the cursor. I prefer to use Method 1 always as I works in all the cases.
Method 1 : INSERT INTO SELECT
This method is used when the table is already created in the database earlier and the data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are not required to list them. I always list them for readability and scalability purpose.
USE AdventureWorks GO ----Create testable CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100)) ----INSERT INTO TestTable using SELECT INSERT INTO TestTable (FirstName, LastName) SELECT FirstName, LastName FROM Person.Contact WHERE EmailPromotion = 2 ----Verify that Data in TestTable SELECT FirstName, LastName FROM TestTable ----Clean Up Database DROP TABLE TestTable GO
Method 2 : SELECT INTO
This method is used when the table is not created earlier and needs to be created when data from one table is to be inserted into the newly created table from another table. The new table is created with the same data types as selected columns.
USE AdventureWorks GO ----Create a new table and insert into table using SELECT INSERT SELECT FirstName, LastName INTO TestTable FROM Person.Contact WHERE EmailPromotion = 2 ----Verify that Data in TestTable SELECT FirstName, LastName FROM TestTable ----Clean Up Database DROP TABLE TestTable GO
Both of the above method works with database temporary tables (global, local). If you want to insert multiple rows using only one insert statement refer article SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL.
Reference: Pinal Dave (https://blog.sqlauthority.com)
677 Comments. Leave new
I think i need to do
1)set identity_insert requests_archive on
2) insert data
3) set identity_insert requests_archive off
i guess its correct?
If you want indentity column values, you can do that. Otherwise just exclude the identity column in the column list
Hi,
I have to write one procedure to insert records from table on one server DB(DEV server) to table with same name and structure on another server DB(TEST server).
for eg. there is a table named employee on DEV server and TEST server both. and i have made some enteries in employee table on DEV server and now using procedure just want to move some particular records to employee table on TEST server DB.
Please help me out
thanks,
Ritu
to insert data from one table to another table in Oracle.
INSERT INTO TABLE1 (SELECT * FROM TABLE2)
You dont need a brace
Hi.
Pinel I have a problem my problem is that I have two servers one is local server and another is Linked server.I send a table from local to linked server with same columns,Local server(parent) has a primary key with Identity column now I created triggers for Insert and Update and reflect data on linked server.Now I want that I will also created trigger for Insert and Update but in opposite direction.
that is
A->B and
B->A
my problem is that when I fired trigger then there is message of Primary key violation how can i fixed this problem.plz send me info of bidirection trigger or any plan on my EMAILID
and my work is on sql server 2000.
hi.
I want to move a specific data from one table to another
I have the same problem to get new data from database and insert it into another database (same server) I just want the most effective way to transfare the new inserted data and make check upon it before add to my database.
please advice
To Mona,
/*Inserting data from one database to another database.*/
use Database1
select*into Database2.Dbo.Table2 from Table1
select*from Database2.Dbo.Table2
— Here is :
Database1:- is a database in which a table named ‘Table1’.
Dbo:- default schema for all the databases.
Table1:- a table in Database1
Database2:- a new database
Table2 :- a new table in Database2
— This query will insert the data from Database1 ‘s table called ‘Table1’ to another database ‘Database2’ ‘s table i.e. in Table2.
Hope it will helpfull.
Thank you
I’m really greatful to this great blog
Dear sir
I have one full/incri .bak file form nrega offline sql server 2005 server.
how it is combile/merge in my nrega offline sql server 2005 same database without any data lose.
The problem are that.
In my block 54 village. block user work on 38 village data enty and i work on 16 village date entry.
how i combine my backup .bak file in block user data. without any lose.
good morning sir
this query is very good
i want to ask a one question in this query
(1) two tables employee or salary. totoal row in employee 300
no column or no row in salary
i used that select * into salary from employee
(2) i have added 100 rows in employee
i want a that only 300 to 400 rows come in salary
not to 1 to 300 rows
i used that many query but give me some erro
plz sir help me about this query
If you have identity column/unique column in the table you can make use of that
sorry sir i don’t know happy about your answer
Do you have an identity or unique column in the table?
good morning sir
i have no use identity or unique column in table but i use primary key on (sal_id)
i don’t about use of identity or unique
thankyou sir
Is primary key an incremental number for each insert?
Which version of SQL Server are you using?
I have get new data from other database and want insert it into my database
(same server) what is most effective way to transfare the new data in my database.
without my data lose. please advice
Do you want transfer only one table or many tables?
Hello sir,
I want Transfer all tabel.
Actually a have a full backup of other end server.
I am Want Joint that data in my Data.
good morning sir
i have table employee in cms database
column name
employee-code
11-234-45645
11-567-13678
11-269-09456
11-256-08654
11-450-24654
total column 400
output only 3 to 8 no come means 234-45
567-13
269-09
plz sir send me answer this question
thankyou sir
What is the logic used behind the result?
hello sir
sir i want a result in 3 to 8 no come .if value are string
used select substring(column_name,3,8) value wii be come
3 no between 8 no come in this query
This is not clear. Can you post some sample data with expected result?
Hi! Newbie here, but this blog is very helpful. Have to dbs on remote server, and decided to test procedure before going live:
1. Copy tableA to db2 from db1
Used following successfully:
SELECT * INTO trsql_TEST.dbo.Products FROM trsql.dbo.Products
2. Copy duplicate table with new name to db2 from db1
SELECT * INTO trsql_TEST.dbo.Products_2 FROM trsql.dbo.Products
So now I have two tables (Products & Products_2) with identical structure in the same db (trsql_TEST).
3. Now I want to copy a field to Products from Products_2… I tried UNSUCCESSFULLY the following:
INSERT INTO Products (stock) SELECT stock FROM Products_2
The result is that the stock field (column) is copied successfully, but ALL OTHER DATA IS NOW NULL !!!
Help? Oh – Im using Studio Express and MS SQL 2005 dbs
TIA – eric
OOPS – Just realized that 3. above (INSERT INTO Products (stock) SELECT stock FROM Products_2) is actually appending data –
It doubles the # or rows, which are all NULL except the “stock” column, which it is picking up from the “Products_2” table. The original rows are still there, unaltered.
Hi
i m asking one question that i have a grid view with bubble event like check box .and i want to retrieve all the records on the another page when i checck box and if i select more than one check box then all the name of we want display in the dropdown list another page Please solve my problem imidiatly.
I m waiting!!!!!
Thanks
You need to send required values as parameters to the stored procedure. Show us the current code you are using
i want u help me about increase automatic 00000001
Keep an identity column of INT datatype and do the formation at front end application
Hi All
I would like to write a trigger on insert or update
so it will write the recored from the table in the MS SQL database to the table in Oracle
can some one help me in that
the trigger must be written inthe MS SQl database and it should write the record to the Oracle table
Regards
This is very bad design. Why do you want to do this via a trigger?
ok how do i do it if
so it will write the recored from the table in the MS SQL database to another the table in a differnt database in MSSQL on the same server
Regards
I tried this
CREATE TRIGGER test ON TA_PUNCHES FOR INSERT,UPDATE
as
begin
Insert into TestPunch.TA_PUNCHES select * from inserted ins
end
I have 6 table
table 1 (1id, 1Name)
table 2(2id, 2Name)
table 3 (3id,3Name)
table4(4id,4Name)
table 5 (1Name, 2Name, 3Name, 4Name)
now i want table6 to be
table6(1id,2id,3id,4id) how can i do that
can anyone please help me with this
Join all the tables and take relevent ids from each table