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,
insert into table2
select col1,col2,col3.. from table1 where col=’xyx’
its not working for me in some situations.
My table1 contains some null columns also,
its working when my col2 and col3 is with data and if these two columns are null its not working for me.
its showing x rows affected. but the data was not moving
Check if table2 has any constraints that prevent data from being copied
Hi experts,
I have 1 orders table
orders table
=========================================
OrderID OrderDate ProductName QtySold Amount
1 11/11/11 item1 2 70
2 11/11/11 item2 7 90
I want to copy this details on another table Order details table
like this :–
Order details table
=====================================================
OrderdetailsID OrderDate CustomerName ProductName QtySold Amount
1 11/11/11 Jane Austen item1,item2 9 160
What will b the Query for this ??
Thanks in advance
So very helpful! Thanks so much!
Hi All,
I have one requirement like from 1 database i have to copy the data from some tables to another server database(taking backup on during night on every day.But not all tables).
Here the table structure in two servers are same.But there is a primary key and foreign key relationship.So, i cannot use direct query to copy the data.Because while copying it will say duplicate records cannot insert.
I have created a SP in that i will drop tables first and select * into and adding constraints to that tables. It will work fine.But taking long time.
If you have any idea/option that will take less time, let me know.
Thanks,
Hanuman
Why dont you update existing data and add new data? The general logic is
update t1
set t1.col1=t2.col1, t1.col2=t2.col2,…
from table1 as t1 inner join table2 as t2 on t1.keycol=t2.keycol
insert into table1 (columns)
select columns from table2 as t2 where not exists
(select * from table1 where keycol=t2.keycol)
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, SYSUTCDATETIME (), 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 exam_date ORDER BY stu_total_marks DESC)stu_rank
FROM railways_result
insert into railways_result_backup select * from current_railways_result
end
sir, i m trying to delete all record from current_railways_result,railways_result and insert into railways_result
when insert into particular table both table records delete but not inserted into
railways_result
plz help me sir
tanks in advance sir..
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, SYSUTCDATETIME (), 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 exam_date ORDER BY stu_total_marks DESC)stu_rank
FROM railways_result
insert into railways_result_backup select * from current_railways_result
end
i m trying to delete data from current_railways_result, railways_result before
new insert
data deleted from both table,but when i going to insert new data today first time data not insert in table,second time inserted in table plz help me sir
using trigger
Works exactly as described. I’m brushing up on my T-SQL skills, this was exactly what I wanted to replace the Access “Append” queries.
Thanks alot.
Hello sir I am new to SSIS and I have very simple task to do that I can easily do in SQl 2008, but for my work I have to make in SSIS.
I have one source and destination table both in SQl 2008 and I want to Insert data from source table to destination. I tried a lot of work around in SSIS but not able to do that , Can you please tell me how to do that in SSIS. I will really arrpiciate you. here is my script that is working well in SQL.
INSERT INTO [EnrollmentDM].[dbo].[table 1]
([AUTHRZ_INFO_QUAL]
,[SEGMNT_ID]
,[AUTHRZ_INFO]
,[SEC_INFO_QUAL]
,[SEC_INFO]
,[INTERCHNG_ID_QUAL1]
,[INTERCHNG_SENDR_ID]
,[INTERCHNG_RECVR_ID]
,[INTERCHNG_ID_QUAL2]
,[INTERCHNG_DT]
,[INTERCHNG_CNTL_STD_ID]
,[INTERCHNG_CNTL_VER_NUM]
,[INTERCHNG_VER_NUM]
,[ACKMNT_RQST]
,[USAGE_IND]
,[COMPNT_ELEMNT_SEPRTR]
,[MBR_ID]
,[CASE_NUM]
,[SEQ_NUM]
,[FILE_ID]
,[BGN_SEGMNT_ID]
,[REC_INSRT_DT])
(select SUBSTRING(SEGMNT_DTLS,5,2) as AUTHRZ_INFO_QUAL,SEGMNT_ID,
SUBSTRING(SEGMNT_DTLS,8,10) as AUTHRZ_INFO ,SUBSTRING(SEGMNT_DTLS,19,2) as SEC_INFO_QUAL,
SUBSTRING(SEGMNT_DTLS,22,10)as SEC_INFO,SUBSTRING(SEGMNT_DTLS,33,2) as INTERCHNG_ID_QUAL1 ,
SUBSTRING(SEGMNT_DTLS,36,4) as INTERCHNG_SENDR_ID ,SUBSTRING(SEGMNT_DTLS,55,15) INTERCHNG_RECVR_ID ,
SUBSTRING(SEGMNT_DTLS,52,2) as INTERCHNG_ID_QUAL2 ,SUBSTRING(SEGMNT_DTLS,71,6) as INTERCHNG_DT,
SUBSTRING(SEGMNT_DTLS,83,1) as NTERCHNG_CNTL_STD_ID ,SUBSTRING(SEGMNT_DTLS,85,5) as INTERCHNG_CNTL_VER_NUM,
SUBSTRING(SEGMNT_DTLS,91,9) as INTERCHNG_VER_NUM,SUBSTRING(SEGMNT_DTLS,101,1) as ACKMNT_RQST ,
SUBSTRING(SEGMNT_DTLS,103,1) as USAGE_IND, SUBSTRING(SEGMNT_DTLS,105,1) as COMPNT_ELEMNT_SEPRTR,
MBR_ID,CASE_NUM,SEQ_NUM, FILE_ID,BGN_SEGMNT_ID,REC_INSRT_DT
from table 2 where SEGMNT_ID =’ISA’)
thank for sharing
I need to create a new table called “costumer” that included some of columns from the “user table”, and also “project table”. I built my suppliers table with spesific column names and I need to fill its column by using data of the other tables. Finally I am trying to finish; when user create a new account and project, the costumer table automatically fill with some of other two tables varieties with different column names.
INFO: I have three different user types such as “suppliers”, “costumers”, “managers”. I am holding their information(include user types) in one table called users.
Please help me if you have free time.
sir
I want to insert column form table into another table
for this I wrote like this
SELECT column_name(s)
INTO new_table_name
FROM old_tablename
but i am getting error like ‘undeclared variable : name of new table ‘
plZ suggest me how to do this
Than U
t the full code you have used. There is no variable in this code
Dear Mr.Pinal,
We need your help, we have 3 table : Sales Last Year, Sales Actual, and Sales Target.
and then we need to get result for new table, the new table is “Sales YTD”,
Sales YTD is cumulative from Sales Actual.
could you give me the query for Sales YTD(Year-To-Date).
[email removed]
Thanks,
Regards.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
namespace Assignment.Classes
{
public class DBCon
{
public static string con = “Data Source=HP-PC;Initial Catalog=AssignmentDB;Integrated Security=True”;
//s public static string con = “Data Source=192.168.0.3;Initial Catalog=AELANK;User ID=sa;Password=sssa;Connection Timeout = 2000000”;
//public static string con = “Data Source=HASHENDRA-PC\\SQRSERVER;Initial Catalog=AELANK;Integrated Security=True”;
public static SqlConnection conn = new SqlConnection(con);
public static SqlCommand comm = new SqlCommand();
public static SqlDataAdapter adapt = new SqlDataAdapter();
public static DataSet dtset = new DataSet();
public static string user;
public static string department;
public static string custommer;
public static string selectedDate;
public static string FromDate;
public static string ToDate;
public static string email;
public static string ddlStateSales;
public static string ddlStateCus;
public static string SalseManager;
public DBCon()
{
}
public static DataTable SQLDTT(string strSQL)
{
conn.Close();
if (conn.State.ToString() == “Closed”) { conn.Open(); }
SqlCommand comm = new SqlCommand();
SqlDataAdapter adapt = new SqlDataAdapter();
comm.CommandTimeout = 0;
comm.CommandText = strSQL;
comm.Connection = conn;
adapt.SelectCommand = comm;
DataTable DataTable = new DataTable();
adapt.Fill(DataTable);
//comm.CommandText = strSQL;
//comm.Connection = conn;
//adapt.SelectCommand = comm;
//dtset = new DataSet();
//adapt.Fill(dtset);
//conn.Close();
return DataTable;
}
public static DataSet SQLDT(string strSQL)
{
conn.Close();
if (conn.State.ToString() == “Closed”) { conn.Open(); }
SqlCommand comm = new SqlCommand();
SqlDataAdapter adapt = new SqlDataAdapter();
comm.CommandTimeout = 0;
comm.CommandText = strSQL;
comm.Connection = conn;
adapt.SelectCommand = comm;
DataSet dtset = new DataSet();
adapt.Fill(dtset);
//comm.CommandText = strSQL;
//comm.Connection = conn;
//adapt.SelectCommand = comm;
//dtset = new DataSet();
//adapt.Fill(dtset);
//conn.Close();
return dtset;
}
public static int GetNumOfRec(string strSQL)
{
/// Use for get No of Records in SELECT command
//try
//{
int intResult = -1;
conn.Close();
if (conn.State.ToString() == “Closed”) { conn.Open(); }
strSQL = DBCon.SQLFormat(strSQL);
SqlCommand sqlCmd = new SqlCommand(strSQL, conn);
intResult = (int)sqlCmd.ExecuteScalar();
sqlCmd.Dispose();
return intResult;
//}
//catch (Exception objError)
//{
// //MessageBox.Show(“System Error – ” + objError.Message.ToString(), “Application Error”, MessageBoxButtons.OK, MessageBoxIcon.Error);
// return -1;
//}
}
public static string SQLFormat(string strSQL)
{
strSQL = strSQL.Replace(“\r”, ” “);
strSQL = strSQL.Replace(“\n”, ” “);
strSQL = strSQL.Replace(“\t”, ” “);
strSQL = strSQL.Replace(” “, ” “);
return strSQL;
}
public static string SQLER(string strSQL)
{
conn.Close();
if (conn.State.ToString() == “Closed”) { conn.Open(); }
strSQL = SQLFormat(strSQL);
SqlCommand sqlCmd = new SqlCommand(strSQL, conn);
string strResult = sqlCmd.ExecuteScalar().ToString();
sqlCmd.Dispose();
return strResult;
}
public static SqlDataReader DataReader(string strSQL)
{
conn.Close();
if (conn.State.ToString() == “Closed”) { conn.Open(); }
strSQL = SQLFormat(strSQL);
SqlCommand sqlCmd = new SqlCommand(strSQL, conn);
SqlDataReader dataRed = null;
sqlCmd.CommandTimeout = 0;
dataRed = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
sqlCmd.Dispose();
return dataRed;
}
public static SqlDataReader oSQLER(string pSQL)
{
conn.Close();
if (conn.State.ToString() == “Closed”) { conn.Open(); }
SqlDataReader objReader;
SqlCommand objCommand;
// conn.Close();
// if (conn.State.ToString() == “Closed”) { conn.Open(); }
objCommand = new SqlCommand(pSQL, conn);
objReader = objCommand.ExecuteReader();
objReader.Read();
return objReader;
}
public static int SQLCX(string strSQL)
{
if (conn.State.ToString() == “Closed”) { conn.Open(); }
strSQL = SQLFormat(strSQL);
SqlCommand sqlCmd = new SqlCommand(strSQL, conn);
sqlCmd.CommandTimeout = 0;
int intResult = sqlCmd.ExecuteNonQuery();
sqlCmd.Dispose();
return intResult;
}
public static int SQLCXRemoveRow(string strSQL, string item)
{
if (conn.State.ToString() == “Closed”) { conn.Open(); }
strSQL = SQLFormat(strSQL);
SqlCommand sqlCmd = new SqlCommand(strSQL, conn);
sqlCmd.CommandTimeout = 0;
sqlCmd.Parameters.AddWithValue(“@value”, item);
int intResult = sqlCmd.ExecuteNonQuery();
sqlCmd.Dispose();
return intResult;
}
}
}
Good One !
ur code is work well…
but image datatype it gives an errer..
it’s copies the rows … but images are stored in Table not displayed in image control……
plz reply ….
Hey Dave,
It would be great if you could add an update to your post to explain that this will not work if there is a TRIGGER on that table which does not take multirow operations into consideration. The user can of course consider disabling/re-enabling the trigger as part of their script.
Regards,
Colly
hi sir how can i send mail by triger
HI Pinal,
we are using sql server 2005,actually frequently we have to restore production database to acceptance environment ,sometimes database in acceptance have more tables than prod database. in that case i just use import\export wizard or select into command to transfer extra tables in acceptance to a temporary created database in same server, but while doing that size of the table in temp database is less than actual one.So ,while looking table structure there is no index’s,primary keys,foreign keys etc. could ypu please post your suggestion
Hello sir…! I have little problem about database in server 2005..!
I have a Table1 and there are 3 columns like, EmpID,EName,Designation..
Designation column is complete NULL.. here i want to add values from different table which is Table 2. there are 2 columns like EmpID and Designation…!
so what will be queary to add Designation values in Table1 from Table2 Designation column on the basis of EmpID!
you can try this code
update table1 set Designation=(select Designation from table2 where Empid=’value1′) where EmpId=’YourEmpid’
Hi,
I am having three tables named like tbl_report1,tbl_report2,tbl_customer .my issue is i want to combine these tables data and structure into one table like tbl_company.is it possible?.if possible in the sense can you please give me the suggesstion.please do the needful.
tbl_report1 columns:
invoiceno(foreign key fk,particulars,quantity,perprice,amount,orderno,compname,invoicedate,ondate,invoiceid.
tbl_report2 columns:
compname,compaddress,invoiceno(primarykey),orderno,invoicedate,ondate,amountstatus,netvat,netamount,tamount
from the above three tables i want to copy their data and structure into tbl_company.how can i proced.
how to insert data into another database? How about that? can you please help me.. Thanks
Use three part name
insert into dbname.ownername.table_name(cols)
select cols from table
Hi All
I’m inserting Data From One Table to Another in different databases in the same server, after this is done i have to delete the copied data from the source table. The records are selected based on few constraints in the WHERE clause This table has several dependent tables that also have to be moved.
I have chosen the INSERT INTO…SELECT method because:
1) I can monitor errors during the process
2) I can use transactions and error handlers for testing purposes and to ensure all or nothing will be copied.
I’m having the following dilemas because of this choice:
1) Performance is not the best.
2) Where to run the delete operation? A) Right after every single INSERT statement B) After all thousands INSERT statements.
3) If i need to COMMIT/ROLLBACK INSERTs on target table and DELETEs on source tables, I wonder if SQL Server is able to handle both of them within the same TRANSACTION statement, or i have to first finish one transaction and then change database context (USE statement i.e.) to commit the operations in the other database.
In the end, seems that for this kind of problem, the approach i have chosen has a high cost since the numbers of inserts are about 228000 (I’m copying one row along with dependent rows per each iteration of a loop), this makes me wonder if i shouldn’t just copy records from one table at the time, using INSERT INTO… SELECT …WHERE ID IN (SELECT IDS FROM SOURCE TABLE)
I forgot to add that my database is a SQL Server 2005