SQL SERVER - Insert Data From One Table to Another Table - INSERT INTO SELECT - SELECT INTO TABLE

August 15, 2007 by pinaldave

Following three questions are many time 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 anther 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 method over cursor. Performance of following two methods is far superior over cursor. I prefer to use Method 1 always as I works in all the case.

Method 1 : INSERT INTO SELECT
This method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are are not required to list them. I always list them for readability and scalability purpose.

USE AdventureWorks
GO
    ----Create TestTable
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 table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.

USE AdventureWorks
GO
    ----Create 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 (http://www.SQLAuthority.com)

Posted in DBA, Pinal Dave, SQL, SQL Authority, SQL Documentation, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, T SQL, Technology | 161 Comments

161 Responses to “SQL SERVER - Insert Data From One Table to Another Table - INSERT INTO SELECT - SELECT INTO TABLE”

on September 4, 2007 at 6:03 am Michael Rapko

Can you tell me how to use the INSERT INTO IN clause to transfer data from a table in MS SQLServer DB into a table in a MS Access DB?
on September 7, 2007 at 12:00 am Soujanya

How to use the Insert into select along with direct data coming from the form in SQl Server 2005?
on September 11, 2007 at 12:43 pm roopesh

thanks for your posting its really helpful do u know how to transfer a db table (with data) from one database to another in different server?
on September 17, 2007 at 11:18 am devan

Hi Dave,

I’m trying to move data between tables of 2 different dB’s on same machine (SQL Server2005).

Exactly trying to move Col1, col2 of Table1 of db1 to Col3 and Col4 of Table2 of db2.

Is it possible only by writing SQL scripts, if so can suggests me some links regarding this?

thx.
on September 19, 2007 at 11:18 pm karthi

Hi devan

select * into Northwind.dbo.employee from pubs.dbo.employee

I think this would work for u

thx,
Karthi.
on September 20, 2007 at 7:47 am Sriram

Hello sir, I want the producure to transfer the data from one to another……….

Plz send the query

Regrads
Sriram
on September 23, 2007 at 3:51 am jiayi

Hi dave,

thanks for posting such wonderful posts.

I’m trying to do a recursive loop searching for a particular parent in a tree, and retrieve all children under it.

Problem: While Exists(Select deptid from global_Master_Directory Where parentid IN (@ParentID) ) not being looped.

Please kindly guide me on this. thanks again.

Stored Procedure:

CREATE PROCEDURE procRetrieveDivUsers
( @divisionId varchar(10))
AS
– Create a variable table. A temporary table starts with #
declare @TableVar table
(
tempPK int identity(1,1),
costcenter varchar(6) NOT NULL ,
deptid varchar(6)
)
— declare and Initalize the variables

Declare @ParentID VARCHAR(1024), @deptID varchar(1024), @costcenter varchar(1024)

SET @ParentID = @divisionId

–Loop through - problem occurs here.. no looping occured. I tried printing out @parentID, it does return –value but when not doing looping. the while loop does work if i do a hard code value replacing @parentID
While Exists(Select deptid from global_Master_Directory Where parentid IN (@ParentID) )
Begin

INSERT INTO @TableVar (costcenter, deptid)
( SELECT costcenter,deptid FROM global_Master_Directory Where ParentID IN (@ParentID) )

SELECT @deptID =COALESCE(@deptID + ‘,’, ”) + deptid FROM global_Master_Directory Where ParentID IN (@ParentID)

SELECT @ParentID = @deptID –SELECT

End

SELECT distinct COALESCE(@costcenter + ‘,’, ”) + costcenter FROM @TableVar

GO
on October 1, 2007 at 12:19 am avishek joardar

hi Dave,
How to retrieve data from a table by row number of that table ?
(addittional information: suppose there is a table, and you don’t know any information except the attribute of that particular table, you get a request that you have to retrieve data of 31st no. row.)
on October 24, 2007 at 3:19 pm Paul

Dave,
What is the easiest way to insert data from one table where the data in the first table doesn’t exist in the second?

Table1 Table2
a a
b b
c

Example: In the above table I want to insert ONLY the row containing the value c into table2.

Thanks in advance for your help!
on October 26, 2007 at 10:44 am Daveg

Paul (9): I did it like this:

INSERT INTO MRA..T_MRA_MBR (MBR_ID, NCPDP_PRVDR_NUM)
SELECT MBR_ID, NCPDP_PRVDR_NUM
FROM P350..T_AH_MBR
WHERE MBR_ACTV_IND = ‘Y’ AND MBR_ID NOT IN (SELECT MBR_ID FROM MRA..T_MRA_MBR)

‘MRA’ is the destination DB, and I only want to insert new records that entered the P350 DB as a result of the previous day’s INSERTS. MBR_ID is the primary key in both. MBR_ACTV_IND is a WHERE clause that’s only pertinent to my code - yours will assuredly be different.
on October 29, 2007 at 12:43 am Golkar

Hello,
How Can I copy my local sql server 2005 table’s data to my host database?
Thanks
on November 1, 2007 at 3:12 am rajeswari

hello

i want to insert only the time into my table. or else how can i differentiate date and time. here i have field logout_time, its data type is date time. here i have to check if the column is empty i have to raise a exception. because while inserting first into the table the logout_time field inserting as empty string, later it will be updated. but its taking the default system date. i don’t want that i want the entire column should be empty. please do help me
on November 7, 2007 at 1:06 pm ndindi

Hi man

I need help, I’ve been working on this for a weeks now. I have created a Job in SQL Server Agent. The job is is executing fine but the my SQL Statement in the Command block is not correct.

I’m using SQL Server 2005, I need to import an Access Database to SQL server automatically every morning, and Delete the old data before Inserting new data.

Can you please help me with the SQL statement to do this job.

Ndindi
on November 8, 2007 at 9:28 pm can help me

Hi, i have code of following:

drop database if exists company;
drop table if exists location;
drop table if exists country;
drop table if exists region;

create database if not exists company;
use company;

/*
create the table named region
*/
create table if not exists region
(
region_id varchar(20) not null ,

region_name varchar(32) not null,

constraint region_PK primary key (region_id)

) ;

/*
create the table named country
*/

create table if not exists country
(

country_id int(20) not null,
country_name varchar(32) not null,

region_id varchar(20) not null ,

constraint country_id_PK primary key (country_id),

constraint region_id_fk foreign key (region_id)
references region(region_id)
) ;

Now i want to use command insert into to add values into tables. But I dont know how to do to add values foreign key region_id in country table which can link wich data from table region. Anyone can help me? Thanks you so much.

note: If you can show me your work clearly. Thanks
on November 14, 2007 at 12:55 pm Dhirendra kumar

hi pinal
i just want to know the command

what is command to populate data from one table to another table

pls help me out

Regards
Dhirendra
mumbai
on November 19, 2007 at 2:33 pm surender

i want to import the data into a table from other table which is in different Database please help me out
on November 19, 2007 at 4:04 pm Rejeesh

on November 19, 2007 at 2:33 pm16 surender
i want to import the data into a table from other table which is in different Database please help me out

SELECT * INTO rejeesh.dbo.authors FROM pubs.dbo.authors
on November 20, 2007 at 5:14 am Liz

Hi ,
You can use the Data Transformation services(DTS) to import the data from one table to another table!
use import/export wizard and create a package to do so.
on November 27, 2007 at 8:16 pm Mushtaque

Hi,
I want to insert data from one server database table
to another server database table in vb6 code
any one can help me
on December 6, 2007 at 7:34 pm jigar

hi deven,

i have same issue as u have disscussed here.

but i already have the SP for the selection of data.

now i have to insert that same data in to table which i m getting through tht SP.

so can i pass this sp to the insert query as you have passed select query to the insert query.

i m getting cofused in it.

Please reply me.
its urgent.

Waiting for your reply.
Thanking You.

Regards,
jigar
on December 12, 2007 at 9:15 pm Hello123

if (user != null)
{
if (user.IsApproved == false || user.IsLockedOut == true)
{
SecurityEvent evt = new SecurityEvent(”The user (” + Login1.UserName + “) is locked out, but has tried to authenticate.”,
this, SecurityEventCodes.EVENT_CODE_LOGIN_ATTEMPT_BY_LOCKED_USER);
evt.Raise();
Login1.FailureText = “Your account is locked. Please contact administrator.”;
}
}
on December 17, 2007 at 7:43 pm manvendra Singh

hi dave ,

I want to insert data in a table located in one database ,
by taking the data from another database —– In DB2.
both database are on different machine.

Please respond ASAP.
on December 20, 2007 at 6:53 pm Dave Suresh

Hi Dave,

I have two differne tables and Table A has around 1000 records.
Table B has around 1500 records

Both tables have a common field (Customer_code).
And few Customer_Codes which are there in the Table A are not there in the Table B and few Custotomer Codes which are there in the Table B are not there in the Table A.
Both the Table have another Column called Quantity.

Now i want to bring Customer_code from any one of the table, Quantity of the Table A and Quantity of table B to a temporary table.

Can u help me with the query where the sum of quantity should not change even after bringing to the temp table.
on December 21, 2007 at 6:26 pm Vishakha

Hi,
I want to insert using SELECT statement but select statement will be only for one column for rest of the coulmns fixed data will be innserted.

like

Insert into table EmployeeInfo(Id,firstname,lastname, Salary) values(1,’aaa’,xyz’, Select salary from Payroll_Info where EmpId = 1)

It is giving me error like
Incorrect syntax near the keyword ’select’.

Can you please help me in this regard.

Thanks,
Vishakha
on December 21, 2007 at 9:09 pm Miguelgon

HOW TO INSERT VALUES FROM ONE TABLE TO ANOTHER AND ADD THE TIME (GETDATE) AFTER A FORM SEARCH WITH FRONT PAGE
on December 22, 2007 at 2:14 am Anand

Hi Suresh(23),

change table names it should work for you.

select t1,sum(qty)
from
(select t1,sum(qty) qty from t1
group by t1
union all
select t1,sum(qty) qty from t2
group by t1
) A
group by t1

cheers,
anand.
on December 22, 2007 at 2:19 am Anand

Hi suresh,

more details if you are expecting below result:

select t1,sum(t1_qty1) t1_qty,sum(t2_qty2) t2_qty
from
(select t1,sum(qty) t1_qty1,0 as t2_qty2 from t1
group by t1
union all
select t1,0 as t1_qty1, sum(qty) t2_qty2 from t2
group by t1
) A
group by t1

cheers,
anand.
on December 22, 2007 at 2:24 am Anand

Hi manvendra Singh(22),

we can use Openrowset and insert data into table using

Insert into
select from OPENROWSET(’SQLNCLI’, ‘Server=xxxx;Trusted_Connection=yes;’,
‘SELECT GroupName, Name, DepartmentID
FROM AdventureWorks.HumanResources.Department
ORDER BY GroupName, Name’) AS a;

see sql help for more details.

cheers,
anand.
on December 22, 2007 at 2:26 am Anand

Dear Vishakha(24),

we can put data like that. i’m not clear about your requirement but generally anthing to be picked by like that then we use Constaraints/Deafults in defining values.

give more details about problem to help you better.

cheers,
anand.
on January 3, 2008 at 10:28 pm neeladri

When the question of optimisation comes we use stored procedures instead of long queries.
Now suppose a stored procedure’s name is 101 character long then what to do?
on January 11, 2008 at 3:59 pm ed9teen

hi, i tried my codes like this but it’s not working…
please help..?

Insert into magpatoc.dbo.RSOTransfer
Select * FROM (’Provider=Microsoft.Jet.OLEDB.4.0;’,
‘Data Source=c:\CopyOfRSODB.mdb;User Id=admin;Password=;’,
‘SELECT * FROM FinalCustItemRSO’)
on January 24, 2008 at 4:20 pm Ravishanker

hi
Iwant to generate a script for insertion of data to a table.

I have to repeatedly insert specific data to a table for each database,i want to automate it by genarating the insertion script.with the scripts of creation and all i have to generate some hundred records for a table to each database.

Please help me if any one got this situation

Thanks and regards
Ravishanker Maduri
on January 24, 2008 at 10:27 pm Lambda

Hi!

Thanks for a great article
Im using ODBC in c++ in a applications,
but all i want is to know how in the program, import large data directly to SQL Server but from the application from an external datasource (csv,txt), without have to use BULK INTO or OPENROWSET.

Heard somewhere that the bcp_init would do the job.

Regards
Lambda Swahili
on February 5, 2008 at 6:33 pm Bhanu

Hi……
consider i hav three tables
table1(t1col1,t1col2)
table2(t2col1,t2col2)
table3(t3col1,t3col2)
i need to enter t1col1 and t2col2 in t3col1 and t3col2 respectively….

plz guide me to implement this using insert select statement

Regards,
Bhanu
on February 6, 2008 at 4:45 am deepti

Thanks, just what I needed.
on February 7, 2008 at 8:10 pm Jeff

Hi Dave,

Nice Blog. Helped Me alot..

Thanks ;-)
on February 7, 2008 at 10:43 pm Wages

