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 Pinal,
I have one question in my mind.that select * into will insert data row wise or it will insert data once completely.
Thank you, saved a lot of time – especially i needed the part about inserting when created earlier, thx
Thank you! Your example was just what I needed. Your site is a major resource for me!
HELLO I AM SANJAY AND YOUR SITE IS BES
Thx Pinal
HI I HAVE TW0 EXCELS.. I WANT TO COPY FROM ONE EXCEL TO OTHER WHICH IS EMPTY
cAN YOU HELP ME
You can very well use SSIS file system task for this
Sir i need a solution, there are 3 places where my application is installed(offline).
and in one place internet does not work regularly.
i want to connect database in the evening to sync or update that all day data…///
is there a solution for this?
Read about Replication
Hi All,
Thank you for good article..
In my project I have 3 table in sqlite database (Category,FoodType,SavedD), the first one contain Category_id and CategoryName which show in TableView and the second table (type_id,Category_id,Type,Quantity,NumberCalories) and this data show it in the second TableView, and display the cell data in label in ViewController, how i can save this data in a new table.
How i can save the data display in the labels which depend in Type_id?
I use the below query, but it doesn’t save any data and when i change Type_id to 3 it saving the third information when the Category_id=1.
****const char *statement= “INSERT INTO SavedD (type_meal,Quantity,NumCal) SELECT Type,Quantity,NumberCalories FROM FoodType WHERE Type_id=?”;
can any one help me insert data from one table to another depending on Type_id…
Thanks
I have a query i am trying to import 7 different excel files into 7 different tables, upon there import i need the data from all 7 to be imported into 1 single master table then dropped, my issue is the column headings in the 7 files are all different, yet all headings exist in the master table
In short how do i loop through each table copying the data into the master where the column match, then drop the tables once all data is present in the master table.
good day sir,
it is possible to move the records from 1 table to another in a single query?
Example:
SELECT ID, NAME, BDATE
INTO NEWTABLE
FROM OLDTABLE
//then delete the records in the old table
Perry,
Suggest you try an insert statement instead:
Insert into NewTable (ID, ‘Name’, ‘BDate’)
Select (ID, ‘Name’, ‘BDate’) from OldTable
If the columns are identical and in the same order in both tables, you can skip the column names in your insert statement:
Insert into NewTable
Select * from OldTable
Use the Select Into clause when your new table is not yet created; use the Insert Into clause when you are transfering data between existing tables.
Forgot to remove paratheses on Select statement. Should look like:
Insert into NewTable (ID, Name, BDate)
Select ID, Name, BDate from OldTable
I am trying to insert values into a table in a different database, but I get an error “Incorrect syntax”, I notice that if I take the “database.dbo.” in front of the table it works fine
insert into table (column1, column5, column8) values (‘x’, ‘4’, ‘m’)
but if I add the “database.dbo.” prefix to the table it gives me the error.
insert into database.dbo.table (column1, column5, column8) values (‘x’, ‘4’, ‘m’)
I need to put the database.dbo. in front of it so it finds the table since it is located in a different database from where my store procedure is located.
Do you know if that is because of rights to the table?
Do you know how to avoid that error?
select * into Table_2 from Table_1 where 1=2
This will create a structure of a Table_1 into Table_2 without moving the data.
What do you do with timestamp erorrs? Using * instead of implicit fields to save time.
“Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.”
Muchas gracias amigo.
Yahh!! Really Its working.Thanks
Hi,
i am creating procedure like this:
ALTER PROCEDURE [dbo]. A
@currentDate DATETIME
AS
BEGIN
Delete from TT_21_STOCK_01_STATUS
INSERT INTO TT_21_STOCK_01_STATUS(BinCode, SKUCode, BatchCode, ConfirmedQuantity, UnconfirmedPutaway, UnconfirmedPicked, BlockedQty, STK_Defragmentation_IN, STK_Replenishment_IN,
STK_Aggregation_IN, STK_Optimize_IN, STK_Total_IN, STK_Defragmentation_OUT, STK_Replenishment_OUT, STK_Aggregation_OUT, STK_Optimize_OUT,
STK_Total_OUT)
SELECT BinCode, SKUCode, BatchCode, ConfirmedQuantity, UnconfirmedPutaway, UnconfirmedPicked, BlockedQty, STK_Defragmentation_IN, STK_Replenishment_IN,
STK_Aggregation_IN, STK_Optimize_IN, STK_Total_IN, STK_Defragmentation_OUT, STK_Replenishment_OUT, STK_Aggregation_OUT, STK_Optimize_OUT,
STK_Total_OUT FROM TT_21_STOCK_02_BACKUP where StockBackupDateTime= @currentDate
END
procedure execute correctly but can’t insert record…….
plz Help me
I am trying to copy data from one server’s view to another’s table. I am sure they have the same structure. For security reasons, the servers CAN NOT be linked.
I tried to use MS_Access. I cannot import the data to there and upload to newtable, but cannot because MS_Access limits to 255 columns.
I tried linking the two tables and doing a DoCmd.RunSQL (“INSERT newtable SELECT * FROM oldtable”) and it failed with error 3162 (inserting a null into a non-variant).
Any suggestions? THANKS!
Sir; I’m new to SQL Server so as I type SQL statements that I’ve used on other platforms, I find syntax errors. I google my problem and keep ending up on this web site. Thanks so much from Memphis for all the help you’ve given me!
i have table1(idno, firstname, secondname, lastname, age, address)
insert three records to this table?
create other table2 consist of having columns(age, firstname, ido)
insert into the data of table1 into table2?
quick answer
sir, i want to copy only two column from table1 to table2 and rest all column in table2 i want to filled it dynamically through query.plese help me how can i fetch only two column data from table 1 and insert into table2 along with other data in table 2.i have 5 column in table 1 and 5 column in table 2 in which two columns in both table are same and i want to copy these two columns in to other table.