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
Hi,
I want to insert data from one server database table
to another server database table in vb6 code
any one can help me
I want to insert data from one server database table
to another server database table in oracle.
pls help me…
pease help me.
i have imported text file file into SQl db, i wan to change the impoted tbale to exixting table. how i can do that
Hi
How can i export data from one table to another table in different database using SQL query. I nead to use the query in VB6.0
Thanks
hi everyone
in seq server 2005 there is a feature of link
server by which u can communicate two diffrent server
and their database and more, ur querying one another server database using the same qury window
Helowww,
I want to insert from one table to another, create an EDM table, which is denormalized fact table, ETL from OLTP…
However this source table consists 30millions rows and still have to join with 6 other tables (also thousand to millions)…
Today this query is done in 10 hours, which is highly unacceptable, how can I solve this???
I’m thinking about devide-n-conquer where each record given an identity, and insert records in 10.000 records/iteration???
Any idea???
And I also have already use indexes…
hi,
I have to copy 2 colums,which are from 2 different tables, in to another table .when I am trying insert into,it is giving error.
Msg 1038, Level 15, State 5, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or [] are not allowed. Add a name or single space as the alias name.
can anyone help me in this.
My query is
select registration.customerid,vendoroffer.offerid,getdate() Into offer_assignment From Registration,vendoroffer where (select dbo.f_AgeCalc(dateofbirth, GetDate()) as age)Between 0 and 6 And zipcode=98052 And gender=’Female’ And vendoroffer.customerid=registration.customerid
hey I got it.Thanks for the post.
Hi
Can we insert data from one table to other which is not the same database?
Am trying to insert data from one table to another, but query is giving error :
INSERT INTO WC_WCLI_RETAIN
(STOP_ORDER_NO, STOP_CRD, Phone_Number, order_type, ORD_RCVD_DT, LINE_TYPE, ROBOT_STATUS)
SELECT ORDER_NO, CUST_REQD_DT, NATIONAL_NUMBER, ORD_CRT_TYPE, ORD_RCVD_DT, ‘C’, ‘RECEIVED’
FROM dbo.HADES_CPS HC
WHERE [PRODUCT_CODE] = ‘A72911’ AND [ORD_CRT_TYPE] = ‘S’ AND [ORD_RCVD_DT] =
(SELECT CONVERT(VARCHAR(10), GETDATE(), 111))) AND ORDER_NO NOT EXISTS
(SELECT 1
FROM WC_WCLI_RETAIN WCR
WHERE WCR.STOP_ORDER_NO = HC.ORDER_NO)
Help !!!!
hi,
i want check duplicate data while insert into sql table
Can any one help me how to do that
Hi GC (51),
What is the error you are getting?? Would help to solve your problem.
hi,
I have to copy the same data to two different tables. Is it possible to do this with only 1 query? or does it have to be 2?
The thing is the database will have to function afterwards for someone who barely uses pc’s. So the messages that pop up must stay at a minimum. I can’t program with VBA so cancelling the messages out by those means is out of the question for me.
P.S: I am using access 2007
Hi,
I have used INSERT INTO TABLE1….SELECT COLA, COLB.. FROM TABLE2 , in a procedure. The Problem is it doesn’t insert all the selected records in TABLE1. eg. If it selects 20 records it may insert all 20 sometimes, sometimes may insert 11 records or 9 records (less no. of records). Pls. suggest.
Thanks
kiran.
Dave,
I am really struggling and my last foray into SQL was 9 years ago. I am trying to run the following on SQL Server 2005
INSERT INTO dbo.sim_control_table (customer_name, sage_customer_number, date_of_sale,
customer_buy_price, customer_commission_due)
SELECT customer_name, sage_customer_number, date_of_sale,
customer_buy_price, customer_commission_due
FROM dbo.excalibur_20080131
WHERE dbo.excalibur_20080131.sim_number = dbo.sim_control_table.sim_number
In order to keep it simple I have used the same column names in both tables and I am looking to update from multiple rows to multiple rows. When I then Execute the query I get the following error message:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier “dbo.sim_control_test_table.sim_number” could not be bound.
Nice forum. thanks.
I am inserting data from one table to another new table using Select into. Can you tell me, how can i copy over the defaults and indexez and constraints along with this.
i want to copy data from one column to another column inside a table.anyone can help me?
jewel
Can you tell me how to use the INSERT INTO IN clause to transfer data from a table in MS SQL Server database into a table in a oracle database?
@ JEWEL
This is what I know…. please correct me if I am wrong.
1. You cannot work on only one column in case of insert and delete. When ever you try to insert a row will be effected .. in no case you can work on only one column. and copy is a insert statement. So that is not possible.
2. You can UPDATE one column, other columns will not be effected when you try to update one particular column. Since copy is not a update statement its a insert statement you cannot work on only one column.
If you want to have the same data in two columns in a table, for sure you can do that with out manually copying it.
This is what I did in the example:
CREATE TABLE UEXAMPLE ( EID INT, EID2 AS EID )
-> I created a table named Uexample and then I create a column EID which is int Datatype and I created one more computed column .. its not an alias because I did not give any datatype for second column, for aliases you still have to give the data type. so my second column is computed column which is exactly the fist column. I used computed column because you cannot have two columns with same name in one table.
INSERT INTO UEXAMPLE ( EID ) VALUES (2)
-> Simple insert statement.
SELECT * FROM UEXAMPLE
-> Simple select statement.
hope this works.