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 am santhosh,
I have a scenario like this..
I have two tables personal_details and academic_details with same columns S_No attributes and details
Obviously some rows of both the tables match with each other like name,age etc
So i want to run a trigger like whenever an update takes place in academic_details it will make the corresponding updates in personal_details only for the rows which are common.
the problem is that these rows are placed in different positions.
to give an example the record with values 5 as S_No,educational qualification as attribute and +2 as details is placed at 5th position in academic_details where as the same is placed at 11th position in personal_details,still i want a trigger to update the details column in personal_details……
Can anyone help me??
I use this code,
mysql> delimiter //
mysql> create trigger acatoper after update on academic_details for each row
-> begin
-> update personal_details,academic_details set personal_details.details=academic_details.details where personal_details.attributes=acad
emic_details.attributes;
-> end;
-> //
the query is ok…runs fine
but if tryto update the table academic_details,i get the following error..
mysql>delimiter ;
mysql> update academic_details set details=’+1′ where S_No=5;
ERROR 1442 (HY000): Can’t update table ‘academic_details’ in stored function/trigger because it is already used by statement which invoked t
his stored function/trigger.
Hello Sir,
what i need is we are creating two tables in the second table some id field is there particular id can be stored in first table how can it possible
I have a two tables, lets say TableA, TableB
TableA Columns:
TLID,TaskID, AID, Detail
TableB Columns:
TaskID, Title, Subject,AID
I have added a column AID to TableA, And I want to copy data from TableB which has AID, with same TaskID on both tables,
Please give a query…
Thanks.
Update A
set AID=B.AID
from tableA as A inner join tableB as B on A.TaskId=B.TaskID
Hello, i could use some help
I have two tables, Living Room, And Main,
They both have Work_Order as a primary key,
I have built the work_Order in Main to be a Foreign key in Living Room
What i would like to have happen, is when ever a write a new row into Main. I I would like the database to also write a new row in living room, with the same Primary key. (the rest should be null).
Thanks
You can do it via an INSERT trigger on living table
Thankyou very much Sir, this is quite informative.
thanks to this site..it really help me a lot…
hello pinal dave , madhivanan sir
i am happy
i have one table employee from cms company
i want to show 25 rows in the table
then use select top(25) * from employee
if i want to show 15 to 40 rows in the table
then i used that but give me error
plz sir help me this query
You need to use pagination. Refer point 4
thankyou madhivanan sir
you and pinal sir good author of sql server
you have solved my problem
hello sir
i don’t use of procedure and triggers
i have read many articles but nothing
first
view use virtual table means shortuct key
index use arrange the table ato z
update use update the table
but procedure and triggers what use of sql server
i knew that procedure and triggers are very most important role in sql server
plz sir help me about this question pinal sir and madhivanan sir
happy
use this query select * from tablename
How to copy tables into another database by using the IN clause?
Why do you want to use IN clause?
You can just do
Insert into table(cols)
select cols from db..table where …
hello pinal dival and madhivanan sir
i have create table employee
(
emp_id int,
emp_doj datetime
);
i used that
insert into employee values(01,’15-12-2011);
but doj output is different 3637-1
plz sir correct my doj problem
select sum(sal_salary) as total salary from employee
if i want to sum 10 to 50 cell what will be use of this query
how is the use of automatic date come in the doj column
plz sir help me about this query
thankyou sir
happy
You need to use a single quote around a date value
For summation, search for pagination in this site
Also you should express dates in YYYYMMDD format
i want to insert a bulk data to another table, but other fields are same… here the column name ‘task_id’ only is same for both table
i tried like this:
INSERT INTO [dbo].[active_task]
([user_id]
,[active_flag]
,[date_entered]
,[tracking_status]
,[task_id] )
VALUES
(‘1′
,’True’,
SYSDATETIME(),
‘1’
,select task_id from task)
but i cant get the solution, any alternative way?
INSERT INTO [dbo].[active_task]
([user_id]
,[active_flag]
,[date_entered]
,[tracking_status]
,[task_id] )
select
’1′
,’True’,
SYSDATETIME(),
’1′
, task_id from task
Hi All,
Inserting data from one table to another table without duplication.
Everybody asking transfer data from one table to another table without duplication of rows. so here is the example which i ve implemented.
[SSDB].[DBO].[PERSON]=destination table.
[CONFIGDB].[DBO].[CFG_PERSON] =source table.
INSERT INTO [SSDB].[DBO].[PERSON]
(dbid, tenant_dbid, last_name, first_name, address_line1, address_line2,
address_line3, address_line4, address_line5, office, home, mobile, pager, fax, modem,
phones_comment, birthdate, comment_, employee_id, user_name, password, is_agent,
is_admin, state, csid, tenant_csid, place_dbid, place_csid, capacity_dbid, site_dbid,
contract_dbid)
SELECT *
FROM [CONFIGDB].[DBO].[CFG_PERSON]
WHERE NOT EXISTS(SELECT *
FROM [ssdb].[dbo].[PERSON]
WHERE ([CONFIGDB].[DBO].[CFG_PERSON].[DBID] = [ssdb].[dbo].[PERSON].[DBID]
)
)
thanks.
varun Gaur
hello pinal dival and madhivanan sir
i used that single quote but error has come(doj datetime)
create table record
(
emp_code int,
emp_doj datetime,
emp_city varchar(60),
emp_age int
);
insert into record values (001,’1582011′,’faridabad’,25);
error
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
how to come dafault datetime come in the emp_doj column
plz give me example
2 i have search that pagination but noting else
plz sir i want to sum only for 15 cell to 40 cell in sal_salary column
You should always express date in YYYYMMDD format
insert into record values (001,’20110815′,’faridabad’,25);
Also read this post to know why you should use YYYYMMDD format
Does it make sense to reference a temp table with dbo.#temp_table?
Yes there is no problem with it
Is my query correct.. Its not working.. please help..
insert into [TblPlayer] (OldId,RegNo,FirstName,MiddleName,LastName,FathersName,DateOfBirth,Category,Phone,Address,
Village,CurrentClubRegistrationDate)
select pid,pid,fname,mname,lname,fathers_name,category,dob,category,contact,address,village,dor
from [players]
What did you mean by it is not working? Did you get any error message?
i m getting the following error:
The data was truncated while converting from one data type to another
hello pinal dave and madhivanan sir
my sql server set up is not working properly
i want to free full download sql server 2005 version
plz sir give me site of sql server 2005 version
sir its very urgent
plz sir help me
can easily find out the link in microsoft site. Have you tried it?
hello madhivanan sir
i have download sql servre 2005 express edition but management studio has not come,i have no idea which part download of sql server, i have tried that but sql server setup has not come, plz sir which part download of sql server setup include management studio, sorry sir i am irritating you, plz sir help me about this question
happy
You can download it via
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017
INSERT INTO new_table (Foo, Bar, Fizz, Buzz)
SELECT Foo, Bar, Fizz, Buzz
FROM initial_table