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
@ Joji
The best way to copy a table with its indexes, defaults, constraints, triggers and all other stuff related to a table is …
Script the table :
1. In object explorer, right click on the table and then select SCRIPT TABLE AS : CREATE TO
REMEMBER: WHEN YOU TRY TO SCRIPT IN SQL SERVER 2000, make sure you uncheck the option DROP if exists. IF THIS IS CHECKED then you will loose your original table. ( If you run the script in the QA in the same database )
When you get the script, then copy the script and run in Query Analyzer (2000) or SSMS connecting to the database in which you want to create the table.
once you run the script then you will have your table ready with all your constraints, indexes, defaults, triggers….
BUT after table is created, you will NOT use SELECT * INTO statement, now you will use
INSERT INTO new_table_name SELECT * FROM old_table_name
Hope this helps.
@ Mark
This is what I understood, please correct me if I am wrong …
Your explanation gives answer for your question : You said you are trying to update the data in table dbo.sim_control_table, but you know what, you are trying to insert the data when you want to update it.
Solution would be : Dont use Insert statement, use update statement, your problem will be solved.
This is a small example:
CREATE TABLE UEXAMPLE1 ( EID INT, EID1 INT , EID3 INT )
CREATE TABLE UEXAMPLE2 ( EID INT, EID1 INT , EID3 INT )
-> I created two tables which are almost same, This is what you did, two tables with different names but they have same column names and that too in the same order :)
INSERT INTO UEXAMPLE2 VALUES ( 1,2,3)
INSERT INTO UEXAMPLE2 VALUES ( 2,2,3)
INSERT INTO UEXAMPLE2 VALUES ( 3,2,3)
-> I inserted three rows in Uexample2
SELECT * FROM UEXAMPLE2
-> checking values using select statement.
INSERT INTO UEXAMPLE1 VALUES ( 1,3,4)
INSERT INTO UEXAMPLE1 VALUES ( 2,4,4)
INSERT INTO UEXAMPLE1 VALUES ( 3,4,5)
-> Now insert three rows in Uexample1, I wantedly inserted different data in this table, just to distuinguish… but you can see my first column in the two tables its exactly same. This is what you did :)
SELECT * FROM UEXAMPLE1
-> Checking the values in Uexample1.
INSERT INTO UEXAMPLE1 (EID1, EID3)
SELECT EID1 , EID3
FROM UEXAMPLE2
WHERE UEXAMPLE1.EID = UEXAMPLE2.EID
-> This is your T-SQL statement, I did the same thing, I am trying to update the value in Uexample1 from Uexample2 using my first column as macthing, this is what you did in your case.
Know what : I got the same error :)
Error: Msg 4104, Level 16, State 1, Line 2
The multi-part identifier “Uexample1.eid” could not be bound.
SOLUTION : Now let us try update for the same table.
T-SQL code will be changed slightly,
UPDATE UEXAMPLE1
SET UEXAMPLE1.EID1 = UEXAMPLE2.EID1 , UEXAMPLE1.EID3 = UEXAMPLE2.EID3
FROM UEXAMPLE2
JOIN UEXAMPLE1 ON UEXAMPLE1.EID = UEXAMPLE2.EID
-> Instead of INSERT we will use UPDATE and instead of WHERE CLAUSE we will use JOIN CLAUSE.
Check the output.
@ J.Marchena
I tried working on your problem… I dont know how to implement this in Access 2007. I tried in SQL Server 2005.. same applies to SQL Server 2000 too.
I will explain my logic and I will ask the question I have .. because I got some errors while executing quiries…
This is what I did in this example :
1. I create three tables, by name: Uexample1, Uexample2, Uexample3
CREATE TABLE UEXAMPLE1 (EID INT, STATE CHAR(2))
CREATE TABLE UEXAMPLE2 (EID INT, STATE CHAR(2))
CREATE TABLE UEXAMPLE3 (EID INT, STATE CHAR(2))
Same tables with different names.
2. Now I created a trigger on the table UEXAMPLE1. This triggers inserts data in UEXAMPLE2 and UEXAMPLE3 tables when ever you try to insert the data in UEXAMPLE1 table.
CREATE TRIGGER TR_UEXAMPLE1
ON UEXAMPLE1
FOR INSERT
AS
DECLARE @EID INT
DECLARE @STATE CHAR(2)
SELECT @EID= EID, @STATE= STATE FROM INSERTED
INSERT INTO UEXAMPLE2 VALUES (@EID , @STATE)
INSERT INTO UEXAMPLE3 VALUES (@EID , @STATE)
This could be one solution when you insert data in UEXAMPLE1 and you want that data in UEXAMPLE2 and UEXAMPLE3.
But that is not your concern, you said you are trying to copy the data, which means you will not insert data in UEXAMPLE1.
For that we will try the same logic, but this time we will create the trigger on UEXAMPLE2, when ever a row is inserted ( while copying fromUEXAMple1) into UEXAMPLE2 this trigger will insert the same row in UEXAMPLE3
CREATE TRIGGER TR_UEXAMPLE2
ON UEXAMPLE2
FOR INSERT
AS
DECLARE @EID INT
DECLARE @STATE CHAR(2)
SELECT @EID= EID, @STATE= STATE FROM INSERTED
INSERT INTO UEXAMPLE3 VALUES (@EID , @STATE)
So you dont have to write two quiries to copy the data into two tables.
There is one more way you can do this … you can create a view on either UEXAMPLE1 or UEXAMPLE2 and create a INSTEAD OF trigger on any one of the view, will do the same thing.
ISSUES: I am not genius… no where near .. while doing the above examples… I got many errors…
1. like when I use INSTEAD OF trigger on Uexample2, the data doesn;t go in the table UEXAMPLE2 … but it is directly goes to UEXAMPLE3. This is strange.
2. When I execute :
INSERT INTO UEXAMPLE2 SELECT * FROM UEXAMPLE1
and I have trigger on UEXAMPLE2 which inserts records in UEXAMPLE3… I saw that only one row is copied in UEXAMPLE3 but all the rows have been copied in UEXAMPLE2… This is also strange to me … ?
please clarify … I spent enough amount of time on this !
@ viswa (38)
This could be one solution … like you said I used cursor in this … but not sure if there is still any better solution for this …
This is the example :
CREATE TABLE UEXAMPLE1 ( COLA INT, COLB INT )
CREATE TABLE UEXAMPLE2 (COLA INT , COLB INT, COLC INT, COLD INT)
-> created two tables, UExample1 and UExample2 , they have 2 columns same between them, UExample2 has two extra columns.
INSERT INTO UEXAMPLE1 VALUES ( 1 , 123)
INSERT INTO UEXAMPLE1 VALUES ( 2 , 223)
INSERT INTO UEXAMPLE1 VALUES ( 3 , 323)
-> I inserted three rows in UExample1. Three differenrt rows.
INSERT INTO UEXAMPLE2 VALUES ( 1, 244 , 333 , 444)
INSERT INTO UEXAMPLE2 VALUES ( 2, 244 , 333 , 444)
INSERT INTO UEXAMPLE2 VALUES ( 3, 344 , 333 , 444)
INSERT INTO UEXAMPLE2 VALUES ( 4, 444 , 333 , 444)
-> Then I inserted four Different rows in UExample2, first column in both the tables is common and has three different values. This is to create your condition… if it exisits then update if not Dont…. So there are three rows in Uexample1 which exists in UExample2.
CREATE PROC USP_UPDATE
AS
DECLARE @COL INT
DECLARE @COLA CURSOR
SET @COLA = CURSOR FOR
SELECT COLA
FROM UEXAMPLE1
OPEN @COLA
FETCH NEXT
FROM @COLA INTO @COL
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS (SELECT COLA FROM UEXAMPLE2 WHERE COLA= @COL)
UPDATE UEXAMPLE2
SET UEXAMPLE2.COLB = UEXAMPLE1.COLB FROM UEXAMPLE1 WHERE UEXAMPLE1.COLA= @COL AND UEXAMPLE2.COLA= @COL
ELSE PRINT convert(varchar(10), @col )+ ‘ DOES NOT EXISITS IN UEXAMPLE2’
FROM @COLA INTO @COL
END
CLOSE @COLA
DEALLOCATE @COLA
GO
-> I create a stored Procedure which is checking if there is any row in cola in Uexample2 which exists in UExample1.
If yes then It is updating that colmn with the value of UExample1.
If not then it is printing … it does not exists in UEXAMPLE2.
Hope this helps … I am not sure if this is a good solution, since I used Cursor in this …
hello, friends,
I want to insert data into one table(save_documen_v) from another table(document_v).
i have 3 more columns r there in save_document_v then document_v.other columns r same.
i want to insert record of that different column from outside n in the same row i want to insert selected record from document_v table.
if i pass the query–
insert into save_document_v
(member_code,class_no) values (1102,(select
class_no from document_v where (acc_no=’D 1173′)))
then it works.
but when i m trying to insert into more columns like-
insert into save_document_v
(member_code,class_no,acc_no) values (1102,(select
class_no,acc_no from document_v where (acc_no=’D 1173′)))
it not work.
i have 16 columns same in both table.
n 3 more columns in save_document_v(member_code,m_type,display)
if any one has solution plz give me.
thanks.
hi,
related to my previous post..
if in that problem i write this query-
insert into save_document_v (member_code,m_type,display,class_no,acc_no,
author,author_2,title,yr,imprint,guide_1,guide_2,
place,source,clnt_spon,discipline,type,remarks,abstract)
values (1101,’document’,’yes’,(select class_no,acc_no,author,author_2,title,yr,imprint,
guide_1,guide_2,place,source,clnt_spon,discipline,type,
remarks,abstract from document_v where acc_no=’D 1173′))
its give error–
ERROR at line 4:
ORA-00947: not enough values
You know what I would love to figure out is how to write an “execute select” statement. In other words,
execute dbo.doSomething @param1, @param2, @param3
select column1, column2, column3
from table
such that the value of column1 is passed to @param1.
Right now, I write something like the following:
for scripts
select ‘execute dbo.doSomething ”’ + column1 + ”’, ”’ + column2 + ”’, ”’ + column3 + ””
from table
for stored procedures
select @value1 = column1, @value2 = column2, @value3 = column3
from table
execute dbo.doSomething @value1, @value2, @value3
It gets the job done, but I’m *convinced* that there’s a better way I am overlooking. Any suggestions on where to start reading?
Thanks!
@ Kruti
This is what I understood, please correct me if I am wrong,
You have two tables, lets say UEXAMPLE1 and UEXAMPLE2, one of the two tables has 3 extra columns, 16 columns are same in both the tables.
Here is the example …
CREATE TABLE UEXAMPLE1 ( EID1 INT , EID2 INT ,EID3 INT )
CREATE TABLE UEXAMPLE2 ( EID1 INT , EID2 INT , EID3 INT , EID4 INT , EID5 INT)
–> I created two examples UEXAMPLE1 and UEXAMPLE2. In these UEXAMPLE1 has only 3 columns and UEXAMPLE2 has 5 columns out of these 5 columns in second table, three columns are common for both the tables.
INSERT INTO UEXAMPLE2 VALUES ( 1, 2, 3, 4, 5 )
INSERT INTO UEXAMPLE2 VALUES ( 11, 12, 13, 14, 15 )
INSERT INTO UEXAMPLE2 VALUES ( 21, 22, 23, 24, 25 )
INSERT INTO UEXAMPLE2 VALUES ( 31, 32, 33, 34, 35 )
INSERT INTO UEXAMPLE2 VALUES ( 41, 42, 43, 44, 45 )
–> Inserted values in the second table.
Problem : Now when you said you are trying to insert the data from second table into the first table. Your query works fine when you select one column from second table but when you select two columns your query does not works,
Solution : The problem is you cannot use one select statement in that way for more than one column. I am not sure but if you write select query for every column seperated by a comma then hope that will work
like this ( I did not execute this query, I assume this will work ) :
INSERT INTO SAVE_DOCUMENT_V (MEMBER_CODE,CLASS_NO,ACC_NO)
VALUES (1102,(SELECT CLASS_NO FROM DOCUMENT_V WHERE (ACC_NO=’D 1173′)), (SELECT ACC_NO FROM DOCUMENT_V WHERE (ACC_NO=’D 1173′)))
My solution :
For your first query this could be a replacement :
INSERT INTO
UEXAMPLE1 (EID1 , EID2)
SELECT ’10’, EID2
FROM
UEXAMPLE2
WHERE
EID5 = ’15’
Explanation : Here you are using a select statement and you are passing the value which you want to insert in the first column and in the second column you are copying the value from second table by giving a where clause.
For your second query this could be repalcement :
INSERT INTO
UEXAMPLE1 (EID1 , EID2, EID3)
SELECT ’20’, EID2, EID3
FROM
UEXAMPLE2
WHERE
EID5 = ’25’
Explanation : Here you are using a select statement and you are passing the value which you want to insert in the first column and in the second column and third column you are copying the value from second table by giving a where clause.
Hope this helps…
Hi
I wondered if anyone could help me?
Pinal’s blogs have proved very useful so far but I have been struggling to do something in sql server 2005 now for over a week. I’m positive it is something quite simple yet I am a novice at sqlserver and it is making me lose the will to live.
OK
My overall aim is to have two versions of the same database (AdventureWorks) on the same server so that I can run a series of tests using various sql comparison tools and compile a report based on my findings.
Using Pinal’s blog I have so far managed to copy what appears to be a skeleton of the DB. I.e. all of the dbo’s have been successfully copied but I cannot seem to get any data to copy into these tables.
As I said above, I am a novice at SQL and SQL Server so if the answer is obvious please show a little patience.
If anyone can help with getting the data into the tables or suggest a whole new approach which will enable me to have two instances of the same database on the same server I would be much obliged.
The renaming of databases was a whole other kettle of fish over which I effectively wasted 2 days work.
I’m sure this is a simple thing to do but I feel I have exhausted all obvious avenues.
regards
Jobby
hi sir ,
can u pls help me???
how can i retrieve all the data from the db row wise to my asp page table???
INSERT INTO DestinationDatabase.dbo.DestinationTable (FIELDS Name)
SELECT FIELDS NAME
FROM SourceDatabase.dbo.SourceTable
khub saras kaam chhe. mane khub gamyu….!!! tame brahman bhai lago chho…!!!! aatlu saras parinam aapva badal khub khub abhinandan…!
can u tell me how can I copy data from one database table to another database same table… ???
can u tell the types of trigger..
@Priya :
This is what I know, please correct me if I am wrong
There are basically two types of triggers in sql server 2005 (DML & DDL) and only one type (DML) in sql server 2000.
Sql Server 2000.
1. DML Triggers: Again in this we have two more types of triggers,
a) ” INSTEAD OF ” TRIGGERS
b) ” FOR ” or ” AFTER ” TRIGGERS
a) INSTEAD OF : Again in this we have three types of triggers,
1. DELETE
2. INSERT
3. UPDATE
b) ” FOR ” OR “AFTER” TRIGGERS: Again we have three types of triggers just like “INSTEAD OF”
1. DELETE
2. INSERT
3. UPDATE
These are total six types of triggers available in Sql server 2000. These triggers gets executed when ever you insert, update or delete.
Now in sql server 2005, they have created one more Trigger named DML, these triggers gets executed when you execute any DML : create , alter, drop statements.
DDL Triggers: We have many triggers under this category available for all kind of DDL statements, to get more information you can refer to books online… the list is very big…..
Please refer to this example to know more abotut DDL triggers,
———————————————————————
I didn;t know anything about Triggers but after reading this article, I know something about triggers, now I write triggers just based on this knowledge… This article is really really good… very easy to understand : to know more about DML Triggers please follow this example,
—————————
Hope this helps
Thanks,
Imran.
Sql Authority is really help me to know different type of query mistry.
In the previous post, there are some mistakes.
By mistake I wrote DML triggers in sqlserver 2005 , it is DDL ( Data Definition Language) – Create , Alter Drop.
2000 – only DML ( Data manupulation Languae) – insert , update , delete.
2005 – DDL and DML.
Hi, I have a query
i have 2 databases ‘A’ and ‘B’ both SQL SERVER 2005,
and i want at such a thing that at some specefied time everyday say (22:00 hours) data to be selected from few columns from table in databse ‘A’ and the to be inserted into specified the table in database ‘B’.
and this process should run self that is self executing.
is this possible
i have one prob is that
i want to select the particualr row from sql database & insert into ms access database how it is possible
how can i write the query in c# appliaction
plz help
i have created student table where student_id is the primary key for that table. another table is logbook_entries where student_id is a primary key and it also a foreign key where it refers student_id in student table. i do not how to retrieve the student_id to logbook_entries from student table.
how can i write the query.
plz help me out.
hi,
i want to write trigger on table of one database for insert which insert that record into table of other database……..