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
Thanks so much, the first useful bulk insert article!
;)
Dear All,
can anybody have answer of how to swap data from one identity column to same column in sql server 2005 table
Hi,
I wanted to know the alter command to add a column in middle of other columns in existing table.
@Sneha.
I am not aware of any direct way of doing this.
You could do this from SQL Server Management studio easily, but if you see Generate Script for this, you would see that
1. Your original table is copied into a temp table,
2. Original table is dropped and
3. A new table is created with column added with the specified column location and
4. Then data is copied from temporary table to this new table.
5. And then name of this new table will be renamed to your original table.
~ IM.
i have to same database with exactly same tables!
i want to add data from DB A into DB B.
i want to check if the primary key of a table in DB A doesn’t exist on DB B insert that record else update that record!
help me !
@Ehsan.
What is the concern. You explained your scenario. But what is that you want from us.
You want us to write a script for you or are you looking for any solution from us on how you can implement your requirement.
Please briefly tell us what is that you want us to do ? what kind of help you are looking for.
~ IM
Hi Ehsan,
You can do this as:
–Insert code
INSERT INTO TableA(columns)
SELECT Columns
FROM TableB b
WHERE NOT EXISTS(
SELECT a.Id
FROM TableA a
WHERE a.ID = b.ID
)
–Update code
UPDATE TableA
SET Columns = b.Columns
FROM TableA a
INNER JOIN TableB b ON a.ID = b.ID
Let me know if you have any questions.
Tejas
SQLYoga
Great post !!!!
Hi Tejas Shah,
I have a few slow performing T-SQL cursor stored procedures that I want to replace with normal T-SQL (i.e. remove the cursors). Can you provide examples.
Thanks
Hi Clive,
First of you need to remove cursors.
What you need to do is:
Populate Table variable or Temp Table with the data, and then loop it thru as:
Please find: h ttp://www.sqlyoga.com/2009/12/sql-server-how-to-remove-cursor.html
Tejas
hi i want to ask one question. plz can u people tell me how to copy whole data from one server to another server using SQL both at diffrent places.
@sana
Quickest method is to use backup – restore. Now, it depends on your business requirements.
Hi all,
I am working to an c# win form application with sql server.
I have to table “father” and “child”, 1:n.
I am populating forms with data and after i have to transfer data into database. i have some text object for “father table” and one datagridview with more lines for “child table”.
I want to use a Sql Server procedure. How can I pass all informtion (text object and datagrid), as parameters, to the procedure?
Is it possible to use XML.
For populating the grid I use SqlDataAdapter and DataSet.
thanks in advance
tomi
Hi sana best is back up and restore wizard . either you can write a script
restore database
from
with replace
insert into Emp_Perform_SecA_Details(empcode,CycleStartDate) values (‘000740′,select settingvalue from applicationsettingmaster where settingname=’AppStartDate’)
it is getting error, what is the solution for that
Correct way is
insert into Emp_Perform_SecA_Details(empcode,CycleStartDate)
select ’000740′,settingvalue from applicationsettingmaster where settingname=’AppStartDate’
I want to select all from table A and select table B where A.id=B.id
like
table A
id=1
id=2
id=3
id=5
table B
id=2
id=3
id=4
out put should be
A B
1
2 2
3 3
5
can any one tell what the query is.
Hi,
I have one problem.. Problem is-
I want to insert records into one table say “NewCountry” from other “Country” table but this “Country” table is located at different MS-SQL server so how can i insert records?
Hi Mandar,
If you want to do only through t-sql query then either use OPENDATASOURCE function or create a linked server and then use OPENQUERY function or by 4 part naming.
If you are not sticked with T-SQL then better use import/export wizard and you can save it as a SSIS package for future use.
Regards,
Pinal Dave
Hi Sir,
Thanks for reply. Your articles are amazing.
Regards,
Mandar Kavishwar
Hey Pinal,
Its really grt blog. keep it up man…..
In my project I want to insert data to a table from another table in the database
I used query as follows
String st = “select code from state where name='” + this.statename.Text + “‘”;
String str = “insert into branch(Code,Name,metro,state)values (‘” + this.txtbranchcode.Text + “‘,'” + this.txtbranchname.Text + “‘,'” + this.cmbmetro.SelectedIndex + “‘,'” + st + “‘)”;
but compile time no error
at run time it is showing error as syntax error at Karnataka
here Karnataka means my statename.text
pls correct me error
Change: “‘,’” + st + “‘)”;
To: “‘,(” + st + “))”;
The SELECT statement needs to be in parenthesis itself.
Another way to do it would be to only use the SELECT statement, and not use the VALUES clause at all:
String str = “insert into branch(Code,Name,metro,state) select ‘” + this.txtbranchcode.Text + “‘,'” + this.txtbranchname.Text + “‘,'” + this.cmbmetro.SelectedIndex + “‘,'” + code from state where name='” + this.statename.Text + “‘”;