Have a user define data type for a phone number field. Defined as (###)###-####. Problem is have a hard time checking the constraint when inserting data from a stored procedure SQL 2005 Thank you for the help.
on February 8, 2008 at 12:38 am viswa

Hi Dave
I have two tables, one with 10 columns and the other with 20 columns
10 columns are common in both the tables
i want to update the table with 20 columns on a daily basis using a DTS Package

i need to insert if a record doesnot exist ,if exists i need to update the 10 fields
kind of new to sql sp’s
thot of using cursors
can anyone suggest me a way of doing it ?
thanks in advance
on February 22, 2008 at 5:48 pm varsha

you can transfer data from one server to another by the
following commands

Insert into finalxlsitems1
select * from
OPENROWSET(’SQLOLEDB’,
’server’;’sa’;”,
‘SELECT *
FROM server.reportserver.dbo.finalxlsitems1′
)
on February 26, 2008 at 4:22 pm yaqub

salam.sir

My Problem

i have 5 tables and one main table.the main table cantain all the foreign keys of another 4 table.so if i enter values in main table.it will not enter it.because it contain foreign keys of 4 tables.so please send me vb code to insert a record.

or

VB code for insertion of foreign keys values in table without mentioning the forighn key attribute.

THANKS
on February 27, 2008 at 10:59 am ABHISHEK ANAND

Hi,
I want to insert data from one server database table
to another server database table in vb6 code
any one can help me
on February 27, 2008 at 2:29 pm lalit

I want to insert data from one server database table
to another server database table in oracle.
pls help me…
on February 29, 2008 at 7:50 pm Mqadar

pease help me.
i have imported text file file into SQl db, i wan to change the impoted tbale to exixting table. how i can do that
on March 3, 2008 at 3:55 pm Avinash

Hi

How can i export data from one table to another table in different database using SQL query. I nead to use the query in VB6.0

Thanks
on March 6, 2008 at 1:07 pm manu

hi everyone
in seq server 2005 there is a feature of link
server by which u can communicate two diffrent server
and their database and more, ur querying one another server database using the same qury window
on March 14, 2008 at 7:50 am Tri Aji Yudista A

Helowww,
I want to insert from one table to another, create an EDM table, which is denormalized fact table, ETL from OLTP…
However this source table consists 30millions rows and still have to join with 6 other tables (also thousand to millions)…
Today this query is done in 10 hours, which is highly unacceptable, how can I solve this???
I’m thinking about devide-n-conquer where each record given an identity, and insert records in 10.000 records/iteration???
Any idea???
on March 14, 2008 at 7:54 am Tri Aji Yudista A

And I also have already use indexes…
on March 17, 2008 at 6:07 am sravanthi

hi,
I have to copy 2 colums,which are from 2 different tables, in to another table .when I am trying insert into,it is giving error.
Msg 1038, Level 15, State 5, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or [] are not allowed. Add a name or single space as the alias name.
can anyone help me in this.

My query is

select registration.customerid,vendoroffer.offerid,getdate() Into offer_assignment From Registration,vendoroffer where (select dbo.f_AgeCalc(dateofbirth, GetDate()) as age)Between 0 and 6 And zipcode=98052 And gender=’Female’ And vendoroffer.customerid=registration.customerid
on March 17, 2008 at 8:16 am sravanthi

hey I got it.Thanks for the post.
on March 17, 2008 at 5:46 pm Geetanjali

Hi

Can we insert data from one table to other which is not the same database?
on March 20, 2008 at 8:58 pm GC

Am trying to insert data from one table to another, but query is giving error :

INSERT INTO WC_WCLI_RETAIN
(STOP_ORDER_NO, STOP_CRD, Phone_Number, order_type, ORD_RCVD_DT, LINE_TYPE, ROBOT_STATUS)
SELECT ORDER_NO, CUST_REQD_DT, NATIONAL_NUMBER, ORD_CRT_TYPE, ORD_RCVD_DT, ‘C’, ‘RECEIVED’
FROM dbo.HADES_CPS HC
WHERE [PRODUCT_CODE] = ‘A72911′ AND [ORD_CRT_TYPE] = ‘S’ AND [ORD_RCVD_DT] =
(SELECT CONVERT(VARCHAR(10), GETDATE(), 111))) AND ORDER_NO NOT EXISTS
(SELECT 1
FROM WC_WCLI_RETAIN WCR
WHERE WCR.STOP_ORDER_NO = HC.ORDER_NO)

