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
if (user != null)
{
if (user.IsApproved == false || user.IsLockedOut == true)
{
SecurityEvent evt = new SecurityEvent(“The user (” + Login1.UserName + “) is locked out, but has tried to authenticate.”,
this, SecurityEventCodes.EVENT_CODE_LOGIN_ATTEMPT_BY_LOCKED_USER);
evt.Raise();
Login1.FailureText = “Your account is locked. Please contact administrator.”;
}
}
hi dave ,
I want to insert data in a table located in one database ,
by taking the data from another database —– In DB2.
both database are on different machine.
Please respond ASAP.
Make another server as linked server. Then write this code
insert into table1(col_list)
select col_list from servername.dbname.ownername.table1
where
Hi Dave,
I have two differne tables and Table A has around 1000 records.
Table B has around 1500 records
Both tables have a common field (Customer_code).
And few Customer_Codes which are there in the Table A are not there in the Table B and few Custotomer Codes which are there in the Table B are not there in the Table A.
Both the Table have another Column called Quantity.
Now i want to bring Customer_code from any one of the table, Quantity of the Table A and Quantity of table B to a temporary table.
Can u help me with the query where the sum of quantity should not change even after bringing to the temp table.
Hi,
I want to insert using SELECT statement but select statement will be only for one column for rest of the coulmns fixed data will be innserted.
like
Insert into table EmployeeInfo(Id,firstname,lastname, Salary) values(1,’aaa’,xyz’, Select salary from Payroll_Info where EmpId = 1)
It is giving me error like
Incorrect syntax near the keyword ‘select’.
Can you please help me in this regard.
Thanks,
Vishakha
Insert into table EmployeeInfo(Id,firstname,lastname, Salary) Select salary,1,’aaa’,xyz’, from Payroll_Info where EmpId = 1
HOW TO INSERT VALUES FROM ONE TABLE TO ANOTHER AND ADD THE TIME (GETDATE) AFTER A FORM SEARCH WITH FRONT PAGE
Hi Suresh(23),
change table names it should work for you.
select t1,sum(qty)
from
(select t1,sum(qty) qty from t1
group by t1
union all
select t1,sum(qty) qty from t2
group by t1
) A
group by t1
cheers,
anand.
Hi suresh,
more details if you are expecting below result:
select t1,sum(t1_qty1) t1_qty,sum(t2_qty2) t2_qty
from
(select t1,sum(qty) t1_qty1,0 as t2_qty2 from t1
group by t1
union all
select t1,0 as t1_qty1, sum(qty) t2_qty2 from t2
group by t1
) A
group by t1
cheers,
anand.
Hi manvendra Singh(22),
we can use Openrowset and insert data into table using
Insert into
select from OPENROWSET(‘SQLNCLI’, ‘Server=xxxx;Trusted_Connection=yes;’,
‘SELECT GroupName, Name, DepartmentID
FROM AdventureWorks.HumanResources.Department
ORDER BY GroupName, Name’) AS a;
see sql help for more details.
cheers,
anand.
Dear Vishakha(24),
we can put data like that. i’m not clear about your requirement but generally anthing to be picked by like that then we use Constaraints/Deafults in defining values.
give more details about problem to help you better.
cheers,
anand.
When the question of optimisation comes we use stored procedures instead of long queries.
Now suppose a stored procedure’s name is 101 character long then what to do?
101 character does not matter. It will now affect the performance. But what is the need of having such long name?
hi, i tried my codes like this but it’s not working…
please help..?
Insert into magpatoc.dbo.RSOTransfer
Select * FROM (‘Provider=Microsoft.Jet.OLEDB.4.0;’,
‘Data Source=c:\CopyOfRSODB.mdb;User Id=admin;Password=;’,
‘SELECT * FROM FinalCustItemRSO’)
What did you mean by “Its not working?”
hi
Iwant to generate a script for insertion of data to a table.
I have to repeatedly insert specific data to a table for each database,i want to automate it by genarating the insertion script.with the scripts of creation and all i have to generate some hundred records for a table to each database.
Please help me if any one got this situation
Thanks and regards
Ravishanker Maduri
Hi!
Thanks for a great article
Im using ODBC in c++ in a applications,
but all i want is to know how in the program, import large data directly to SQL Server but from the application from an external datasource (csv,txt), without have to use BULK INTO or OPENROWSET.
Heard somewhere that the bcp_init would do the job.
Regards
Lambda Swahili
Hi……
consider i hav three tables
table1(t1col1,t1col2)
table2(t2col1,t2col2)
table3(t3col1,t3col2)
i need to enter t1col1 and t2col2 in t3col1 and t3col2 respectively….
plz guide me to implement this using insert select statement
Regards,
Bhanu
Thanks, just what I needed.
Hi Dave,
Nice Blog. Helped Me alot..
Thanks ;-)
Have a user define data type for a phone number field. Defined as (###)###-####. Problem is have a hard time checking the constraint when inserting data from a stored procedure SQL 2005 Thank you for the help.
Format is
col like '([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9]'
Hi Dave
I have two tables, one with 10 columns and the other with 20 columns
10 columns are common in both the tables
i want to update the table with 20 columns on a daily basis using a DTS Package
i need to insert if a record doesnot exist ,if exists i need to update the 10 fields
kind of new to sql sp’s
thot of using cursors
can anyone suggest me a way of doing it ?
thanks in advance
you can transfer data from one server to another by the
following commands
Insert into finalxlsitems1
select * from
OPENROWSET(‘SQLOLEDB’,
‘server’;’sa’;”,
‘SELECT *
FROM server.reportserver.dbo.finalxlsitems1’
)
Can you tell me the naming conversion and the format of data passing in OPENROWSET …what is ‘sa’ stand for?
salam.sir
My Problem
i have 5 tables and one main table.the main table cantain all the foreign keys of another 4 table.so if i enter values in main table.it will not enter it.because it contain foreign keys of 4 tables.so please send me vb code to insert a record.
or
VB code for insertion of foreign keys values in table without mentioning the forighn key attribute.
THANKS