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 am writing a SP to copy existing data into the same tables. This is similar to the actual COPY command. The sequence of copy is as follows
1) Table1(Parent table)
id Column1
11 Test1
12 Test2
2) Table2(Child 1 Table)
id FK Column1
13 11 Test4
14 11 Test5
3) Table3(Child 2 Table)
id FK Column
15 13 Test6
16 13 Test7
17 14 Test8
I am copying only say data for ID = 11 into the table1 and all child tables. The new rows inserted into the ChildTables (Table2, Table3) should carryover the Primary key and insert them as foreign keys into the child tables.
For eg: output looks like this, If user Copied 11 from Table 1 then
Table1 will look as
id column1
11 Test1
12 Test2
100 Test1 (new copied data)
Table2 will look as
id FK column1
13 11 Test4
14 11 Test5
15 100 Test4 (new copied data)
16 100 Test5 (new copied data)
Table3 will look as
id FK Column
15 13 Test6
16 13 Test7
17 14 Test8
18 15 Test6 (new copied data)
19 15 Test7 (new copied data)
20 16 Test8 (new copied data)
Appreciate any ideas or help on this
Hi,
I am trying to write a query that will copy select data from table A to Table B. However Table A gets updated, so I need a way to copy that selected data from Table A over to Table B without recopying data I have already copied from Table A into Table B. Any help would be greatly appreciated.
@ Josh,
You can create an insert trigger on table A which will insert data in table B automatically.
Here is an example,
1. create two tables , exampl and example.
2. create trigger on table exampl.
3. insert a record in table exampl, and check table example, you should be able to see the same inserted record.
CREATE TABLE EXAMPL (COLA INT , COLB VARCHAR(10))
GO
CREATE TABLE EXAMPLE (COLA INT , COLB VARCHAR(10))
GO
CREATE TRIGGER TR_EX1
ON EXAMPL
FOR INSERT
AS
DECLARE @VAR1 INT
DECLARE @VAR2 VARCHAR(10)
SELECT @VAR1 = COLA , @VAR2 = COLB FROM INSERTED
INSERT INTO EXAMPLE SELECT @VAR1 , @VAR2
GO
INSERT INTO EXAMPL VALUES ( 1, ‘TRUE’)
GO
SELECT * FROM EXAMPL
SELECT * FROM EXAMPLE
GO
Hope this helps.
Thanks
Imran.
i need some help guys,
suppose table a has ID as a column
and you want to insert data from table b to table a where table a ID = @id (where @id is passed into a sproc)
lets say
insert into table a
(column a, column b, column c)
SELECT column a, column b, column c
(FROM a series of joins and table b and WHERE conditions)
— and this is where the problem is…
AND table a.ID = table b.ID??? wont even let me alias table a…any clues?
@DON,
I am not sure if I understood your questions.
1. Reason why you cannot use alias a fot table would be, generally we give aliases for table after FROM statement thats where we define all aliases and in your insert into statement your table is coming before FROM statement. This is just observence … I dont know what is the correct explanation.
2. When you try to insert into a table, you can insert all the column, similarly when you try to delete some row, you cannot delete one column in that row, whole will be deleted, BUT when it is for UPDATE, you can update one column of one row.
You are writing an insert statement which means you are inserting whole row and the same time you are using a join to compare the same table using a.ID = b.ID , This is not possible to insert a record in table a when you are using the same table in comparision, only situation when this possible is UPDATE. when you can compare the values of same column in that row.
I know its very confusing, hope you understood, If I am wrong please correct more than happy to learn :)
Hi, CAn Anybody tell me that , i Have 10 records in Table A , and 2o record in Table B now i want to add these 20 records in table A using DATA TRANSFORMATION WIZARD in SQL 2000,
Can anybody help me plssssssssssssssss. It s Very Very Urgent.
Thanks in Advance,
Kamlakar
Software Developer
surely just write a dts that goes:
insert into tableA
select recordnames from tableB
???
hello sir
i am manoj
i am trying to fetch images form folder and image name in data base.
i want to all images in a table in 2 rows and 4 column.
plz help me
Hi
1.I want data to be inserted in T2 table from T1 table on
schedule basis.
2.Both tables are in different server and differe DB.
3.I have 19 clumn in T1 where as 20 columns in T2 the 20th column is addition in T2 which accepts NULL .
4 I dont want to alter T1 design.
5.Please can any body help me in transfering the data . I need to pass NULL value to the 20th column in T2 table while moving the data from T1
Thanks in advance………………:-)
Hello!
First off I want to say great example. Worked perfectly for my needs with little customization. How I’m wonder if something more is possible.
My working current code is (generalized for simplicity)
INSERT INTO db1.dbo.CommonTable
SELECT * FROM db2.dbo.CommonTable
WHERE my_column = 4
Is it possible that the new rows, inserted into db1, could have the ‘my_column’ value changed? Something like:
INSERT INTO db1.dbo.CommonTable
SELECT * FROM db2.dbo.CommonTable
WHERE my_column = 4
SET my_column = 99
Could anyone lend a hand?
@Mathew
This can be done like this… I am sure there must be a good way to do this…
Example :
create table example1 ( cola int, colb int, colc int)
create table example2 ( cola int, colb int, colc int)
insert into example1 values ( 2, 3, 45)
insert into example1 values ( 12, 13, 45)
insert into example1 values ( 112, 113, 45)
insert into example2 (cola , colb, colc ) select cola , colb , 99 from example1 where example1.colc = 45
select * from example1
select * from example2
Result :
Example1:
2 3 45
12 13 45
112 113 45
Example2 ( output)
2 3 99
12 13 99
112 113 99
Hope this helps.
Thanks,
Imran.
@ Imran Mohammed
That’s great, thank you very much. I realize the solution must include the specific column names along with the value I wish to substitute but is there a way to generalize the statement and only specify the column I want change?
The reason I’m hesitant about listing all columns is I’m trying to write a generic algorithm that works on 40 databases containing 60 tables with a varying number of columns.
In the meantime, I’ll try and incorporate the column names and that should work with the above code you gave me.
begin tran
insert into table1
select max(id)+1 as id ,’name’,’rollno’ from table1
hai dev
i have a problem in right outer join in below query
Select a.contact_id,a.contact_name as ‘Contact Name’,
SUBSTRING(a.contact_number,3,12 )as ‘Contact Number’,
Gender= case a.gender WHEN ‘m’ THEN ‘male’ WHEN ‘f’ THen ‘fem’ ELSE ‘who’ End,
c.m_group_id
From phone_book a with(nolock) ,User_Group_Members_Dets b with(nolock)
Full outer join User_Group_Dets c with (nolock)
on b.gid=c.gid
where a.user_id=922124
and a.active = 1
and b.status=1 and a.contact_id=b.contact_id and
b.gid = 67754 order by a.contact_name
here if anyrows of a,b are not maching with where clause
the right tale (c) are not getting
plz give me a suggesition
Hi… please help
how i can copy data from table wich different database where every database have same table name into one database where have same table
Hi,
Actually, there is a table called ‘Workgroup’ in Oracle Database in which the users will change their workgroups rarely.
There is a same table in ‘MS SQL Server’ Database.
I would like to bring the table from Oracle to MS SQL Server and whenever there is a data change (Workgroup change) in the Workgroup table in Oracle, data of the same table in MS SQL Server should also change.
Is there a way to do this?
Please help me. It’s urgent..
Hi murry,
murry you can use Import/export data to copy a data from one database to other.
OR
use this query
insert into databasename.dbo.tablename
from databasename.dbo.tablename
where (condition)
e.g
select lastname,firstname into pubs.dbo.test
from northwind.dbo.Employees
This query will create a test table and copy the data.
Regards,
Vinit Satam
hello sir,
i am new to sql server
so my problem is also little for you but i am confusing please answer
problem is,
i have one table with 10 records in it
when i insert or update records there is no problem,
but when i delete lets say row no 4 and..
then when i insert a new record it is inserted at 4th position and then the record are continued to be added from that position to downwards
and not at the end of table(means, not from last record onwards)
what happens and what is the solution???
A table named ’employees’ have the columns Emp_name, emp_no,age and the second table named ‘User’ have columns Emp_name,age. How to insert the values Emp_name, emp_no from the ’employees’ table into ‘User’ table using subqueries
@shameer
How can you insert data in the column when you dont have the column in the destination table.
Thanks,
Imran.
thanks a lot , it is very usefull for the requirement which i am trying on, can you tell me how can i configure the servers database in to the application
ya, nice and thanks
but when i entered a value in particular column in one table the next column will automatically inserts the value from another table which has the value at particular column
I tried that code in query manually but i need this code to be generate automatically when i enter a value in beside the column