Help !!!!
on March 24, 2008 at 12:18 pm G.Prabhakaran

hi,

i want check duplicate data while insert into sql table

Can any one help me how to do that
on March 24, 2008 at 12:56 pm Vishwanath

Hi GC (51),

What is the error you are getting?? Would help to solve your problem.
on March 30, 2008 at 10:01 pm J.Marchena

hi,

I have to copy the same data to two different tables. Is it possible to do this with only 1 query? or does it have to be 2?
The thing is the database will have to function afterwards for someone who barely uses pc’s. So the messages that pop up must stay at a minimum. I can’t program with VBA so cancelling the messages out by those means is out of the question for me.

P.S: I am using access 2007
on March 31, 2008 at 6:17 pm Kiran M

Hi,

I have used INSERT INTO TABLE1….SELECT COLA, COLB.. FROM TABLE2 , in a procedure. The Problem is it doesn’t insert all the selected records in TABLE1. eg. If it selects 20 records it may insert all 20 sometimes, sometimes may insert 11 records or 9 records (less no. of records). Pls. suggest.

Thanks
kiran.
on March 31, 2008 at 10:11 pm Mark

Dave,

I am really struggling and my last foray into SQL was 9 years ago. I am trying to run the following on SQL Server 2005

INSERT INTO dbo.sim_control_table (customer_name, sage_customer_number, date_of_sale,
customer_buy_price, customer_commission_due)

