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 Dave,
Is is possible to insert data from a table to another, where these two tables are located in different databases, and databases are located in different servers?
basically inserting from one server to another.
source table is in a remote server and destination table is in the local server.
also I connect to these 2 servers using different connections.
1 Use linked server
2 Use four part name in the query
insert into table(col)list)
select col_list from server.db.owner.table
Hi I’ve one table called HISTORY. This table is heavily used in production environment. Now i Want to create another table of same structure. Also i want to move data from this table to another created table. also created table must be updated by HISTORY.
plz help!!!!!!!!
Its a kind of Archiving of table.
1 Create a new table
2 Insert into new_table(col)list)
select col_list from history
creating a table is not a problem………. moving data dynamically is required.
I ll really appreciate if u can help out that…. let me give u one more info that..this table(history) contains more than 5 lakh rows and constantly increasing.
Scenario-
History more than 5 lakh record(rows) and increasing.
now i want to move data from history table to New_histroy table….and truncating the data from history @ weekly basis.
so whatever data come to history table will be moved to new table @ sunday 00:00 am. deleting the data which is moved from history table.
History table gets data 24/7.
You can create a stored procedure that copies data from history to other table. Schedule it as a job so that it will run periodically
that is what i want…….c whenever record reaches more than 400000 move the data into another table.
if (select count(*) from table) >400000
–your insert statement
@varun.
5 Lakhs Records is very normal. But size of the table for holding those 5 Lakhs Record matters…
Anyways,
You already finalized that you want to go with Archiving ???
Table Partition would be a good option.
Archiving Solution,
Even after you acrhive History Table, you need to update Indexes. Do you re-organize or rebuild Indexes on that table. Because lot of inserts and delete creates lot of fragmentation.
Depending on what locks you are using when inserting data into table, you will be able to delete data from history table after archiving.
Look at this sample Archiving procedure below
I am not clear if I answered your questions, in case not, please ask your question clearly.
~ IM.
@Imran Mohammed
this is good…thanks……… scenario is like this………..
I’ve table called HISTORY…..it has more than 500000 rows….. what i want is whenever rows reaches more than 500000 …..move 250000 rows to ARCHIVED_HISTORY. Also delete the data from HISTORY table which has moved.
Moving of rows shud be LIFO.
Environment is Production Database.
Please send the procedure.
PLZ help.
@ Imran Mohammad
I have two Tables Subject and subjectDim(SubID,SubName, Level,Total Marks) i have to insert data into SubjectDim where SubID is an auto number and subName will be selected from Subject and Level is ‘HSSC1’ and TotalMarks are 100.
I need Urgent help plz reply me soon how to write this Query.
Thanks in Advance
i have two tables: ITEM_HISTORY and ITEM
columns in ITEM_HISTORY : ITEM_ID, SOURCE_ID, TIME_STAMP. etc.
ITEM columns: ITEM_ID, MODEL_ID, LAST_UPDATE, LOCATION
LOCATION from ITEM is same as SOURCE_ID from ITEM_HISTORY
I need to get the ITEM_IDs for a SOURCE_ID for a time interval of like every few days..and i need to get the records inserted in a new table called PH_ABC with ITEM_ID, PART_ID, TIME_STAMP,YEAR_CODE, JULIAN, LAST_UPDATE
here, i cant loop the items and i am getting only the last item from the whole set of items. and i dont want to get the duplicates.
ALTER PROCEDURE [dbo].[insertitems](@PSourceID varchar(100),@PStartDate datetime,@PItemID varchar(100) out)
AS
BEGIN
DECLARE @VItemID varchar(100),@VTimeStamp datetime,@VJulian varchar(5),@VJulian1 varchar(100), @VYearPart int, @VDayPart int, @VZeroDate datetime, @VDate datetime,
@VStartDate datetime, @VEndDate datetime
SET @VStartDate = @PStartDate
SET @VEndDate = DATEADD(dd,5,@VStartDate)
SELECT @VTimeStamp = TIME_STAMP
FROM ITEM_HISTORY
WHERE SOURCE_ID = @PSourceID
IF((CONVERT(varchar(10),@VTimeStamp,120)>= @VStartDate) AND (CONVERT(varchar(10),@VTimeStamp,120) <= @VEndDate))
BEGIN
SELECT @VItemID = ITEM_ID
FROM ITEM_HISTORY
WHERE SOURCE_ID = @PSourceID AND
TIME_STAMP = @VTimeStamp
PRINT 'ITEM' + ':' + @VItemID
INSERT INTO PH_ABC(ITEM_ID,TIME_STAMP)
VALUES(@VItemID, @VTimeStamp)
SET @VJulian = SUBSTRING(@VItemID,3,5)
SET @VJulian1 = '20' + @VJulian
SET @VYearPart = CAST(LEFT(@VJulian1,4) AS int)
SET @VDayPart = CAST(RIGHT(@VJulian1,3) AS int)
SET @VZeroDate = DATEADD(yy,-1 * DATEDIFF(yy,0,GETDATE()),GETDATE())
SET @VDate = DATEADD(dd,@VDayPart – 1, DATEADD(yy,@VYearPart-YEAR(@VZeroDate),0))
PRINT 'Date' + ':' + CAST(@Vdate AS Varchar(100))
DECLARE @VModelID varchar(100), @VLastUpdate datetime
SELECT @VModelID = MODEL_ID,
@VLastUpdate = LAST_UPDATE
FROM ITEM
WHERE ITEM_ID = @VItemID AND
LOCATION = @PSourceID
PRINT @VModelID
UPDATE PH_ABC
SET PART_ID = @VModelID,
JULIAN = @VJulian,
YEAR_CODE = @VYearPart,
LAST_UPDATE = @VLastUpdate
WHERE ITEM_ID = @VItemID AND
TIME_STAMP = @VTimeStamp
END
ELSE
PRINT 'No items with that location'
END
please help me with this….i have been trying for the last 5 days…
Hi,
Please I have a table with and identity column. When I try to insert 5,000 records from another table with the Insert Into and select statement. It takes forever to finish.
But when i create a new table without any identity column, this same script doesn’t take a minute to finish.
Please can you help me
Can you post the code that has this problem?
hi,
i have two datagridview which is dgv1 and dgv2.
how can i insert row from dgv1 into dgv2. both of them is data bound.
let say, i have two table in database, beverage1 and beverage2. beverage1 consist of attributes of ID, Name and Price.it also has the data in it.
in beverage2 consist the same attributes but no data in it.
In dgv1 will display the beverage1. hence, how should i click entire one row and insert into dgv2.
hi,
i have two datagridview which is dgv1 and dgv2.
how can i insert row from dgv1 into dgv2. both of them is data bound.
let say, i have two table in database, beverage1 and beverage2. beverage1 consist of attributes of ID, Name and Price.it also has the data in it.
in beverage2 consist the same attributes but no data in it.
In dgv1 will display the beverage1. hence, how should i click entire one row and insert into dgv2.
Hi Anne,
You can create a collection of datarows from the first grid [from these datarows you can fetch row specific attribute values], then you may add a ‘row click’ funtionality to your grid on which you may add each row instance to the second grid also you can update database from there.
Hope this gives you an idea to solve your problem.
If you have any further queries please tell me.
Regards,
Suvradeep Banerjee
thanks,
its really useful for me.
i have two tables: table1 and table2
copy one table to another existing table
INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1
Hello i have a request for the gurus
i want to insert rows into a table where data are coming from 2 other tables
Table1
DB_id, DB_name, owner
Table2
server_id, server_name, sql_instance
Table3
server_id, DB_Name, DB_id
I want to insert data into table3 from data Table1 and table2
how can i achieve this ??
Thanks for all your help
Hi
How to use index while executing select query
I have an xml file. I read the nodes of XML file into cte. My problem is that i need to load this data into an exisiting table and there is a stored procedure which i have to use to get the id value from its output parameter.
Do i have to loop the cte to insert the record or is there any direct way to insert the cte data into table ?
Thank you!!
Hi
I have one table it is having only one colun length is 120 char. the data is like this
column name ID
d1012052011B10005280915A10004380920A10004360920
in the above data :-
d10 is code
12052011 is date
B100 is type
0528 is empcode
0915 is time
A100 is type
0438 is empcode
0920 is time
a100 is type
0436 is empcode
0920 time
like the above id fields is having length of 120 chara.
so, I want tha data is:-
empcode,date,type,time
0528 12/05/2011 B100 0915
0438 12/05/2011 A100 0920
0436 12/05/2011 A100 0920
so, pl help with sql query
Ashok
Hi All,
I have a scenario where i need to create a table using only the key columns [some specific columns] of another table. Is it possible?
Any help would be appreciated.
Thnaks in advance
You can create a view using the specific columns
i have a table with no keys. the table size is about 40 million. i get a record which first needs to be checked for duplicate values in the table (agaisnt 4 of 10 columns) and if there are no duplicates, then i need to insert the value into the table.
it take a large time if i use where clause or if exists etc. what alternate ways are there to check duplicates before inserting
thanks
@Balu.
Don;t event think of a trigger. That will kill your performance.
1. I would suggest you use a unique Constraint on the table. That would be the quickest way.
2. You can use Check Constraint with a combination of Scalar function.
This Scalar Function should accept column value as input and check for uniqueness in that table, and return either 1 or 0. This returned value will then be checked by Check Constraint defined on that column before inserting record in the table.
Do you have Clustered Index on this table by any chance ?
~ IM.
Thank you. The table is indexed . Point is, composite keys are not feasible due to the some limitations and any other combinations i am trying , the result is crossing 8 to 10 seconds which is not allowed in the system. I was thinking about hash tables?
If TestTable is exist already with 0-row or record if you want to insert all records from Contact table then you have to check both the table structure (Number of column and associated datatypes) & finally you can use the below query to insert rows from source table to destination table without mentioning the field or column name.
INSERT INTO TestTable
SELECT *
FROM Person.Contact
WHERE EmailPromotion = 2
If any of the table set the replication mode then you have to mention the column name because at the you should not use keyword “*”, below query will help you.
INSERT INTO TestTable (F1,F2)
SELECT F1,F2
FROM Person.Contact
WHERE EmailPromotion = 2
Thanks,
S.Venkatesh
hello pinal and madhivaan sir,
my name is happy
sir i knew sql server,ms-Access i have exp in only for sql in windows xp
but i have changed my company/ company use windows 7. sql server not downloading of windows 7/ i dont have use of oracle (database) plz sir help me about this topic
sir i have many querires, backup point,csv into sql server, fuction from our website
plz give me some oracle site when i learn oracle 10 g,9g, because company use oracle or DB2 universal database, sir plz help me about this topic
thankyou sir
hello sir,
i want to transfer my data from one server to another server.
is this possible. if is this possible then please give me solution how to transfer data from one server table to another server
thanks
Jonish Aggarwal
Thank a lot… this article