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
Can you tell me how to use the INSERT INTO IN clause to transfer data from a table in MS SQLServer DB into a table in a MS Access DB?
I have 2 tables viz… Table_1 and Table_2. In Table_2, I created a instead of trigger that modifies the data and then inserts it into the table. But when I try to copy data from Table_1 to Table_2 using the above method, only the last record is copied and the Trigger is also applied to it. Can you please tell me the reason for this?
Are you sure?
Post the code used in the trigger
thanks
You need to use OPENROWSET function
Refer SQL Server help file for the sample code
How to use the Insert into select along with direct data coming from the form in SQl Server 2005?
i cant understood what u asked that question “direct data” means
I can’t understand what is “i cant understood”
on March 12, 2010 at 10:27 am | Reply chaitanya kumar svs
i cant understood
I can understand that instead of helping other people you are just picking on some stupid grammar thing. Grow up, it is fine as long as people understand what the other person means.
thanks for your posting its really helpful do u know how to transfer a db table (with data) from one database to another in different server?
u can use open rowset query to transfer ond db to other
Hi Dave,
I’m trying to move data between tables of 2 different dB’s on same machine (SQL Server2005).
Exactly trying to move Col1, col2 of Table1 of db1 to Col3 and Col4 of Table2 of db2.
Is it possible only by writing SQL scripts, if so can suggests me some links regarding this?
thx.
By moving, did you mean updating the table2?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger railways_result_trigger on railways_result
after insert
as
if exists(select distinct update_date from railways_result where update_date!=(SELECT CONVERT(date, getdate (), 103)))
begin
delete from current_railways_result
delete from railways_result
end
else
begin
delete from current_railways_result
insert into current_railways_result select *,dense_rank()OVER (PARTITION BY stu_batch_time,exam_date ORDER BY stu_total_marks DESC)stu_rank
FROM railways_result
insert into railways_result_backup select *,dense_rank()OVER (PARTITION BY stu_batch_time,exam_date ORDER BY stu_total_marks DESC)stu_rank
FROM railways_result
;with DelDup as (select row_number() over (partition by
stu_name, stu_roll,exam_date,stu_rank,stu_batch_time order by stu_name,stu_roll,exam_date,stu_batch_time) as RowNo from railways_result_backup)
Delete from DelDup where RowNo> 1
end
first time data not inserted into first table plz help me
This may be the culprit if exists(select distinct update_date from railways_result where update_date!=(SELECT CONVERT(date, getdate (), 103)))
Dont use convert function
my requirement is delete both table data before insert plz help me i need in project sir.
i m creating trigger
Hi devan
select * into Northwind.dbo.employee from pubs.dbo.employee
I think this would work for u
thx,
Karthi.
thankyou for this query
thank you for your querry
legend, already had the table dbo.tblStock in the “new database, simply had to delete that, then run the above query. :)
Hi Karthi,
Thanks , this worked for me.
Hello sir, I want the producure to transfer the data from one to another……….
Plz send the query
Regrads
Sriram
Sriram,
INSERT INTO database1.dbo.table (column1, column2)
SELECT column1, column2 FROM database2.dbo.table
David
wat would be the query if i want to transfer data from one to another n also add some independent values in my other columns…
i.e if i have two tables A and B , say A wid 4 columns and B with 6 columns, transferrin values from 4 columns in A to B… and i want to insert some values into other 3 columns in B…
where is the problem adding the independent values to the select-part?
INSERT INTO database1.dbo.table (column1, column2, column3, column4)
SELECT column1, column2, 1, ‘abc’ FROM database2.dbo.table
this query fails to transfer data from one table to another table across different databases…
Thx.
This code works for me
Hi dave,
thanks for posting such wonderful posts.
I’m trying to do a recursive loop searching for a particular parent in a tree, and retrieve all children under it.
Problem: While Exists(Select deptid from global_Master_Directory Where parentid IN (@ParentID) ) not being looped.
Please kindly guide me on this. thanks again.
Stored Procedure:
CREATE PROCEDURE procRetrieveDivUsers
( @divisionId varchar(10))
AS
— Create a variable table. A temporary table starts with #
declare @TableVar table
(
tempPK int identity(1,1),
costcenter varchar(6) NOT NULL ,
deptid varchar(6)
)
— declare and Initalize the variables
Declare @ParentID VARCHAR(1024), @deptID varchar(1024), @costcenter varchar(1024)
SET @ParentID = @divisionId
–Loop through – problem occurs here.. no looping occured. I tried printing out @parentID, it does return –value but when not doing looping. the while loop does work if i do a hard code value replacing @parentID
While Exists(Select deptid from global_Master_Directory Where parentid IN (@ParentID) )
Begin
INSERT INTO @TableVar (costcenter, deptid)
( SELECT costcenter,deptid FROM global_Master_Directory Where ParentID IN (@ParentID) )
SELECT @deptID =COALESCE(@deptID + ‘,’, ”) + deptid FROM global_Master_Directory Where ParentID IN (@ParentID)
SELECT @ParentID = @deptID –SELECT
End
SELECT distinct COALESCE(@costcenter + ‘,’, ”) + costcenter FROM @TableVar
GO
Sir i have problem in mysql ………. I have 2 tables socialnet (firstname, lastname) and other is socialreg (age, status) ………. now i want to copy whole Firstname coulmn from socialnet to socialreg………………………PLZ tell me the query
How To Transfer The Data from One Table To Another Table…………
Insert Into TableName1(columnname1,columnname2…)
Select columnname1,columnname2… from TableName2
hi Dave,
How to retrieve data from a table by row number of that table ?
(addittional information: suppose there is a table, and you don’t know any information except the attribute of that particular table, you get a request that you have to retrieve data of 31st no. row.)
You need to apply pagination technique using row_number() function. Refer this post for more informations
Dave,
What is the easiest way to insert data from one table where the data in the first table doesn’t exist in the second?
Table1 Table2
a a
b b
c
Example: In the above table I want to insert ONLY the row containing the value c into table2.
Thanks in advance for your help!
select distinct * into Table2 from Table1
This is not correct
Read the question again
The correct answer is
insert into table2(col)
select col from table1 as t1 where not exists
(select * from table2 where col=t2.col)
hi…..
actually i hv 2 tables….frst table is hvng oly one column and the second table is hvng mre than one column…bt i need to transfer data frm frst table to only one field in second table…hw cn i achieve tat???plz help me out in this…
Thanks,
Paul (9): I did it like this:
INSERT INTO MRA..T_MRA_MBR (MBR_ID, NCPDP_PRVDR_NUM)
SELECT MBR_ID, NCPDP_PRVDR_NUM
FROM P350..T_AH_MBR
WHERE MBR_ACTV_IND = ‘Y’ AND MBR_ID NOT IN (SELECT MBR_ID FROM MRA..T_MRA_MBR)
‘MRA’ is the destination DB, and I only want to insert new records that entered the P350 DB as a result of the previous day’s INSERTS. MBR_ID is the primary key in both. MBR_ACTV_IND is a WHERE clause that’s only pertinent to my code – yours will assuredly be different.
select distinct * into Table2 from Table1
Thanks SHEEBA
Its useful for me.
Hello,
How Can I copy my local sql server 2005 table’s data to my host database?
Thanks
Take a backup and restore it over host server
hello
i want to insert only the time into my table. or else how can i differentiate date and time. here i have field logout_time, its data type is date time. here i have to check if the column is empty i have to raise a exception. because while inserting first into the table the logout_time field inserting as empty string, later it will be updated. but its taking the default system date. i don’t want that i want the entire column should be empty. please do help me
Does the column has default constraint? If so remove it
Hi man
I need help, I’ve been working on this for a weeks now. I have created a Job in SQL Server Agent. The job is is executing fine but the my SQL Statement in the Command block is not correct.
I’m using SQL Server 2005, I need to import an Access Database to SQL server automatically every morning, and Delete the old data before Inserting new data.
Can you please help me with the SQL statement to do this job.
Ndindi
Hi, i have code of following:
drop database if exists company;
drop table if exists location;
drop table if exists country;
drop table if exists region;
create database if not exists company;
use company;
/*
create the table named region
*/
create table if not exists region
(
region_id varchar(20) not null ,
region_name varchar(32) not null,
constraint region_PK primary key (region_id)
) ;
/*
create the table named country
*/
create table if not exists country
(
country_id int(20) not null,
country_name varchar(32) not null,
region_id varchar(20) not null ,
constraint country_id_PK primary key (country_id),
constraint region_id_fk foreign key (region_id)
references region(region_id)
) ;
Now i want to use command insert into to add values into tables. But I dont know how to do to add values foreign key region_id in country table which can link wich data from table region. Anyone can help me? Thanks you so much.
note: If you can show me your work clearly. Thanks
Note that this site is for MS SQL Server
Are you using it?
hi pinal
i just want to know the command
what is command to populate data from one table to another table
pls help me out
Regards
Dhirendra
mumbai
Insert into table1(col_list)
select col_list from table_2
where some_condition
i want to import the data into a table from other table which is in different Database please help me out
Use three part name
Insert into table1(col_list)
select col_list from db_name.dbo.table_2
where some_condition
very bad answer dont give this type of answers.
Why do you think it is a bad answer?
Insert into table1(col_list)
select col_list from db_name.dbo.table_2
where some_condition
My question If Col_list is differred from the other table.
How to populate?
Please help..
You can join the respective tables and take needed columns
select * into newdatabasename.dbo.newtablename from olddatabasename.dbo.oldtablename
on November 19, 2007 at 2:33 pm16 surender
i want to import the data into a table from other table which is in different Database please help me out
SELECT * INTO rejeesh.dbo.authors FROM pubs.dbo.authors
Hi ,
You can use the Data Transformation services(DTS) to import the data from one table to another table!
use import/export wizard and create a package to do so.
Hi,
I want to insert data from one server database table
to another server database table in vb6 code
any one can help me
You need to make use of Linked Server
Read about sp_addLinkedserver in SQL Server help file
hi deven,
i have same issue as u have disscussed here.
but i already have the SP for the selection of data.
now i have to insert that same data in to table which i m getting through tht SP.
so can i pass this sp to the insert query as you have passed select query to the insert query.
i m getting cofused in it.
Please reply me.
its urgent.
Waiting for your reply.
Thanking You.
Regards,
jigar
Insert into table(col_list)
EXEC proc