SELECT customer_name, sage_customer_number, date_of_sale,
customer_buy_price, customer_commission_due

FROM dbo.excalibur_20080131

WHERE dbo.excalibur_20080131.sim_number = dbo.sim_control_table.sim_number

In order to keep it simple I have used the same column names in both tables and I am looking to update from multiple rows to multiple rows. When I then Execute the query I get the following error message:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier “dbo.sim_control_test_table.sim_number” could not be bound.
on April 1, 2008 at 8:13 am Joji

Nice forum. thanks.
I am inserting data from one table to another new table using Select into. Can you tell me, how can i copy over the defaults and indexez and constraints along with this.
on April 8, 2008 at 3:06 pm jewel

i want to copy data from one column to another column inside a table.anyone can help me?
jewel
on April 8, 2008 at 3:52 pm sandip

Can you tell me how to use the INSERT INTO IN clause to transfer data from a table in MS SQL Server database into a table in a oracle database?
on April 9, 2008 at 7:51 pm Imran

@ JEWEL

This is what I know…. please correct me if I am wrong.

1. You cannot work on only one column in case of insert and delete. When ever you try to insert a row will be effected .. in no case you can work on only one column. and copy is a insert statement. So that is not possible.

2. You can UPDATE one column, other columns will not be effected when you try to update one particular column. Since copy is not a update statement its a insert statement you cannot work on only one column.

