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
Hi Dave,
How do you insert data from text box into table?
Regards,
Zulfi
@Zulfi Asdani
You can do it in two ways,
1. Use bulk copy command from command prompt or sql server and import that data from text into sql server.
2. create a dts package, select source as text file and destination as SQL Server and you can import all data into sql server from text file ( very easy).
Try looking on internet you can find many examples.
Hope this helps,
Imran.
how to run a insert query so as to insert a column from remote server to local DB, this column contains some sql queries so these queries should also get executed while inserting the column.
Hi,
have 3 tables in one server database and want to copy that data in to my Local database (b’cos I want to work with that local data with out effect that original data)
Please help me.
@Anu
You can use SSIS or DTS to do that.
IF you are using SQL server 2005, right click the database–>Task–>Export. Then everything is straigth forward from there on wards.
hope this helps
Hello
I am very new to write SP,so need guidance in doing the following.
I need to write a stored procedure,which will insert data into 3 tables.
These are the 3 table
Table-TOCHeaderSchema
Columns-TOCHeaderSchemaID
-TocHeaderScemaNameText
-CreatedByLID
Table-TOCHSElement
Columns-TOCHSElementID
-TOCHSLevel
-TOCHSPrefixText
-TOCHSCounterStyleID
-TOCHeaderSchemaID
Table-TOCHSCounterStyle
Columns-TOCHSCounterStyleID
-TOCHSCounterNumber—-this is autogenerated
-TOCHSCounterLabelText
Basically these tables are changing the numbering of elements.
like for example.
this is the numbering of a document named Transportation TP 1.1.1
so this will be changed to 1.A.1 or 1.a.1 depending upon the choice.
so the table TOCHSCounterStyle is like
TOCHSCounterStyleID —1
TOCHSCounterNumber—-1,2,3,4,5(autogenerated)
TOCHSCounterLabelText—-A,B,C,D,E
TOCHSCounterStyleID —2
TOCHSCounterNumber—-1,2,3,4,5(autogenerated)
TOCHSCounterLabelText—-a,b,c,d
TOCHSCounterStyleID —3
TOCHSCounterNumber—-1,2,3,4,5(autogenerated)
TOCHSCounterLabelText—-i,ii,iii,iv,v…
Thank You
@ blue star
Need more information ….
Its really confusing !!! Please provide more information with input and how you want your output to be.
Thanks
Imran.
Hello Pinal Dave,
i have a problem regarding data transfer.
The scenario is there are 2 database DB1 and DB2.
Both the database having a table TAB1 with same structure.
Now when data is inserting into DB..TAB1 we are firing a Trigger TGR1 to insert the same data into DB2..TAB1. But the concern is on performance…
So is there any other way to insert the data in DB2..TAB1 after inserting into DB1.TAB1?????
If is there any error while inserting into DB2..TAB1, trasaction under Trigger will be ROLLBACKED.
plz help soon its urgent.
Thanks in advance
Hi , thank for the script in your document, very useful.
My friend and i tried it, and did work but the next thing we want to do was to add a new column to the table having received the data, and update that column the same way as in the insert into , but it happens that the data are appended from the last row of the table , leaving the two initials table blank.
That the script:
//Create a new Table
create table Info
(FirstName VARCHAR(100), LastName VARCHAR(100))
//Inserting the data
INSERT INTO Youssef(FirstName, LastName)
SELECT FirstName, LastName
FROM AdventureWorks.Person.Contact
//Altering the Table
alter table Info
add Email VARCHAR(100)
Here is where i get stack, it update from the last row leaving the Firstname and Lastname blank
update Info
set Email = (SELECT EmailAddress
FROM AdventureWorks.Person.Contact)
i don’t want to use a procedure and looping construct.
thanks
@@Imran
How Can I copy my local sql server 2005 table’s data to my host database?
It would be helpfull if you can provide me the code.
Currently im doing this by using Import/Export Wizard and its a very long process.
Thanks in Advance!!
Hello Dave,
I’m having two tables
Table1: Table2:
ID Date Month Jan Feb Mar Remarks
1 1 Jan xx xx xx xxxxx
2 14 Jan xx xx xx xxxxx
3 26 Jan xx xx xx xxxxx
4 14 Feb xx xx xx xxxxx
5 13 Mar
I want distinct values of Month in Table1 to be the Column Name of Table2 as above said:
can you help me out
Thanks & Regards
Surendar K
Hi,
I need a help
I had a Query that when we delete the data from any table
from a particular database in SQL Server 2005, the deleted data has to insert in the DeleteLogTable, using triggers
DeleteLogtable Columns
TableName ColumnName DeletedValue
Plz solve my problem.
Very usefull Notes
Hi Dave,
I have 2 tables as @temp1 and @temp2 such as
@temp2
id name
1 pavan
2 raj
2 kumar
2 pav
1 tamma
and @temp1
id city
1 nagpoor
2 poona
and want new table @result as
id city name
1 nagpoor pavan,tamma
2 poona raj,kumar,pav
please tell me how to do this with stored procedure or UDF
Hi,
pavan mainde
I hope this query may help u to get ur output.
Select T2.Id, T2.Name, T1.City from @Temp1 T1, @Temp2 T2
where T1.Id= T2.ID
after about 5 years of writing database applications , i am still confused with insert with select!!
Hi,
I need a help
I had a Query that when we delete the data from any table
from a particular database in SQL Server 2005, the deleted data has to insert in the DeleteLogTable, using triggers
DeleteLogtable Columns
TableName ColumnName DeletedValue
Plz solve my problem.
Hi Dave
I have to move some tables from one databes to another on the same SQL server 2005.
Actually I have to do this a few times, move it from DB1 to DB2, from DB2 to DB3, from DB3 to DB4…, all of them on the same server.
The respective databases and tables have different names.
In fact only the beginings or the endings of the names are different.
I was thinking to this with INSERT INTO – SELECT FROM
statements. I have moved data from DB1 to DB2, that was no problem. To move it again I have to write new script with the new names.
Can I somehow use one script, where I can do something about the table and database names. The manes of the columns are all the same in all databases.
Can you please help me. I have about 50 tables. How can I automate this procces?
Should I use BCP instead?
I would really appriciate your help.
Grateful M
could u pls guys help me with a insert procedure that will allow 2 records to be inserted inone column
Hi Dave,
i am asking this query from 29 Sep 2008.
Plz help me to solve the problem.
I need a help
I had a Query that when we delete the data from any table
from a particular database in SQL Server 2005, the deleted data has to insert in the DeleteLogTable, using triggers
DeleteLogtable Columns
TableName ! ColumnName ! DeletedValue
Plz solve my problem.
If guys any one has any idea on this plz give me the reply.