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 have 2 tables simillar to this example.
Table A Table B
group_id group_id count
1 1
1 2
2 19
1 33
2
2
19
19
33
19
i need to write an sql statement that will count the number of occurrences of each group_id # in table A and place that count in the count column of Table B.
Thank you in advance,
tom
Thanks Pinal,
Will select into statement copy constraints also?
We’ve found that OPENROWSET only ever returns 1 row in SQL Server 2008?
For example, this code
SELECT *
FROM OPENROWSET(‘MSDASQL’,
‘DSN=the_dsn;UID=the_uid;PWD=the_pwd;’,
‘select 123 from dual union select 456 from dual’)
… correctly returns 2 rows (123 and 456) in SQL 2000 but only returns 1 row (123) in SQL 2008.
Is this a configuration issue? We’re connecting to an Oracle database.
Thanks in advance
Q. Write a SQL select statement. Given a table called EmailAddresses and the columns ID (int) and EmailAddress (varchar(100)), write a SQL SELECT statement that retrieves all email addresses that occur more than twice in the table.
you are the best
thank you! this article was very enlightening! i used the select into query combined with an update query to clone a table and swap values of two columns in a circumstance.
SELECT *
INTO [new-table]
FROM [old-table]
DECLARE @temp AS decimal
UPDATE [new-table]
SET @temp=value1, value1=value2, value2=@temp
WHERE id < 806
Hi there..!
I want to know how to copy the data and its properties(identities)/structure from the old table to new table. I am really upset with this thing. When I was just using SELECT INTO FROM statement, It only copy the data with basic structure of the old table.. Kindly need your help.. thanks!
SEND ME A FUNCTION PROCEDURE
I tried by making TransactionOption to be Required.
i want to insert a data throug child table
for this i want to check if the data is all ready in master table than no problem,
but if it is not in master table than i want ot insert data through child table.which is connect with the master table
so please tell me what should i do
Hi,
how do i insert br_no?
i have created a table1 mst_brng( br_no)pk
i have created a table2 sec_mst_brng(br_no,br_mk)br_no is a FK
and i want to insert the br_no in table2(br_no, br_mk)
but i want check first if br_no allready in master table than no problem,
but if it is not in master table than i want to insert data through child table so for this what should i do
Hi Rushi,
You are going right way. You have to use transaction.
while inserting records in child table first you have to find that that record is already exist or not ,if not then first store into one local variable and then add it into master table first and then add in child table.
I hope u will get it .
Thanks
Darshan Shah
Hi Mitch,
Basically we have to use select * into for temporary tables.
We can use it to create basic structure with Data.
But if you want to create table with all details then first you have to create script for it.
GO Enterprise manager->right click on object ->Generate Script ->Create ->copy the script and use that script to create new object.
after then use insert statment with select.
I hope this will help you.
Thanks
Darshan Shah
My tables are SubjectTable and GradeTable.SubjectTable has datafields of Course, Subject, Units While GradeTable has Studname, Subjects, Grade, Remarks. I inserted datafields of subject from SujectTable to GradeTable. And it leaves the other rows blank. How can I Add data to a Null rows?Can somebody help me with this problem of mine….thanks
Hi Dave,
How do we pronounce u’r name.
thanks in advance. :)
Hi pinal,
i have a problem inserting multiple records into a table. My code looks like this:
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT strength_app_men_inst_id from TACF_WFTA_STRENGTH_APP_MEN_INST where wf_task_assignment_id = @WfTaskAssignmentIDP1
BEGIN TRAN t3
BEGIN
OPEN @MyCursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @MyCursor
INTO @StrengthAppMenInstIDP1
END
exec @ReturnCode = getid_sel @table = ‘TACF_WFTA_STRENGTH_APP_MEN_INST’, @id = @NewID2 OUTPUT, @mode = 2
insert into TACF_WFTA_STRENGTH_APP_MEN_INST (id, wf_task_assignment_id, strength_app_men_inst_id) values (@NewID2, @WfTaskAssignmentIDP2, @StrengthAppMenInstIDP1)
CLOSE @MyCursor
DEALLOCATE @MyCursor
END
IF @@error 0
ROLLBACK TRAN t3
COMMIT TRAN t3
the select statement returns multiple values and i have to insert that into the same table. i know cursor is not a good way, but my select statement will always return a max of 10 values. Can you please check where i am going wrong, or a better way.
Can you please help me with this as soon as possible. I would really appreciate this.
Thanks,
Harish
FYI: in my test data, the select returns 2 values, but when i ran the query, i got only one row with @StrengthAppMenInstIDP1 as null, but it had a value.
Dont know where i am going wrong…
Harish
@Harish
Is there a way top combine this into one statement?
INSERT INTO …. SELECT … FROM
@Brian, hmm i dont think so. But i was able to figure out my issue.
The correct one’s is:
DECLARE @MyCursor1 CURSOR
SET @MyCursor1 = CURSOR FAST_FORWARD
FOR
SELECT strength_app_men_inst_id from TACF_WFTA_STRENGTH_APP_MEN_INST where wf_task_assignment_id = @WfTaskAssignmentIDP1
OPEN @MyCursor1
FETCH NEXT FROM @MyCursor1 INTO @strength_app_men_inst_id
WHILE @@FETCH_STATUS = 0
BEGIN
exec @ReturnCode = getid_sel @table = ‘TACF_WFTA_STRENGTH_APP_MEN_INST’, @id = @NewID2 OUTPUT, @mode = 2
insert into TACF_WFTA_STRENGTH_APP_MEN_INST (id, wf_task_assignment_id, strength_app_men_inst_id) values (@NewID2, @WfTaskAssignmentIDP2, @strength_app_men_inst_id)
FETCH NEXT FROM @MyCursor1 INTO @strength_app_men_inst_id
END
CLOSE @MyCursor1
DEALLOCATE @MyCursor1
Thanks for checking out… Harish
@Harish
I’m happy you could figure it out.
I always try making it one query if possible. I could not see it here, because a PROCEDURE is being used in the middle.
INSERT…SELECT is usually faster than a CURSOR. And as one query, everything will be in context too. And for many, it is much easier to read because all the logic is in one statement. It might be worth a bit of your time to review the entire process and see if it can be one statement.
Thanks Brian, for your time and effort, i’ll re-look into my stored proc and try to make it simple as you suggested…
Thanks again,
Harish