If you want to have the same data in two columns in a table, for sure you can do that with out manually copying it.

This is what I did in the example:

CREATE TABLE UEXAMPLE ( EID INT, EID2 AS EID )

-> I created a table named Uexample and then I create a column EID which is int Datatype and I created one more computed column .. its not an alias because I did not give any datatype for second column, for aliases you still have to give the data type. so my second column is computed column which is exactly the fist column. I used computed column because you cannot have two columns with same name in one table.

INSERT INTO UEXAMPLE ( EID ) VALUES (2)

-> Simple insert statement.

SELECT * FROM UEXAMPLE

-> Simple select statement.

hope this works.
on April 9, 2008 at 8:01 pm Imran

@ 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.
on April 9, 2008 at 9:01 pm Imran

@ 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.
on April 10, 2008 at 1:20 am Imran

@ 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 !
on April 10, 2008 at 2:51 am Imran

@ 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 …
on April 10, 2008 at 5:46 pm kruti

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.
on April 10, 2008 at 6:01 pm kruti

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
on April 14, 2008 at 12:04 am Duncan

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!
on April 15, 2008 at 1:21 am Imran

@ 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…
on April 23, 2008 at 6:37 pm Jobby

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
on April 24, 2008 at 11:25 am ameen

hi sir ,
can u pls help me???
how can i retrieve all the data from the db row wise to my asp page table???
on April 27, 2008 at 12:56 pm haeri

