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
No help for my query?
It really is quite important that I get this to work. If anyone can help it would be aprreciated.
Post #69
Hi ,
I need following output. code should be in store procedure
column1 column2 column3
a a1 1
a a2 Yes
a a3 12
b b1 Null
b b2 65
b b3 67.7
There are two ways to specify the data values:
– Use a VALUES clause to specify the data values for one row:
INSERT INTO MyTable (PriKey, Description)
VALUES (123, ‘A description of part 123.’)
– Use a SELECT subquery to specify the data values for one or more rows.
INSERT INTO MyTable (PriKey, Description)
SELECT ForeignKey, Description
FROM SomeView
How to insert the old purge data from AA database to additional the BB archive database?
Thanks.
Hi,
I want to copy tables with structure, keys and indexes from one server to another server in sqlserver2005. I can’t use Generate script as it copies all tables. I just need to copy selected tables from source server to destination server which doesn’t exist in the destination server.
Eventhought i tried with the
SELECT *
INTO server1.db1. tblName
FROM server2.db1.othertblName
but it doesn’t copy the indexes and keys.
Please send me query .
Thanks
Hi,
I have SQL Server table with TimeStamp column. I canno insert values into this field. When I try to
Insert values into this field it says the followin error.
“Server: Msg 273, Level 16, State 1, Line 1
Cannot insert a non-null value into a timestamp column. Use INSERT with a column list or with a
default of NULL for the timestamp column.”
Kindly let me know how to insert values into this field.
Thanks in Advance,
Wellsgano
Help please..?
i tried to code that enable to transfer from the data in my local drive database to the network database to the network database updated but i got error i used this statement below..
please help me please..?
here is my codes….
Dim conn As ADODB.Connection
Dim SQL As String
conn = New ADODB.Connection
conn.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\pc1TSmobileDataCopyTSMobile.mdb;Persist Security Info=False;Jet OLEDB:Database Password=h1lt1”
conn.Open()
SQL = “Select into CopyTSMobile.dbo.TTMtransaction” & _
“Select FROM (’Provider=Microsoft.Jet.OLEDB.4.0;’,’Data Source=c:TSMobile.mdb;’,’User Id=;’,’Password=h1lt1;’,’SELECT FROM TTMtransaction where sync=0’)”
conn.Execute(SQL)
conn = Nothing
please help please how can i fix this error..
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
your posting its very helpful but still i am not getting solution for my problem is that “to transfer a db table (with data) from one database to another in different server?”
I have a Table A and Table B if i insert a record in Table A, a message box will shown in Table B that “In table A a new records is inserted”. i need a query for this.. thanks in advance..
Your site is very clean, and easily readable. Thanks for your generous help. you’re the best!
I have one table say T1 with 5000000 records. I need to insert in this record in another table say T2. I have written
Insert into T2
Select * from T1
But it is taking very long time. also after executing for 10 min its giving Disk full error as there is hugh disk space.
help me out…
i need to copy a table from one server to another server.
both seevers reside on different different machines.
i think “select * into” will work but i dont know the actual syntex of it…
Thanks all for such a good posts
Anand
You can do this using this by connect to one server and Right click on the databasen->select Task->Import/Eport option.
try this.
INSERT INTO TABLE 2(SELECT * FROM TABLE 1);
Works great on an Oracle DB.
dear sir
i have a question
can i copy one table to another table in same database and before inserting in second table i want to fire one trigger is it possible if yes plz give me code
ex
one table is employee
and second table is company
and one trigger
i want to copy employee in company before copying in company trigger will fire and if any error then it will give u in the error message table
plz send me thanks in advance
How to insert data(only one column) from source table to another table
its very urgent need please send me the query.
hello dev,
i have two tables ticket and account.
In ticket table i have two columns ticketid, balance
In account table i have three columns acid,balance,ticketid
when i insert data in ticket table, automatically the balance in account table should be inserted.
how to write a trigger for this in sql 2005
@pavani.
CREATE TABLE TICKET (TICKETID INT, BALANCE MONEY)
CREATE TABLE ACCOUNT(ACID INT IDENTITY, BALANCE MONEY,TICKETID INT)
Description:I am creating two tables ticket and account as you described in your question.
CREATE TRIGGER TR_INSERT
ON TICKET
FOR INSERT — Remember this trigger is only for insert
AS
BEGIN
DECLARE @TICKETID INT
DECLARE @BALANCE MONEY
SELECT @TICKETID = TICKETID FROM INSERTED
SELECT @BALANCE = BALANCE FROM INSERTED
INSERT INTO ACCOUNT (TICKETID , BALANCE) SELECT @TICKETID , @BALANCE
END
then I created a trigger which will insert same data in accounts table in columns ticketid and balance, when ever you try to insert data in ticket table.
here is an example.
INSERT INTO TICKET VALUES ( 2 , 300)
SELECT * FROM TICKET
SELECT * FROM ACCOUNT
Hope this helps, I am not sure if this what you were looking at, Your decsription was not enough, so what ever I undertsood I tried to implement.
Thanks,
good,
Hello sir,
I want a I want update a recored from another table at once using a select query. So please tell me query