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
This is a different subject.
I have some very large table which I will like select all the data in those tables to different tables by using select into. Also, I want to set the row count to 100000 at a time until all the rows a select into the new table with a loop. Here is the code I have and it is not working properly. Can you help?
SET ROWCOUNT 100000
DECLARE @RCOUNT INT
SET @RCOUNT = 10000
WHILE @RCOUNT = 10000
BEGIN
BEGIN TRAN
INSERT Tbl_archive2
SELECT * FROM archive
SELECT @RCOUNT = @@rowcount
COMMIT TRAN
CHECKPOINT
END
go
WHILE @RCOUNT = 10000
should be
WHILE @RCOUNT <= 10000
I have the cross dock making scans per HU’s. Number of scans = 6 that can happen at different times. The key is based on 2 fields 1> TPP and 2> HU
Hence I should have a data line as follows:
TPP HU Receive_Dt Stage_dt Load_Start Loading Loaded_Dt Load_Complete
HOU 123 12-10-2009 12-11-2009 12-21-2009 12-21-2009 12-21-2009 12-21-2009
But since the scans are done at multiple time stamps my database fills up like this
TPP HU Receive_Dt Stage_dt Load_Start Loading Loaded_Dt Load_Complete
HOU 123 12-10-2009 12-11-2009 – – – –
HOU 123 – – 12-21-2009 – – –
HOU 123 – – 12-21-2009 12-21-2009 12-21-2009 12-21-2009
The other problem is the sheer volume of HU’s processed hence I do not know an ideal way to create a stored procedure that will index each key and plug in the dates accordingly. Any tips or examples you can share?
I perform this operation many times but never heard that SQL can create New Table for me as you described in Method 2.
Thanks you sir
Note that the target table will not have any indices, constraints,etc if you used method 2
i want a single query “to delete a record from table1 and insert that deleted record to table2”
or
“i want to move a record to another table”
please help me
Hi Mukherjee,
You can use this query to move data from one table to another table:
DELETE
FROM SourceTable
OUTPUT
deleted.Column1
,deleted.Column2
INTO DestinationTable
Thanks,
Tejas
SQLYoga.com
I had created a table student.and exected it. i then wanted to add more columns to the student table but i get a ‘create table denied’ message
hi,
i want to insert row in a table where two values are given which are to be inserted as it is but thje third value is to be fetched from the other table, how can i do this using Insert statement.
insert into abc (aa,bb,cc)
values (‘xx’, ‘yy’, –)
insert into abc (aa,bb,cc)
select ‘xx’, ‘yy’, col from other_table
Hi,
I have a MDF file (database) and I want to copy the contents of a table in .mdf to a similar table in a .sdf file.
I tried ==>
SELECT *
INTO sdfDB.ScannerData_Test
FROM mdfDB.ScannerData_MDF
also i tried ==>
INSERT INTO sdfDB.ScannerData_Test
SELECT *
FROM mdfDB.ScannerData_MDF
But no use. is this kinda copy not allowed on sdf files.
If not, how do I copy the contents to this compact Database (.sdf)
Thanks,
Vishruth
Hello,
I have 2 databases and they are
#1 an mdf file and
#2 an sdf file.
How to I transfer the contents of a table in .mdf to a similar table in .sdf database.
I tried both mentioned methods in the first article, but it doesn’t really work for me.
Is it because of the compact edition (.sdf) database what am using.
Please suggest.
Thanks,
Vishruth
Use Simplest Way:
SELECT * into NewTable FROM Old_Table
This is the query for inserting data form one table to another table in different DATABASE
THIS IS A SUCCESSFUL QUERY
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
It is Running
One question related to your post …I have question table and answer table with quesid as foriegn key in answer table. there is another similar table structure and I want to insert from one table to another table i am using following query for it…
–DECLARE @QuestionID AS BIGINT
–INSERT INTO QBQuestions (AreaID,UserID,QuestionType,QuestionTextActive)
–SELECT AreaID,UserID,QuestionType,QuestionText,Active
–FROM ContributedQuestions
–WHERE CQuestionID in (18,19,20)
–SET @QuestionID = SCOPE_IDENTITY()
–INSERT INTO QBAnswers ( Answer,QuestionID,IsCorrect)
–SELECT Answer,@QuestionID,IsCorrect FROM ContributedAnswers
–WHERE CQuestionID in (18,19,20)
Now to insert mulitple row at one time I used query like this
DECLARE @lclMySQL as varchar(MAX)
SET @lclMySQL = ‘INSERT INTO QBQuestions (AreaID,UserID,QuestionType,QuestionText,Active)’
SET @lclMySQL = @lclMySQL +’SELECT AreaID,UserID,QuestionType,QuestionText,Active FROM ContributedQuestions’
SET @lclMySQL = @lclMySQL + ‘SET IsActive =0 WHERE CQuestionID IN (‘+@QuestionID+’)
‘ where I am using @question id as string of quesiton id but in this case how can I insert question id as foriegn key to Answer table??? Is there some way in which i can use IN keyword in where condition and also insert forign key value in to answer table?????
hai sir, i want the coding for update the results from the another table
General approach
update t1
set t1.col=t2.col
from table1 as t1 inner join table2 as t2
on t1.keycol=t2.keycol
how to retrive data through using join condition.
in table A i am having std no, std name, address.
in table B I am having host id, std no, age, division
now i want std name who are the people more than 30 age
can u please help me with query
select t1.* from tableA as t1 inner join tableB as t2 on t1.stdno=t2.stdno
Hi friends. I’ve a dought on sqlserver2000. As i’m going to do my major project, i wenna know how to add images into sqlserver 2000. and how to retrive. Please send me simple codes for that.
For instants.
{
con SqlConnection;
con= new SqlConnection(server=”myserver”; uid=”sa”; pwd=”sa”;database=”mydatabase”,con);
}
plz tell me..
Better approach is to store only the physical path of the image in the table and store actual image in the Server’s directory
1. Friends I need to write a procedure using cursor by for loop.
Below are the scenario:
1.. Data will be pulled from table1
2. Use for loop for cursor into table1(Table1 records needs to go to table2 ,table3),
3. Will also need to go into table2
4. Will also need to go into table3
Kindly help.
Why do you need a cursor?
This is as simple as
insert into table2(columns)
select columns from table1
where some_condition
insert into table3(columns)
select columns from table1
where some_condition
I tried this method, but it did not work for me.
USE KudlerAccounting
GO
INSERT INTO Accounts2004(Expenditures)
SELECT Expenditures
FROM ExpendTotals2004
WHERE Account > 0
Error: Cannot insert the value NULL into column ‘Account’, table ‘KudlerAccounting.dbo.Accounts2004’; column does not allow nulls. INSERT fails.
The table Accounts2004 has one more non-null column. You need to add data to that column too
Hello ,
What is way to Avoid Log in case ‘ insert into’ operation .
Hello, am using this query in sybase
insert into table2 SELECT * FROM table1
The two tables have the same table structure
Getting below error
An explicit value for identity field in table2 can only be specified in a insert statement when a field list is used
can anyone help to proceed?
thanks
You need to list out the column names for table2
insert into table2(col1,..colN) SELECT col1,…,ColN FROM table1
Thanks Madhivanan,
Used the query in this format
insert into table2(col1,..colN) SELECT col1,…,ColN FROM table1
There is one another problem now
Error:
Explicit value specified for the identity field in table2 when ‘SET_IDENTITY_INSERT’ is OFF
On checking table details sp_help table2 i did not get any information on this
Is this a trigger avoiding insert into the table or do i need to ask the DBA to set the value for this to ON
You need to omit the identity column from the column list