INSERT INTO DestinationDatabase.dbo.DestinationTable (FIELDS Name)
SELECT FIELDS NAME
FROM SourceDatabase.dbo.SourceTable
on May 3, 2008 at 4:15 pm anil

khub saras kaam chhe. mane khub gamyu….!!! tame brahman bhai lago chho…!!!! aatlu saras parinam aapva badal khub khub abhinandan…!
on May 3, 2008 at 4:17 pm anil

can u tell me how can I copy data from one database table to another database same table… ???
on May 4, 2008 at 10:37 pm priya

can u tell the types of trigger..
on May 5, 2008 at 9:24 pm Imran

@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,

http://www.sql-server-performance.com/articles/audit/ddl_triggers_p1.aspx

———————————————————————

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,

http://www.sql-server-performance.com/articles/dev/triggers_2000_p1.aspx

—————————

Hope this helps

Thanks,
Imran.
on May 6, 2008 at 12:20 am Imran

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.
on May 9, 2008 at 3:47 pm abhi

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
on May 13, 2008 at 4:54 pm bhaiyalal

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
on May 15, 2008 at 10:47 am maha

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.
on May 20, 2008 at 4:47 pm nileema

hi,
i want to write trigger on table of one database for insert which insert that record into table of other database……..
on May 21, 2008 at 4:11 pm Jobby

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
on May 21, 2008 at 6:12 pm Di

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
on May 22, 2008 at 9:37 am wellsgano

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.
on May 22, 2008 at 11:18 am Satya

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
on May 22, 2008 at 1:19 pm wellsgano

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
on May 26, 2008 at 9:55 am edgar

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=\\pc1\TSmobileData\CopyTSMobile.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.
on June 3, 2008 at 2:58 pm Pranav Dave

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?”
on June 10, 2008 at 6:02 pm karthik

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..
on June 13, 2008 at 2:28 am Bears

