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
Could you please help me with the query
I want inset data from table1 to table2 both tables are same but data types are differnt how do i do this?
i want to create a trigger when an article is inserted into database then give permission/access to a testuser
I add two columns in my existing table(table A) with ten columns but now I am wondering how can I insert values into these new columns with insert and select statement as I need to extract values for these two new columns from another table (table B). There are two similar columns between table A and table B.
how to insert data one table to another table
insert into target_table(cols)
select cols from source_table
create a table TB1 with 10 rows of data
create another table TB2 with no rows inserted init..
and to copy the contents of TB1 inti TB2 then use this syntax
” insert into TB2 select * from TB1 “
Don;t forget to specify the column names
insert into TB2(col1,col2,..) select col1,col2,.. from TB1
If I want take backup 3-4 tables using each day ?? can scheduled plan ???
Hi All,
I’ve two tables (table A, table B ) table B is empty with column names,
I need to copy the table a column’s data to table B by one- one column.
I used the command for first time copy the one column data with – Insert into table B (column) =select column from table A
it was successfully copied.
except copied column, all columns were filled with “Null values”
again need to copy the second column from table A to table B
above command is not working as like first time, how I copied
Please help.
Thanks,
Chary K
Insert into B(col1,col2,…)
select col1,col2,… from A
Thank Chary k and Mdhivan
But I do not create any table in server is that possible alternate way ???
Hi Dev,
if i am trying to retrieve one row from on database (DB1 hosted in a server A) and inserting to another database (DB2 hosted in server B). how i can i achieve this through my vb application?
Please help me
thanks
manzoor
i have 2 table let us say customer and queuemail. i have to retrieve all email id of that column from customer table and inserted into queuemail with additional columns as mentioned below. when a user in customer table registered in before today’s date on i.e,[CreatedOnUtc] field then i want to send message to all the users.
Please help me out on this. i dont want gmail pattern answers.
this is for my application itself not globally.
Queuemail table
SELECT TOP 1000 [Id]
,[Priority]
,[From]
,[FromName]
,[To]
,[ToName]
,[CC]
,[Bcc]
,[Subject]
,[Body]
,[CreatedOnUtc]
,[SentTries]
,[SentOnUtc]
,[EmailAccountId]
FROM [QueuedEmail]
customer table
SELECT TOP 1000 [Id]
,[CustomerGuid]
,[Username]
,[Email]
,[Password]
,[PasswordFormatId]
,[PasswordSalt]
,[AdminComment]
,[LanguageId]
,[CurrencyId]
,[AffiliateId]
,[Active]
,[Deleted]
,[IsSystemAccount]
,[SystemName]
,[LastIpAddress]
,[CreatedOnUtc]
,[LastLoginDateUtc]
FROM [Customer]
Grate I stop using cursors for transfer data between 2 tables, and the execution plan is now visible (before the max was 255 records).
Hi Pinal,
Nice article ,, I am struggling with almost same issue .. I need to insert data from different tables into one table and each column in my Table is a grouping of some data (As subqueries doesn’t allow order by clause inside queries ) . can you please suggest some good way to approach this issue.
Looking forward to your reply
Thanks in advance
Roma Mehta
Hi,
I need to compute statistics from two tables and update into the third table.
example:
INSERT INTO table1(date,col1_summ)
SELECT date,sum(table2.col1)+sum(table3.col2) FROM TABLE2,TABLE3
WHERE date=”somedate”;
What is the correct query to do something like this?
Please advise.
how to split the data in raw to colum
Hi, I want to update a database on a remote server based on an insert from a different server
Is this possible, if so what is the code for doing this? Please help…
Hi,
Is there any way for below issue.
I want to insert a row from one table to anther without mentioning columns name having similar schema but for some fields i want to use some local variable.
I want to avoide to mention columns name because table have more than 900 columns
900 columns? Thats too many. Make use of the result of the following query
select column_name+’,’ from information_schema.columns
where table_name=’your table’
I am trying to insert data in a table table1 of data base db1 from table 2 of database db2 . But I find error token line ,tablename=table2
“INSERT INTO db1.table1
(Client, date)
SELECT Cin, Nom
FROM db2.table2”
hi I want to append data from the table enquirymaster2 to enquirymaster1,
I have field companyname, mobile.
I used this but it is deleting pervious data
from enquirymaster1
INSERT INTO test.dbo.EnquiryMaster1
(Company, MobileIT)
SELECT Company, MobileIT
FROM test.dbo.EnquiryMaster2
It will not delete previous data. After inserting to table, do SELECT * FROM table and see if there are data
Sir,
I have 2 servers( ex. Server1 & server2 with same database name). I want to replicate the data from perticular table (ex. table 1 with server1) from server1 to server2(ex. Table1 with server2). Please tell me how to do the same.
Thanks.
Anil
I have a table that contains many columns for quarterly periods beginning in 4Q2009 going out to 4Q2015 – I want to create a user form with a dropdown where a user can select a quarter and the query will return all columns up to the user selected value. Example: the user selects 3Q2012 and the query returns all columns from 4Q2009 – 3Q2012.