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
I’m writing a batch job using .NET and want to compare a table in our data warehouse with a transactional table. These 2 tables reside on different servers and databases. I am creating a datahandler from .NET and have been told that creating 2 different data connections will not work ???
I actually need to compare rows on each table to determine if rows may have been updated already.
Any other ideas or will 2 different connections work with two seperate data adaptors? Any ideas welcome or if there is a better idea please share.
thanks
What if you have a large amount of data and your transaction log fills. We are using a query to gather data from several tables and inserting into one table. The insert into select works well except where the result set is very large, the transaction log fills. This is a reporting db that is refreshed nightly from a highly normalized transactional db. After the restore, we are running a script to create & populate some unnormalized tables. What other method can we use for the large results set?
Thank you!
hai
i am getting ora.00905 : missing keyword error
with the following method
can you please help me
Method 2 : SELECT INTO
This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.
USE AdventureWorks
GO
—-Create 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
and my sql query is
select accnt_name into testtable
from inturi.c_account
where row_id=’460-2BF’
@Rama
I dont know if this feature is in ORACLE.
From the error message you posted, I am guessing that you are trying to execute this query in ORACLE. Well script executes fine in SQL Server not sure about ORACLE.
Regards,
IM.
@Imran
Just confirming, Oracle uses INTO in PL/SQL to set a variable. To load a TABLE it’s CREATE TABLE AS SELECT….
I get confused in between the two sometimes. It’s not the different syntax, it’s when the same syntax means two different things.
@Brian
I agree with you.
Your explanation, to perform similar action in ORACLE, we have to use below command is absolutely correct.
CREATE TABLE AS SELECT…. or
But Brian there are chances that users copy scripts from internet and with out knowing that Script is written in T-SQL they could try to execute it on ORACLE which leads to these error messages.
Brian any idea, which is according to ANSI SQL,
SELECT * INTO table_name from ……
or
CREATE TABLE Table_name as select ……..
Regards,
IM.
Hi!!
i have just started working on SQL i need sum guide line how to create own table can u help me …
RAFAY PERVAIZ.
Hi,
I have different tables (say table1, table2,etc) with the following fields.
table1(Timestamp,value1)
table2(Timestamp,value2)
.
.
.
All have same timestamp. I have to merge all tables into single table
table(timestamp,value1,value2,…..)
I think the time stamp can be updated as datetime format. But I dont know how to update columns by comparing with the timestamp. Please help
Thanks
Hello,
I want to update my table a from database A with table b from database B.
Has anyone any tips?
Hello Dave
I am doing a select and inserting into temp table. I want the rows inserted into temp table ina particular sequence.
Example
select a,b from table 1 order by a into temp table2
But i see that table2 is not inserted in order. Is there a way to do this?
thanks,
Madhu
Hello to all,
I want to copy data from a database table in a server to database table of another server.
I user this example
insert into DocTypeGroup
Select DocGrp_Id,DocGrp_SubId,DocGrp_GroupName,DocGrp_PM,DocGrp_DocType
from Opendatasource( ‘SQLOLEDB’,’Data Source=10.132.20.19;User ID=sa;Password=gchaturthi’).dbIPFMCI.dbo.DocTypeGroup
all table name database name username and password are ok but the following error shows —
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, see “Surface Area Configuration” in SQL Server Books Online.
What is solution now….
Hi pinal dave ,
Thanks in advance
I have one question about sql server 2005 TRIGGER.
can i insert data into master-detail table using Trigger.
say for example.
i have one master table name(Info) and field(ID,Name,Age)
and detail table name(sub_info) and field(SUB_ID,ID,ADDRESS). so now when i insert single row in info(master) table then using trigger also insert row in sub_info(detail table). but in sub_info table i want to pass address filed data from outside. so is it possible or not.
also i create trigger which i given below. just refer it and reply me as soon as possible.
CREATE TRIGGER ts_SUB_INFO_ADD_TRIGGER ON info for insert
AS
declare @id int
select top(1) @id = id from info order by id DESC insert into Sub_info(ID,address)values(@id,’Event issue’)
insert into info(name,age) values(‘bhavin’,24). when i run this insert query then t\above trigger insert single row in sub_info table. but here address is static. so i want to add dynamic address.
So please take a urgent and send me reply.
I have two table table 1 & table 2
table 1
OLD_id
——————— ———————
64518
59414
65007
57750
table 2
New_ID
——————— ———————
5053
5756
8535
2328
I need result
OLD_id New_ID
——————— ———————
64518 5053
59414 5756
65007 8535
57750 2328
I dont have any condition .
my query with union is returning the result
OLD_id New_id
——————— ———————
0 5585
0 5891
0 5895
0 5896
57750 0
59414 0
64518 0
65007 0
I need this result in this form
OLD_id New_id
——————— ———————
57750 5585
59414 5891
64518 5895
65007 5896
Any solution ??
Hi,
How to make condition. Are you sure that Row 1 in Table A has match with Ro1 of Table B?
If that is the case then you can use this:
;with A AS(
SELECT ROW_NUMBER() OVER(ORDER BY ColumnA) AS RowNum,
*
) , B AS(
SELECT ROW_NUMBER() OVER(ORDER BY ColumnB) AS RowNum,
*
)
select A.columnA, B.ColumnB
from A
INNER JOIN B ON a.RowNum = B.RowNum
Thanks,
Tejas
thanks vry much..it helps me a lot…
This Data Has completely resolved my problem.
For this kind help thanking you so much
how to write a cursor to loop through the records of one table and insert them into a temp table?
Can anybody tell me how to write a cursor to loop through the records of one table and insert them into another table?