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 have a table of 16 column in sql that i am uploading excel file in using asp.net . all the column contains value either ‘yes’ or ‘no’.
i need to calculate the percentage of ‘yes’ in each column and i need to transfer the calculated percentage value to another table .
and one more thing , that percentage will be calculated using the “group by” clause .
please help me..
Hello Madhu,
i have created one test table say ‘test’ and i have inserted some records into it. Following is just sample of data that i have created..
ID IsActive IsVisible
1 yes yes
2 no yes
3 yes no
4 no no
5 yes no
6 yes yes
7 no no
8 yes no
9 yes no
10 yes yes
Now write query as-
SELECT IsActive,CONVERT(VARCHAR(5),(COUNT(IsActive)*100)/(SELECT COUNT(ID) FROM test))+’%’ AS PercentageWiseYes_No FROM test
GROUP BY IsActive
After executing this query i am getting result as follows-
IsActive PercentageWiseYes_N0
no 30%
yes 70%
I think your requirement was like this.
I am also new in MS-SQL Server but i think this query helps you.
Thanks.
Mandar Kavishwar.
Sir ur Code is very Effective but I want to transfer the data from one column of one table to another column of another table in existing database.
kindly Please Reply
You need update statement
update t1
set t1.col1=t2.col1
from table1 as t1 inner join table2 as t2
on t1.keycol=t2.keycol
Sir ur Code is very Effective but I want to transfer the data from one column of one table to another column of another table in existing database. without making any temporarily table
kindly Please Reply
Hi.
I have 3 tables that I’m working with. I need to update all records in Table1 with the value from a similarly named column in Table3. However, I need to use a value from a column in Table2 to get to the right record in Table3.
The common column in Table1 and Table2 is recid.
The common column in Table2 and Table3 is VendorSKU.
Finally, I need to delete all the records from Table3
Will this work correctly in a Stored Procedure?
UPDATE Table1 SET Table1.Cost = Table3.Cost
FROM Table3 c, Table2 p
INNER JOIN Table1 m ON m.recid = p.recid
INNER JOIN Table2 ON p.VendorSKU = c.VendorSKU
DELETE FROM Table3
Thanks,
Alan
I have 2 table in sql server freetrial and freetrial_backup, freetrial table contain startdate and enddate when enddate is over it should transfer all data to freetrial_backup table pls help me(startdate is registrationdate and enddate is 2days after registrationdate)
You need to make use of a job to do this
Hi Sharad,
If this is the case that you want to copy one column from one table to another table, then you can directly write an update statement, if there is one-to-one relation between these tables. Else you need to write custom logic, based on requirement.
You can use UPDATE in this case.
Thanks,
Tejas
hi,
I need to insert the data from table1 to table to.
Table1 has a primary key, and which is a foreign key in the table2.
I want to retrieve the data of primary key from table1 and insert that to table2.
Also table2 has a independent columns , so I need to insert the all columns.
Kindly please reply,
sradha.
Hi,
I don’t quite follow. If you need just some value from table1 then just fetch it make insert to table2. For example:
declare @id int
select @id = id from table1 where …
insert into table2 select @id, other, values, …
Better run that inside a transaction though.
But I think you need to elaborate your question a bit since I don’t see what could be the problem here.
DUDE! This query Rocks! It modified the column names for my use and it worked.
Thanks again
Hi Dave,
I want to import data from one main table say ‘A’ to some 200 tables with some condition like if part_no=1 then it will go to part1 table.
I am not interest to write insert query 200 times.
Plz help me on this.
Debasish
Run this
copy the result
Run them
select 'insert into part'+cast(number as varchar(10))+' select * from where part_no=A'+cast(number as varchar(10))
from master..spt_values
where type='p' and number between 1 and 200
Hello,
I have a small question.I have 2 tables.One is a main table and the other is a temp table.The main table is a table which gets populated from the data in the temp table.(This is done on a daily basis).
If we perform a select operation on Main table it lists all the data since years.
I need help with 2 things.
1) I just need to get data from today(I can set this query to system data I think).
2) I need to make sure that data in Main table is not duplicated.(That is main table should not have duplicated rows).
Two points can be seen as
insert into main_table(columns)
select columns from temp_table
where
date_col>=dateadd(day,datediff(day,0,getdate()),0)
and
date_col<dateadd(day,datediff(day,0,getdate())+1,0)
I want to insert one database comlete data into other databse ex. test.district into finla.district
What i can do
insert into finla..district(columns)
select columns from test..district
You are fabulous…I have always got what I was looking for here
hi,
can any one help me pl!!
in sql 2005.i have created a table with a column name as rid,total,cur_value and balance.
In balance column, this total value minus(-) cur_value is stored.(eg.10000-100=900).
900 is stored in balance column.
1)now,after each transaction i wanna store this balance value into the total column again automatically…how to do it (eg.now automatically this 900 must go to total column again)?
2) how to get a particular value in a table using aggregate as condition ?(e.g select name from table where MAX(id))
3)is it possible to raise alarm or alert msg in sql server 2005 ???
i have created a table in sql 2000 and i have transfered few
rows into it from other table with in the same database,now i want to push few more rows into the same table from diffirent database, is it possible in sql 2000?
You can use three part names
dbname.ownername.objectname
Are there any limitations for amounts of data that can be copied using or methods with Simple recovery mode? If copied table has 300GB of BLOBs can we still use option to copy all data to another table or we should copy by portions?
I think that we need to create table with name TestTable as shown below in Methode 2. Right?
Method 2 : SELECT INTO
This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.
USE AdventureWorks
GO
—-Create new table and insert into table using SELECT
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
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
i have created two table tab1 with colomn name call_no, title, author_name, edition and second table tab2 colomn call_no, user_name, title, author_name. i want to insert title and author_name from tab1 to tab2. how can do it?
Insert into table2( title ,author_name )
select title ,author_name from table1
I want to fetch the unique record from a table. I can use distict but it return records with only single column. Requirement is that all column’s values will come. I have three column emp_id, name and user_id.
Any help will be appreciable.
Thank you.
You need to post some sample data with expected result to help you
hi
itry to insert content of one table to another but my query has error
my query is below:
and error is :
i know that tables have connected toghether
but can any man help to solve this prob????
please i need!!!!!
What was the error you got?