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
How to copy data from one column to another column in same table but it should update automatic using time of our system.
hello sir my name is happy.
Q1. how to create some table backup in sql server 2005
becuase i knew that if full backup use database right click tasks and go for backup, translog backup
Q2. what is use of stroed procedure in sql server. what is main differnce between view and stored proceduure and fuctions
plz sir help me about question
i want to move xml to sqldatabase they move propely bt every time they fetch the old data also..i want to insert only updated value everytime nt the old 1’s
/My Code is here
string xmlfile = Server.MapPath(“~/ResumeFolder/Export1.xml”);
try
{
using (SqlConnection con = new SqlConnection())
{
con.Open();
using (DataSet dsTemp = new DataSet())
{
dsTemp.ReadXml(xmlfile);
using (DataTable dt =dsTemp.Tables[0])
{
using (SqlBulkCopy sb = new SqlBulkCopy(con))
{
sb.BatchSize = 50;
sb.DestinationTableName = “TBQUANTITY”;
sb.ColumnMappings.Add(“Id”, “Id”);
sb.ColumnMappings.Add(“ProductName”, “ProductName”);
sb.ColumnMappings.Add(“CategoryName”, “CategoryName”);
sb.ColumnMappings.Add(“Country”, “Country”);
sb.ColumnMappings.Add(“OrderDate”, “OrderDate”);
sb.ColumnMappings.Add(“OrderTime”, “OrderTime”);
sb.ColumnMappings.Add(“UnitPrice”, “UnitPrice”);
sb.ColumnMappings.Add(“Quantity”, “Quantity”);
sb.WriteToServer(dt);
}
}
}
}
}
catch
{
}
Hi there very nice website!! Man .. Excellent .. Wonderful .. I will bookmark your blog and take the feeds also?I am glad to find so many helpful information here in the post, we want develop more techniques on this regard, thank you for sharing. . . . . .
select * into TABLE2 from TABLE1
Hi ,Can anybody know how to compute minimum value in a cube.As while cube uploads it data, it loads zeros at the places of Null …I want to calculate minimum value excluding these zeros. Thanks
Thanks..It worked..
i want to insert a vedio mp4 format into database(sqlserver) any one help me for this
Hi everyone.
I have two tables ,
table1 and table2
suppose table 1 contains emp id proj id and table 2 contains emp id,project id and updated date.the updated date of 2nd table will only change when there is change in project_id on 1st table.
Kindly help
Can anyone tell me how can i insert value from one table to another with other values like….
@status int
insert into Table2(FirstName,LastName,Status)
select FirstName,LastName from Table1,@status
I don’t know exact syntax kindly help me…
Can Anyone Help Me…?
In my problem data of one column in a table has to be moved to another column of same table depending on the time. Means for every day at 12:00Am data of column1 has to be moved to column2.
Hi, i am using sql for databse and C# in visual studio……i want to update table1 which contains 4 columns(eg Name, CNIC, Attendance, Date) i want to select Name and CNIC culimns’ value from table2 and and want to enter values of attendance and date my self….please tell me how should i do this?
i have try this..
INSERT INTO Dailyreport (Name,CNIC,Attendance,Date) VALUES (SELECT Name,CNIC FROM Registration,(‘Present’, ‘” + date + “‘)) WHERE CNIC=” + n.ToString();
but this does not work……….
please help me………
Incorrect syntax near ‘,’.
this is the error (exception) of the above post …….
hello sir,
i have a doubt regarding inserting records into two tables and my doubt is that …….
let us consider the following two tables,
the first table is “pradeep” …
SQL>select * from pradeep;
SNO NAME PHNO
— ——————– ———-
1 pradeep 1
2 prasad 2
3 lavanya 3
4 tulasi 4
5 ravi 5
my second table is … “prasad” …
SQL> desc prasad
Name Null? Type
—————————————– ——– —————————-
SNO NUMBER
NAME VARCHAR2(20)
PHNO NUMBER(10)
ADDRESS VARCHAR2(20)
my condition and doubt is that ………….. in the above mentioned tables, the two columns i.e., SNO,NAME (COMMON COLUMNS IN BOTH TABLES),HERE I WOULD LIKE TO COPY THOSE TWO PARTICULAR COLUMN DATA FROM PRADEEP TO PRASAD WHERE I WOULD LIKE TO INSERT THE DATA INTO THE ANOTHER COLUMN I.E.,ADDRESS (COLUMN IN SECOND TABLE(PRASAD)) ………………
THE BOTH OPERATIONS I.E., COPYING RECORDS FROM ONE TO ANOTHER TABLE AND INSERTING VALUES TO ONE TABLE HAS TO DONE SIMULTANEOUSLY WITH SINGLE QUERY …………..
IS IT POSSIBLE IN PRACTICAL?
AM WAITING HERE FOR YOUR REPLY ………………………..
THANKS IN ADVANCE
REGARDS,
[email removed]
Hi, i need to insert data from table in one database to another database with some where conditions. Both tables have same columns (tablename,column name ,datatype etc).
Regards,
chella
Have another server as linked server and write
insert into source_table(col_list)
select col_list from servername.dbname.ownername.source_table where
Thanks! Just what I needed.
I have searched and tried a lot can anyone help me.
my case is very complicated. I need to insert all the column values from a select * statement in one column of another table in multiple rows.
hope you all understood what i want.
ex.
table1(a,b,c)
temptable(col1)
i need each columnar data of table1 as 3 record in temptable.
any solution?
hi,
is there any possible way to create table from select * from another table without create any table structure……
select * into new_table from old_table where 1=0
hello every one i have a doubt…..can u plz clarify it………………….
my doubt is that………………..
How to create a new table by copying particular records from another base table depending on the row number of the base table……….for example consider the following tables.
table 1: pradeep (base table)
empname empno1 loc
———————————————-
azar 9156 hyd
sachin 9458 chennai
shewag 1245 banglore
dravid 2589 vizag
pradeep 2122 guntur
table 2: prasad (which has to be created by copying the records from the base table)
the table2 prasad should be as mentioned below:
empname empno1 loc
———————————————-
azar 9156 hyd
sachin 9458 chennai
shewag 1245 banglore
Motto: i have to create a new table (i.e., Prasad) by copying only the first
3 records from the base table (i.e., pradeep), it is very easy if we know
the relevant data .
Conditions:
(1). I have to copy the relevant data from base table to new table
depending on the row number but not the data which is stored in
the base table,i assume that i don’t know the data until & unless it
is copied into the new table. So this operation has to be performed
depending on the row number.
(2) It is very easy to copy structure & records separately into new table
,but here i want to perform both operations (i.e.,copying structure
& copying records) had to be done at a time.
(3) Here there is a possibility to copy multiple records from base table
to new table.
++++++ hope every one understood my requirements …………………………………….
thanks in advance ……………… am waiting for your replies…………………
regards,
[email removed]
(if any one has any doubt in my requirements or any doubts in my explanation ..plz free to ask me)
perfect..!!! good job…
Hi Dav,
I have 3 tables with the columns below
1.ecatalog_itsc_extension
—————————–
Catalog_ID
2.eCatalog_Item_Type_Approval_Type
——————————
Approval_Type_ID
Catalog_Item_Type_ID
ISO_alpha_2_country_code
Sequence
Retired_Date
Create_Date
Last_Update_Date
Last_Update_By_Text
3. eCatalog_Catalog_Item_Approval_Type
————————————–
Approval_Type_ID
Catalog_ID
Sequence
Retired_Date
Create_Date
Last_Update_Date
Last_Update_By_Text
So i want to update the 3rd table with 1st table coulmn “Catalog_ID” as 3rd “Catalog_ID” and from 2nd table all records to 3rd table based on the “ISO_alpha_2_country_code”. (County code as “US” or “MX”, or “CA”)