Your site is very clean, and easily readable. Thanks for your generous help. you’re the best!
on June 25, 2008 at 2:43 pm Alok Sinha

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.
on June 26, 2008 at 11:06 am Anand

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…
on June 26, 2008 at 12:44 pm shyam

Thanks all for such a good posts
on June 26, 2008 at 12:48 pm Shyam

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.
on June 27, 2008 at 9:04 am Jonathan Summers

INSERT INTO TABLE 2(SELECT * FROM TABLE 1);

Works great on an Oracle DB.
on June 27, 2008 at 3:08 pm Satyanand rai

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
on June 30, 2008 at 1:46 pm Mansoor

How to insert data(only one column) from source table to another table

its very urgent need please send me the query.
on July 1, 2008 at 2:40 pm pavani

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
on July 2, 2008 at 2:45 am Imran Taher Mohammed

@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,
on July 2, 2008 at 10:28 am harishsk

good,
on July 5, 2008 at 2:27 pm Qaim

Hello sir,
I want a I want update a recored from another table at once using a select query. So please tell me query
on July 6, 2008 at 8:37 am Vinay

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
on July 8, 2008 at 9:44 pm Josh

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.
on July 12, 2008 at 10:01 pm Imran Mohammed

@ 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.
on July 15, 2008 at 8:46 pm don

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?
on July 16, 2008 at 2:50 am Imran Mohammed

@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 :)
on July 18, 2008 at 1:51 pm Kamlakar

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
on July 23, 2008 at 12:27 pm manojkirar

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
on July 23, 2008 at 12:48 pm Raghu

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………………:-)
on July 25, 2008 at 12:30 am Matthew

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?
on July 25, 2008 at 10:26 am Imran Mohammed

@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.
on July 25, 2008 at 5:55 pm Matthew

@ 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.
on July 25, 2008 at 6:12 pm sravan

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
on July 29, 2008 at 2:24 pm murry

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
on July 29, 2008 at 5:44 pm Jegan

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..
on August 5, 2008 at 1:31 pm Viny

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
on August 7, 2008 at 3:10 am Nirav

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???
on August 12, 2008 at 9:56 am Shameer

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
on August 13, 2008 at 12:53 am Imran Mohammed

@shameer

How can you insert data in the column when you dont have the column in the destination table.

Thanks,
Imran.
on August 13, 2008 at 4:32 pm srinadh

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
on August 18, 2008 at 8:03 pm Manjunath

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
on August 20, 2008 at 1:18 pm Zulfi Asdani

Hi Dave,

How do you insert data from text box into table?

Regards,

Zulfi
on August 20, 2008 at 10:43 pm Imran Mohammed

@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.
on August 22, 2008 at 10:08 am Anand A.K

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.
on August 22, 2008 at 8:46 pm Anu

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.
on August 29, 2008 at 12:00 pm Emeka

@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
on September 3, 2008 at 9:35 pm bluestar

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
on September 4, 2008 at 9:34 am Imran Mohammed

@ blue star

Need more information ….

Its really confusing !!! Please provide more information with input and how you want your output to be.

Thanks
Imran.
on September 6, 2008 at 2:46 pm Narayan

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
on September 16, 2008 at 4:48 pm Raymond

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
on September 24, 2008 at 9:37 am Vinay</

Journey to SQL Authority with Pinal Dave

Notes of a SQL Server MVP and Database Administrator

SQL SERVER - Insert Data From One Table to Another Table - INSERT INTO SELECT - SELECT INTO TABLE

161 Responses to “SQL SERVER - Insert Data From One Table to Another Table - INSERT INTO SELECT - SELECT INTO TABLE”