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://blog.SQLAuthority.com)

SQL Server Downloads

Posted in Pinal Dave, SQL, SQL Authority, SQL Optimization, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology | 282 Comments

282 Responses

on September 4, 2007 at 6:03 am | Reply 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 February 2, 2010 at 10:42 pm | Reply Deepu
  
  I have 2 tables viz… Table_1 and Table_2. In Table_2, I created a instead of trigger that modifies the data and then inserts it into the table. But when I try to copy data from Table_1 to Table_2 using the above method, only the last record is copied and the Trigger is also applied to it. Can you please tell me the reason for this?
on September 7, 2007 at 12:00 am | Reply 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 | Reply 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 December 1, 2009 at 1:04 pm | Reply KARTHIK
  
  u can use open rowset query to transfer ond db to other
on September 17, 2007 at 11:18 am | Reply 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 | Reply 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 | Reply Sriram

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

Plz send the query

Regrads
Sriram
- on December 19, 2009 at 10:20 am | Reply David
  
  Sriram,
  
  INSERT INTO database1.dbo.table (column1, column2)
  SELECT column1, column2 FROM database2.dbo.table
  
  David
on September 23, 2007 at 3:51 am | Reply 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 January 20, 2010 at 4:00 pm | Reply Prashant Srivastav
  
  Sir i have problem in mysql ………. I have 2 tables socialnet (firstname, lastname) and other is socialreg (age, status) ………. now i want to copy whole Firstname coulmn from socialnet to socialreg………………………PLZ tell me the query
on October 1, 2007 at 12:19 am | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply deepti

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

Hi Dave,

Nice Blog. Helped Me alot..

Thanks ;-)
on February 7, 2008 at 10:43 pm | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply Tri Aji Yudista A

And I also have already use indexes…
on March 17, 2008 at 6:07 am | Reply 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 | Reply sravanthi

hey I got it.Thanks for the post.
on March 17, 2008 at 5:46 pm | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply haeri

INSERT INTO DestinationDatabase.dbo.DestinationTable (FIELDS Name)
SELECT FIELDS NAME
FROM SourceDatabase.dbo.SourceTable
on May 3, 2008 at 4:15 pm | Reply 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 | Reply 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 | Reply priya

can u tell the types of trigger..
on May 5, 2008 at 9:24 pm | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply shyam

Thanks all for such a good posts
on June 26, 2008 at 12:48 pm | Reply 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 | Reply Jonathan Summers

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

Works great on an Oracle DB.
on June 27, 2008 at 3:08 pm | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply harishsk

good,
on July 5, 2008 at 2:27 pm | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply Zulfi Asdani

Hi Dave,

How do you insert data from text box into table?

Regards,

Zulfi
on August 20, 2008 at 10:43 pm | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply Vinay

@@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!!
on September 26, 2008 at 3:16 pm | Reply Surendar

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
on September 29, 2008 at 12:10 pm | Reply Malik

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.
on September 30, 2008 at 3:25 pm | Reply BPS

Very usefull Notes
on October 1, 2008 at 7:26 pm | Reply pavan mainde

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
on October 4, 2008 at 12:49 pm | Reply M ANIL REDDY

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
on October 4, 2008 at 5:49 pm | Reply سیستم مدیریت آموزش سما

after about 5 years of writing database applications , i am still confused with insert with select!!
on October 6, 2008 at 9:04 am | Reply M ANIL REDDY

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.
on October 8, 2008 at 10:35 pm | Reply Maris

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
on October 9, 2008 at 5:29 am | Reply bubu

could u pls guys help me with a insert procedure that will allow 2 records to be inserted inone column
on October 10, 2008 at 11:45 am | Reply Anil

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.
on October 10, 2008 at 1:35 pm | Reply Anil

Hi bubu,
I hope this will help u to solve ur problem.

Suppose Test1 is a table in that there are 3 columns(CL1,Cl2,Cl3) so now we are adding the CL1 & CL2 into CL3
So below procedure ma help u.

Create Proc Insert2RecordsInTo1Col
AS
Insert into Test1(Cl3)
Select Cl3= CL1+Cl2 from Test2
GO
on October 20, 2008 at 5:17 pm | Reply Punter

I want to insert records from more than one table into a new table..

can anyone help??

thanks in advance !!!!
on October 22, 2008 at 11:42 am | Reply Anil

Hi Punter,

I hope the below query will help u.

There are 3 tables(Test1, Test2, Test3) and the Test1&Test2 are named as T1, T2
In the Test1 we have First_Name(Column)
In the Test2 we have Last_Name(Column)
In the Test3 we have Full Name(Column)
So now i am inserting Test3 table in the Full_Name(Column)
from Test1&Test2 tables “First_Name+Last_Name”
but i have a comon column ‘ID’ in both the tables(i.e,T1, T2)
so i am comparing the ‘ID’ column from both tables and inserting it in the Test3 table in the Full_Name Column.

Insert into Test3(Full_Name)
Select T1.First_Name+’ ‘+T2.Last_Name from
Test1 T1, Test2 T2
where T1.ID = T2.Id
on October 24, 2008 at 4:48 pm | Reply Paul

Hi
Thanks for the post. Helped me a lot. Seems to have generated a few questions though…
Thanks
Paul
on October 29, 2008 at 12:19 pm | Reply Rajesh

hi,

I need a help .. need to insert a purticular field value into another table.(need trigger)

cons:

T1 = table one
f1=field1

t2=table two
f2=field2

then have to insert using trigger

update t1 set f1=t2.f2

please help me
on October 31, 2008 at 1:09 pm | Reply Vijayakumar.P

list advanced t-sql querying methods
on November 1, 2008 at 4:36 pm | Reply NaMiQ

hi.please help me.. i want to insert date to my table in sqldeveloper,but i cannot.. what i must do can anyone write this code whole!!!!
on November 5, 2008 at 4:56 pm | Reply Abhi

HI,
i have a probelem in sqlserver 2005.
i want to integrate data into a single intergration table from many other tables.
all tables have some column names. and i want to bring the data such that if a entry in row is changed it should be updated , and if row doesnot exist is should be inserted.

But probelem here is i have to compare 7-8 columns in a single row and then decide to update or not is there any solution.

there are a lot of solution available but they compare 1 or 2 columns in a row only.

PINAL please help out.
on November 10, 2008 at 9:30 pm | Reply LuvDB

Please can someone help me…

I am fairly new in the DB domain and I need to create a scripte that will CUT data from a table and PASTE it to a new table. The objective is to segragate historical data per month to improve the performance of a reporting tool.
Also how can I implement variables into my script to let the user specify from what date till what date he want to “transfer” the data into a new table that will have a variable name as well?

Is this possible?

Thank you in advance for any help!
on November 11, 2008 at 12:03 am | Reply Muralidharan Rathinasamy

Hi

Looks the situation you have best suits for table partitioning.
The Data movement will also be very fast when partioning is in place . the requirement will be just to have a column in the table with the representing time or date which will help us to segregate data very easily.

Regards
Muralidharan
on November 17, 2008 at 7:43 am | Reply Emily

So I am having a problem. I have set up my database tables in phpMyAdmin. Therefore, in my php code I am having a hard time writing the code to insert a primary key from one table into another. For example,
StudentInfo (stdID, FName, etc)

LookUpInfo(stdID, ProductID, etc)

If anyone understand please HHEEELLPPP.

$query= “INSERT INTO LookUpInfo(stdID) VALUES (’stdID’)”;
$result= $result = mysql_query($query)
or die (“Query failed: ” . mysql_error());

DOESNT WORK…PLLLEEAASSEEE HEELLLPP!!!!
on November 27, 2008 at 10:06 am | Reply Gourv

Hi

PL help me, i want export data through query in SQL
on November 29, 2008 at 2:20 am | Reply Deep

The following query helps to create the archival of table and dump the data from source table into test table based on criteria.
Step one: Run the following query

SELECT * INTO tbltest FROM tblsorce WHERE year(fieldyear) >= 2008

step Two: Refresh the table list

You will see a new table tbltest

Then use this table and test your query.
on December 2, 2008 at 1:33 pm | Reply Shashi

sql for two different table from two different server

like server1 & server2 — Two Different Server Not Database
tables table1, table2

select a.sn, b.name from server1.table1 left a join server2.table2 b on a.sn=b.sn
on December 8, 2008 at 2:10 pm | Reply PRITESH PARMAR

This is the query for inserting data form one table to another table in different DATABASE

THIS IS A SUCCESSFUL QUERY

insert into DocTypeGroup
Select DocGrp_Id,DocGrp_SubId,DocGrp_GroupName,DocGrp_PM,DocGrp_DocType
from Opendatasource( ‘SQLOLEDB’,'Data Source=10.132.20.19;User ID=sa;Password=gchaturthi’).dbIPFMCI.dbo.DocTypeGroup
on December 8, 2008 at 2:40 pm | Reply PRITESH PARMAR (PINTOO)

This is the query for inserting data form one table to another table in different DATABASE

THIS IS A SUCCESSFUL QUERY

insert into DocTypeGroup
Select DocGrp_Id,DocGrp_SubId,DocGrp_GroupName,DocGrp_PM,DocGrp_DocType
from Opendatasource( ‘SQLOLEDB’,’Data Source=10.132.20.19;User ID=sa;Password=gchaturthi’).dbIPFMCI.dbo.DocTypeGroup
on December 12, 2008 at 12:41 am | Reply Derek

Thank you, this was exactly the procedure I was looking for and worked perfectly for me!

Good job!
on December 19, 2008 at 1:28 am | Reply Priya

Hi,

I am connected to a network server through sql server management studio express.

I need to restore a copy of the backup which resides in my pc into the db connected through the network.

When I click on the restore db thru files option, I am able to browse thru only the drives/files of the server. How do I restore the db from my pc?

Thanks,

Priya.
on December 27, 2008 at 5:04 pm | Reply Kruti

Hi frnds,
i m try to do one query.
i have two tables
M_Payment (machine no, total amt, reference no)

Mode_of_payment (reference no, cash amt, cheq amt, DD amd)
data are likn:-
M_Payment

machine no total amt reference no consumer No

1 1000 122 C1
1 200 123 C2
1 300 124 C3
2 500 125 C4
2 500 125 C5

Mode_Of_Payment

reference no cash amt cheq amt DD amt

122 500
122 500
123 200
124 100
124 200
125 500
125 250
125 250

i want machine no wise total amt and sum of total cash amt, total chq amt and total DD amt

my require o/ p is…
O/P
machine no totla amt cash amt cheq amt DD amt
1 1500 600 700 200
2 1000 500 250 250

PLZ ANY BODY HAVE SOLUTION FOR THIS TYPE OF OUT PUT. I HAD TRY LOTS OF SELECT QUERY BUT I CANT GET THIS TYPE IS OUT PUT.

Kruti
Thanks
on January 2, 2009 at 12:57 pm | Reply Dharemendra Dixit

Hi Pinal,

Wish you and your Family a Very Happy New Year 2009 :-)

Pinal i have my Group on Yahoo for MSSqlServer which is “ASKSQLEXPERTS”, its my Pleasure if you will Join this Group so that all the members of this Group will get more help…

Thanks & Regards
Dharmendra Dixit
on January 2, 2009 at 1:18 pm | Reply Dharemendra Dixit

@devan

Hi Bro,

You can solve Your problem by writing this Script, think it will work successfully..

SELECT * INTO MYTable FROM Pubs.dbo.TableName

Regards
Dharmendra Dixit
ASKSQLEXPERTS
on January 11, 2009 at 4:43 am | Reply Kevo

You the man Pinal!!

I was looking to copy tables in sql server, and I got the way quickly from your website.

Thank you.

Kevo
on January 14, 2009 at 9:47 am | Reply Maverick

Hi,

Could anybody help or guide me on how to automate the backups of our tables from all the databases in Sql server 2000 and storing that backup file as *.mdb(MS Access) file on daily basis?

Thank you!
on January 14, 2009 at 5:06 pm | Reply Kunal Mehta

Very nice blog. I appreciate your efforts. It works for me.

Can you please let me is it possible with one query?

Kunal Mehta
on January 15, 2009 at 1:49 am | Reply Maggie

How to insert data from one SQL server table to another SQL server table?
For example, I want to recreate the table on production server from test server. Both servers are SQL server 2005 express, table structure are same.
on January 16, 2009 at 1:44 pm | Reply Rory

Hi

I’m trying to transfer records from one table in one database to a parallel table in a second database, using code as described in responses above.

I’m using the code :

“INSERT INTO [Students] IN ‘C:\VB9\Future Indicative\fiarchive2007.mdb’ SELECT * FROM [Students] IN ‘C:\VB9\Future Indicative\fi.mdb where [ID] in (2626,3548,2627,3549)”

and I get an error ‘Syntax error in from clause’. Can you spot a fault????

I have an open connection to the first database, but have NOT iopened a connection to the second database. Is this required????

Many thanks

Rory
on January 16, 2009 at 1:46 pm | Reply Rory

Further to that… I note that I have a connection open to the database FROM which I’m copying, but do NOT have an open connection to the destination database. This is clearly wrong… I’ll experiment.
on January 16, 2009 at 2:17 pm | Reply Rory

I’ve now opened connections to both databases but still have no success. (Neither database has a password….)
on January 16, 2009 at 6:34 pm | Reply Steve

Love you work Dave,
on January 28, 2009 at 1:25 pm | Reply satish kumar singh

Hi,

I retrive data from table using mysql database with the c++ program under linux , but when i insert data using variable then table receive null value
on January 28, 2009 at 3:34 pm | Reply jorge

can you help me…
this is a simple problem that i can’t figure it out.

syntax:

strql=”INSERT INTO tablename (field, field) VALUES (” & text1.text & “, ” & text2.text & “)”

unable to save in the database..
on February 5, 2009 at 1:37 pm | Reply jurj

How do you read data automatically from a table with a newly inserted data on it?
on February 10, 2009 at 4:50 pm | Reply Pritesh Parmar

Automate sql Query(stored procedure) using Jobs

Just create a new job through sql server agent
and in description write down

EXEC Stored procedure name
on February 10, 2009 at 4:52 pm | Reply Pritesh Parmar (Pintoo)

Automate sql Query(stored procedure) using Jobs

Just create a new job through sql server agent
and in description write down

EXEC Stored procedure name
on February 10, 2009 at 4:56 pm | Reply Pritesh Parmar (Pintoo)

This Query is for AUTOMATION OF STORED PROCEDURE

WHILE CREATING JOBS THROUGH SQL SERVER AGENT

EXEC sp_makewebtask
@outputfile = ‘D:\Pritesh\Media WIP Balances as of 31 Jan 2009.xls’,
@query =’Select ad_PCCode, Ad_Loc, Cl_Name, Brand_Name, Brand_Code, ad_pjid, ad_pjno,
case ad_reftype when ”VINV” then ”VINV” when ”MINV” then ”MINV” when ”TRO” then mis_name when ”RRO” then mis_name when ”PRO” then mis_name end as Stat_Name,
sum(ad_dramt) as WIP_DR,
sum(ad_cramt) as WIP_Cr,
sum(ad_dramt – ad_cramt) as WIP_Bal
From WIPMediaById
left outer join qryBrands on ad_BrandCode = Brand_Code
left outer join mismasterids on ad_statusid = mis_misid and mis_group = ”MSTAT”
Where Ads_AccDate <= ”2009-01-31” and ad_accode = ”1320000500”
Group by ad_PCCOde, Ad_Loc, Cl_Name, Brand_Name, Brand_Code, ad_pjid, ad_pjno,
case ad_reftype when ”VINV” then ”VINV” when ”MINV” then ”MINV” when ”TRO” then mis_name when ”RRO” then mis_name when ”PRO” then mis_name end’,
@colheaders =1,
@FixedFont=0,@lastupdated=0,@resultstitle=’Media WIP Balances as of 31 Jan 2009′
on February 12, 2009 at 4:54 pm | Reply Mustaque Ahemed

I am trying to insert data in a table table1 of data base db1 from table 2 of database db2 . But I find error message 208 invalid object name

insert into tb1(f1,f2) select f1,f2 from ma.tb1 where ma.tb1.f1=1
on February 13, 2009 at 9:59 am | Reply Imran Mohammed

Mustaque Ahemed

you have databaseA , TableA

you have databaseB, TableB

say, you are trying to insert data from TableA of DatabaseA into TableB of DatabaseB

This is script,

use DatabaseB
insert into TableB (f1,f2) select f1,f2 from databaseA..TableA as Tab1 where Tab1.f1 = 1

Regards,
IM.
on February 14, 2009 at 9:28 am | Reply Mustaque Ahemed

Thanks Mr Imran Mohammed, It is working I was missing a dot, it should be double dot
on February 17, 2009 at 9:59 pm | Reply dave

based on this example i used the following code:

insert into entrydata
(StaffID, EntryDate, DayIndex, ClientID, ProjectID, StartTime, EndTime, ClientAreaID, ElapsedTime, WeekBeginningDate)
select
(StaffID, EntryDate, DayIndex, ClientID, ProjectID, StartTime, EndTime, ClientAreaID, ElapsedTime, WeekBeginningDate)
from holdingtable where StaffID = ‘AMBRES003′ and WeekBeginningDate = ‘2009-01-26′;

i recieved the following error message:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘,’

i have checked the code and don’t see any erroneous ‘,’ and there are no ‘red lines’ under any of the code in SSMS – I am using SQL Server 2008 if that helps

any idea what the problem is? i have wasted a whole afternoon trying to copy these columns from one table to another!
on February 18, 2009 at 11:35 am | Reply Imran Mohammed

@Dave

insert into entrydata
(StaffID, EntryDate, DayIndex, ClientID, ProjectID, StartTime, EndTime, ClientAreaID, ElapsedTime, WeekBeginningDate)
select
StaffID, EntryDate, DayIndex, ClientID, ProjectID, StartTime, EndTime, ClientAreaID, ElapsedTime, WeekBeginningDate from holdingtable where StaffID = ‘AMBRES003′ and WeekBeginningDate = ‘2009-01-26′

When you are writing insert into table_name it should be immediately followed by a select statement.

insert into table_name select col1,col2,col3 from table_name2
where blah blah….

Regards,
IM.
on February 25, 2009 at 11:09 pm | Reply SQL SERVER - Top Five Articles of Year 2008 Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE Insert multiple records using SELECT statement. [...]
on February 26, 2009 at 5:37 pm | Reply SQL SERVER - 2008 - Interview Questions and Answers - Part 8 Journey to SQL Authority with Pinal Dave

[...] (Read More Here) [...]
on February 26, 2009 at 5:43 pm | Reply SQL SERVER - Guidelines and Coding Standards Part - 1 Journey to SQL Authority with Pinal Dave

[...] Instead of using LOOP to insert data from Table B to Table A, try to use SELECT statement with INSERT statement. (Read More Here) [...]
on March 3, 2009 at 8:57 pm | Reply hughie

I’m writing a batch job using .NET and want to compare a table in our data warehouse with a transactional table. These 2 tables reside on different servers and databases. I am creating a datahandler from .NET and have been told that creating 2 different data connections will not work ???

I actually need to compare rows on each table to determine if rows may have been updated already.

Any other ideas or will 2 different connections work with two seperate data adaptors? Any ideas welcome or if there is a better idea please share.

thanks
on March 5, 2009 at 11:56 pm | Reply Leslie Hurst

What if you have a large amount of data and your transaction log fills. We are using a query to gather data from several tables and inserting into one table. The insert into select works well except where the result set is very large, the transaction log fills. This is a reporting db that is refreshed nightly from a highly normalized transactional db. After the restore, we are running a script to create & populate some unnormalized tables. What other method can we use for the large results set?

Thank you!
on March 6, 2009 at 5:36 am | Reply rama

hai
i am getting ora.00905 : missing keyword error
with the following method
can you please help me

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
on March 6, 2009 at 5:39 am | Reply rama

and my sql query is

select accnt_name into testtable
from inturi.c_account
where row_id=’460-2BF’
on March 6, 2009 at 10:36 am | Reply Imran Mohammed

@Leslie Hurst

Since the database is only for reporting, Why dont you keep your database recovery model in simple mode.

If data is huge, if there are any Non-Clustered indexes that are not build on primary key or unique key, then disable those indexes, loading will be much faster. Also when you update statistics you might want to change recovery model to Simple.

Look at this link:

http://blog.sqlauthority.com/2009/02/19/sql-server-enable-and-disable-index-non-clustered-indexes-using-t-sql/

More Important : Please read comments by experts for the above link.

When you change recovery model, it will break any backup chain that you might have scheduled. But assuming this is only for reporting services, I would not be concerned about log file.

Hope this helps,
IM.
on March 6, 2009 at 10:39 am | Reply Imran Mohammed

@Rama

I dont know if this feature is in ORACLE.

From the error message you posted, I am guessing that you are trying to execute this query in ORACLE. Well script executes fine in SQL Server not sure about ORACLE.

Regards,
IM.
on March 6, 2009 at 6:50 pm | Reply Brian Tkatch

@Imran

Just confirming, Oracle uses INTO in PL/SQL to set a variable. To load a TABLE it’s CREATE TABLE AS SELECT….

I get confused in between the two sometimes. It’s not the different syntax, it’s when the same syntax means two different things.
on March 7, 2009 at 10:56 am | Reply Imran Mohammed

@Brian

I agree with you.

Your explanation, to perform similar action in ORACLE, we have to use below command is absolutely correct.

CREATE TABLE AS SELECT…. or

But Brian there are chances that users copy scripts from internet and with out knowing that Script is written in T-SQL they could try to execute it on ORACLE which leads to these error messages.

Brian any idea, which is according to ANSI SQL,

SELECT * INTO table_name from ……

or

CREATE TABLE Table_name as select ……..

Regards,
IM.
on March 7, 2009 at 4:51 pm | Reply RAFAY

Hi!!

i have just started working on SQL i need sum guide line how to create own table can u help me …

RAFAY PERVAIZ.
on March 9, 2009 at 4:15 pm | Reply Kuttan

Hi,

I have different tables (say table1, table2,etc) with the following fields.

table1(Timestamp,value1)
table2(Timestamp,value2)
.
.
.
All have same timestamp. I have to merge all tables into single table

table(timestamp,value1,value2,…..)

I think the time stamp can be updated as datetime format. But I dont know how to update columns by comparing with the timestamp. Please help

Thanks
on March 12, 2009 at 4:17 pm | Reply saskia

Hello,
I want to update my table a from database A with table b from database B.
Has anyone any tips?
on March 13, 2009 at 3:08 am | Reply Madhu

Hello Dave

I am doing a select and inserting into temp table. I want the rows inserted into temp table ina particular sequence.

Example

select a,b from table 1 order by a into temp table2

But i see that table2 is not inserted in order. Is there a way to do this?

thanks,
Madhu
on March 13, 2009 at 5:39 pm | Reply Surajit

Hello to all,
I want to copy data from a database table in a server to database table of another server.
I user this example
insert into DocTypeGroup
Select DocGrp_Id,DocGrp_SubId,DocGrp_GroupName,DocGrp_PM,DocGrp_DocType
from Opendatasource( ‘SQLOLEDB’,’Data Source=10.132.20.19;User ID=sa;Password=gchaturthi’).dbIPFMCI.dbo.DocTypeGroup

all table name database name username and password are ok but the following error shows –

SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, see “Surface Area Configuration” in SQL Server Books Online.

What is solution now….
on March 13, 2009 at 5:55 pm | Reply subhash

Hi pinal dave ,

Thanks in advance

I have one question about sql server 2005 TRIGGER.

can i insert data into master-detail table using Trigger.

say for example.

i have one master table name(Info) and field(ID,Name,Age)
and detail table name(sub_info) and field(SUB_ID,ID,ADDRESS). so now when i insert single row in info(master) table then using trigger also insert row in sub_info(detail table). but in sub_info table i want to pass address filed data from outside. so is it possible or not.

also i create trigger which i given below. just refer it and reply me as soon as possible.

CREATE TRIGGER ts_SUB_INFO_ADD_TRIGGER ON info for insert
AS

declare @id int
select top(1) @id = id from info order by id DESC insert into Sub_info(ID,address)values(@id,’Event issue’)

insert into info(name,age) values(‘bhavin’,24). when i run this insert query then t\above trigger insert single row in sub_info table. but here address is static. so i want to add dynamic address.

So please take a urgent and send me reply.
on March 16, 2009 at 6:08 pm | Reply guest

I have two table table 1 & table 2

table 1

OLD_id
——————— ———————
64518
59414
65007
57750

table 2

New_ID
——————— ———————
5053
5756
8535
2328

I need result

OLD_id New_ID
——————— ———————
64518 5053
59414 5756
65007 8535
57750 2328

I dont have any condition .
on March 16, 2009 at 6:22 pm | Reply guest

my query with union is returning the result

OLD_id New_id
——————— ———————
0 5585
0 5891
0 5895
0 5896
57750 0
59414 0
64518 0
65007 0

I need this result in this form
OLD_id New_id
——————— ———————
57750 5585
59414 5891
64518 5895
65007 5896

Any solution ??
on March 16, 2009 at 7:19 pm | Reply Tejas Shah

Hi,

How to make condition. Are you sure that Row 1 in Table A has match with Ro1 of Table B?

If that is the case then you can use this:

;with A AS(
SELECT ROW_NUMBER() OVER(ORDER BY ColumnA) AS RowNum,
*
) , B AS(
SELECT ROW_NUMBER() OVER(ORDER BY ColumnB) AS RowNum,
*
)
select A.columnA, B.ColumnB
from A
INNER JOIN B ON a.RowNum = B.RowNum

Thanks,

Tejas
on March 26, 2009 at 1:22 pm | Reply fastanum

thanks vry much..it helps me a lot…
on April 1, 2009 at 6:28 pm | Reply Sanjeet Kunwar

This Data Has completely resolved my problem.
For this kind help thanking you so much
on April 6, 2009 at 2:29 pm | Reply tdcl

how to write a cursor to loop through the records of one table and insert them into a temp table?
on April 6, 2009 at 2:32 pm | Reply tdcl

Can anybody tell me how to write a cursor to loop through the records of one table and insert them into another table?
on April 7, 2009 at 1:21 am | Reply Taskheer

Dear Pinal,

Thanks for this.
I want to transfer table of one data of first database to table of second database using stored procedure.
Will u please send me the answer on my mail id.

Thanks
on April 7, 2009 at 5:31 am | Reply Imran Mohammed

@tdcl

Simple example of cursor:

http://blog.sqlauthority.com/2007/01/01/sql-server-simple-example-of-cursor/comment-page-1/

~ IM
on April 14, 2009 at 10:15 am | Reply vani

hi
i am trying to insert data from one table to another table with same field name but the datatype for one field is different that is for TravelDate field in the 1st table it is Varchar(15) & in the 2nd table the Datatype of TravelDate is Datetime so when im trying to insert data from 1st table to 2nd table im getting error as
“Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.”

when im trying to insert from this query

insert into linkinvoicesector(InvoiceSecID, InvoiceID, InvoiceNo, SOSecID, SOID, SONO, GDID, PAXID ,SecID ,PNR, DepartureCity, DestinationCity, DestinationFlag, TravelDate, FlightNo, ETA ,ETD, Class ,Status ,Flag)
select InvoiceSecID, InvoiceID, InvoiceNo, SOSecID, SOID, SONO, GDID, PAXID ,SecID ,PNR, DepartureCity, DestinationCity, DestinationFlag, TravelDate, FlightNo, ETA ,ETD, Class ,Status ,Flag
from linkinvoicesectors

i have tried even by use convert while inserting but no use still same error.
can i have any solution regarding this problem. I would be very thankfull to u if my problem is solved

thankq
on April 15, 2009 at 12:23 am | Reply Usman

If I Have Two Databases On Two Different Server Connected With Each Other And I Have Got To Make View How Can i Collect Data From Both Databases I Mean What is The Way to Make Connection Between Both Databases?
on April 23, 2009 at 5:24 am | Reply Zuhaib Alam

excellent post! helped me tons.
thanks.
on April 28, 2009 at 4:52 pm | Reply Gilsygirl

Thanks very much for this post i have been struggling but am finally getting somthing. My problem is the query below is repeating each record twice. what could be the problem.

Select CandTrans.CentCode,CandTrans.CandNo,CandTrans.Subjcode,CandTrans.CandName,CassMarks.SubjName
FROM CandTrans,CassMarks
WHERE NOT EXISTS(SELECT CassMarks.CandNo FROM CassMarks WHERE(CandTrans.CandNo=CassMarks.CandNo))
on May 4, 2009 at 12:12 pm | Reply babu

Hi,

i am working on asp with backend as sqlserver 2000

I have a diff problem

i hav two tables for masters one is main and another is history table

when some one modifies or updates the existing record
the data of the existing record will be copied to the history table.

but when iam using this below query

insert into NRI_PIS_SCHEME_MASTER_HISTORY select SchemeId,Narration,Hslbrokper,
BankPisPer,DPper,convert(datetime,StartDate,103), convert(datetime,EndDate,103) ,
Remarks,Seqno , CreatedBy,CreatedDate,’aurionpro’,getdate(),’0′,’1′ from
NRI_PIS_SCHEME_MASTER where SchemeId= ‘15′

the data is inserted twice

i checked out the query it got displayed only once

i donno what’s going on

i tried diffly but nothing going in the right way pls help me

thnx in advance :)
on May 9, 2009 at 4:07 pm | Reply kumar

How To Protect Password in database using sql query

2005.

Pl help me.
on May 9, 2009 at 9:13 pm | Reply Imran Mohammed

@Kumar

You can use, Symmetric Keys, Asymmetric Keys or Certificates to encrypt your data. When you store your data in database you need to specify a certificate name or symmetric or asymmetric key name and this will encrypt your data. When you want to retrieve your data back you can always specify certificate name and get your data.

This is a very good tutorial. Step by step, I strongly suggest you please go through this.

Link : http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/

~ IM.
on May 14, 2009 at 2:42 pm | Reply Jitendra Patel

I want to insert data from one server database table
to another server database table in sqlserver 2000.
pls help me…
on May 15, 2009 at 8:29 am | Reply Imran Mohammed

@Jitender

You can use any one these to get your task done.

1. You can use DTS Package and using that DTS package you can import data from one sql server and load it into another sql server.

2. You can create a linked server on any of the server, lets say you have a table1 on Server1 and table2 on server2, you want to copy data from table1 on server1 to table2 on server2.

First you create a linked server, on server1 (name of linked server lets says is Server1) after you create this, execute this script on server2

insert into table2 select * from server1.db_name.dbo.table1

This should work fine, you have to change db_name in above query with the database name on server1 in which table1 is present.

if you do not want to create a linked server because of security reasons, you can also use OPENROWSET Function. Read more about OPENROWSET in Books online, look at sample script provided in books online, That should give you a good start.

IM
on May 20, 2009 at 12:03 am | Reply Jean

hi, why my insert do not work??

INSERT INTO psi_Checklist_Logs
(application_id, checklist_id, data_month, activity_status, execute_Date, operation_status, summary, bgcolor, boxicon, starttime, endtime, checklist_code, application_code)
SELECT APPLICATION_ID, OPERATIONCHECKLIST_ID, 200903, isnull(ACTIVITY_STATUS,”), EXECUTE_DATE, isnull(operation_status,”), isnull(COMMENTS,”), ”, ”, [start], [end], CheckCode, AppCode FROM psi_Checklist_Logs_bk

Msg 512, Level 16, State 1, Procedure trig_Checklist_Logs, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.
The statement has been terminated.

thank you
on May 21, 2009 at 12:54 pm | Reply Ali Kazim Gardezi

Hi All,

I need to selectively copy records from one table to another i.e. copy if the record does not exit otherwise update a particular field.

I guess I have to use cursor in a stored procedure. But I’m not sure how. Would appreciate if someone can put some code here.

I’m using Firebird database.

Thanks in advance.
on May 22, 2009 at 12:04 pm | Reply Imran Mohammed

@Ali Kazim

You need not use Cursors for this task,

Instead of one query write two quries,

1. Query to update existing Records
2. Query to Insert New records

Example Script :

create table example1 ( eid int , ename varchar(10))

insert into example1 values ( 1, ‘Apple’)
insert into example1 values ( 2, ‘Boy’)
insert into example1 values ( 3, ‘Cat’)
insert into example1 values ( 4, ‘Doll’)

select * from example1

create table example2 (eid int, ename varchar(10))

insert into example2 values ( 1, ‘Apple’)
insert into example2 values ( 3, ‘Boy’) — Needs to be updated.

select * from example2

– Update Using Joins

update example2
SET ename = B.ename
from EXAMPLE2 A
join example1 B on A.EID = B.EID

– Insert Using Joins
insert into example2
select A.eid
,A.ename
from example1 A
full outer join example2 B on A.eid = B.eid
where B.eid is null

– Update Using Where & IN Clause

insert into example2
select A.eid
,A.ename
from example1 A
where A.eid not in (select eid from example2)

– Insert Using Where & IN Clause

update example2
set ename = A.ename from example1 A
where A.eid in (select eid from example2)

Some times Joins are faster than In and where clause, in some rare cases joins could be slower. That is the reason I have provided you examples using both, Joins and where clause,

Check which one suits best for you consider only performance, both scripts will and should return same result set.

One Suggestion: I read this comment some where, long back, Cursors and While Loops are for freshers.

Use while loops and cursors carefully, they can kill your performance like anything.

~ IM.
on May 28, 2009 at 10:34 am | Reply Milan

Hi,

I how do i insert guid?

i have created a table1( guid, field1)

and i want to insert the guid in table2(field2, field3, guid)

table1 has guid as PK

i am having problems inserting record in table2, i want to insert the guid from table1 into table2.
on May 31, 2009 at 2:16 pm | Reply James KAYIHURA

Thx for this one, it’s really interesting

Thx again
on June 1, 2009 at 1:16 pm | Reply ashish

pls reply me i want to connect my excel file with my sqlserver 2005,
so tell me how to that!!
on June 2, 2009 at 10:39 am | Reply Imran Mohammed

@Ashish,

Are you trying to import data from Excel sheet or Export to Excel sheet.

You can use,

Openquery ,
BCP,
DTS/SSIS

Look at this link :

http://blog.sqlauthority.com/2008/01/08/sql-server-2005-export-data-from-sql-server-2005-to-microsoft-excel-datasheet/

If you google, you will get at least 1000 links for this topic, with step by step screen shot explanation.

~ IM
on June 12, 2009 at 8:35 pm | Reply Ryan D

Thanks for this article, However it inserting new records into an existing child table is not working for me because it is dependent on a parsed version of one of the parent table fields. And because I use a table function to achieve this. How can I fix?

I have SQL Server 2008 and Visual Studio 2008 and am trying to automatically insert new records into a child table based on new inserts into its parent table.

I know that this should be simple: via an insert trigger. However, the complexity is that the new records into the child table need to be a parsed version of one of the parent table’s fields.

To achieve this, I developed a table function which parses this one field in the parent table.

Originally I had the Foreign Key defined below, but this wasn’t automatically inserting new records into my child table, so I commented it out.

I have tried writing both recursive and non-recursive insert triggers, but I have received errors on almost every attempt. I was able to partly get non-recursive triggers to work…but without the parsing function. I think the trick is that this is a table function.

How should I design this? Some of my pseudo code as follows:

CREATE TABLE ParentTable
(
ParentID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
strInput VARCHAR(8000) NULL
)

CREATE TABLE ChildTable
(
ChildID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
ParentID INT,
FullRow VARCHAR(8000)
–I removed the following FK cause it wasn’t automatically inserting new records:
–CONSTRAINT AddParentID FOREIGN KEY ParentID REFERENCES ParentTable(ParentID)
)

Sample output of what I want is:
ParentTable:
ParentID strInput
1 ‘AAA|BB|CCCCCC|D|EE’
2 ‘FFF|R|BC|D|EE’
3 ‘GG|BB|CCHC|D|EE’

ChildTable:
ChildID ParentID strInput
1 1 ‘AAA’
2 1 ‘BB’
3 1 ‘CCCCCC’
4 1 ‘D’
5 1 ‘EE’
6 2 ‘FFF’
7 2 ‘R’
8 2 ‘BC’
9 2 ‘D’
10 2 ‘EE’
…

My table-function removes the “|” delimiter with strInput as its input. How can I achieve the above results?

Thanks!
on June 12, 2009 at 8:36 pm | Reply DNJ

Hi dave,

How to copy data from columns which are on a different server.
on June 20, 2009 at 4:33 am | Reply Desiree

I had to rebuild an Access Database that was kind of a mess. So I started from scratch, I created a database relational database and now I need to take the data from the old database to the new one which is on SQL Server 2005. So I am taking and old MDB and turning it into an ADP and I am trying to find the best way without loosing data, migrating the data from the old to the new. How would I do that??

I was thinking of doing a plain insert, but the thing is the datatypes are not exactly the same
on June 23, 2009 at 4:06 pm | Reply pritesh

how to multiple rows in the sql 2000through vb6.0 with the concept of RDBMS.
please help me
on July 2, 2009 at 10:20 am | Reply Prateek Tiwari

Its a very helpfull article. Very simple and to the point. Thanks.
on July 11, 2009 at 3:04 am | Reply Tom L

i have 2 tables simillar to this example.
Table A Table B
group_id group_id count
1 1
1 2
2 19
1 33
2
2
19
19
33
19

i need to write an sql statement that will count the number of occurrences of each group_id # in table A and place that count in the count column of Table B.

Thank you in advance,
tom
on July 13, 2009 at 3:14 pm | Reply New techie Praveen

Thanks Pinal,

Will select into statement copy constraints also?
on July 13, 2009 at 4:03 pm | Reply Matt M

We’ve found that OPENROWSET only ever returns 1 row in SQL Server 2008?

For example, this code

SELECT *
FROM OPENROWSET(‘MSDASQL’,
‘DSN=the_dsn;UID=the_uid;PWD=the_pwd;’,
’select 123 from dual union select 456 from dual’)

… correctly returns 2 rows (123 and 456) in SQL 2000 but only returns 1 row (123) in SQL 2008.

Is this a configuration issue? We’re connecting to an Oracle database.
Thanks in advance
on July 24, 2009 at 3:20 am | Reply nu

Q. Write a SQL select statement. Given a table called EmailAddresses and the columns ID (int) and EmailAddress (varchar(100)), write a SQL SELECT statement that retrieves all email addresses that occur more than twice in the table.
on July 29, 2009 at 7:01 am | Reply SQL SERVER – 2008 – Copy Database With Data – Generate T-SQL For Inserting Data From One Table to Another Table Journey to SQL Authority with Pinal Dave

[...] data from one table to another table without generating any script or using wizard in my article SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TA…. Today, we will go over a similar question regarding how to generate script for data from database [...]
on August 13, 2009 at 1:48 am | Reply jw1982

you are the best
on August 14, 2009 at 9:32 pm | Reply xero

thank you! this article was very enlightening! i used the select into query combined with an update query to clone a table and swap values of two columns in a circumstance.

SELECT *
INTO [new-table]
FROM [old-table]

DECLARE @temp AS decimal
UPDATE [new-table]
SET @temp=value1, value1=value2, value2=@temp
WHERE id < 806
on August 18, 2009 at 7:51 am | Reply mitch

Hi there..!

I want to know how to copy the data and its properties(identities)/structure from the old table to new table. I am really upset with this thing. When I was just using SELECT INTO FROM statement, It only copy the data with basic structure of the old table.. Kindly need your help.. thanks!
on September 7, 2009 at 4:49 pm | Reply ANJAN GHOSH

SEND ME A FUNCTION PROCEDURE
on September 15, 2009 at 4:55 pm | Reply Rushi

I tried by making TransactionOption to be Required.
i want to insert a data throug child table
for this i want to check if the data is all ready in master table than no problem,
but if it is not in master table than i want ot insert data through child table.which is connect with the master table
so please tell me what should i do
on September 15, 2009 at 5:29 pm | Reply Rushi

Hi,

how do i insert br_no?

i have created a table1 mst_brng( br_no)pk
i have created a table2 sec_mst_brng(br_no,br_mk)br_no is a FK
and i want to insert the br_no in table2(br_no, br_mk)
but i want check first if br_no allready in master table than no problem,
but if it is not in master table than i want to insert data through child table so for this what should i do
on September 25, 2009 at 7:19 pm | Reply Darshan

Hi Rushi,
You are going right way. You have to use transaction.
while inserting records in child table first you have to find that that record is already exist or not ,if not then first store into one local variable and then add it into master table first and then add in child table.

I hope u will get it .

Thanks
Darshan Shah
on September 25, 2009 at 7:23 pm | Reply Darshan

Hi Mitch,
Basically we have to use select * into for temporary tables.
We can use it to create basic structure with Data.
But if you want to create table with all details then first you have to create script for it.
GO Enterprise manager->right click on object ->Generate Script ->Create ->copy the script and use that script to create new object.
after then use insert statment with select.

I hope this will help you.

Thanks
Darshan Shah
on October 1, 2009 at 11:34 am | Reply lugene

My tables are SubjectTable and GradeTable.SubjectTable has datafields of Course, Subject, Units While GradeTable has Studname, Subjects, Grade, Remarks. I inserted datafields of subject from SujectTable to GradeTable. And it leaves the other rows blank. How can I Add data to a Null rows?Can somebody help me with this problem of mine….thanks
on October 7, 2009 at 5:27 pm | Reply Gopu

Hi Dave,

How do we pronounce u’r name.

thanks in advance. :)
on October 26, 2009 at 8:25 am | Reply Harish

Hi pinal,

i have a problem inserting multiple records into a table. My code looks like this:

DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT strength_app_men_inst_id from TACF_WFTA_STRENGTH_APP_MEN_INST where wf_task_assignment_id = @WfTaskAssignmentIDP1
BEGIN TRAN t3
BEGIN
OPEN @MyCursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @MyCursor
INTO @StrengthAppMenInstIDP1
END
exec @ReturnCode = getid_sel @table = ‘TACF_WFTA_STRENGTH_APP_MEN_INST’, @id = @NewID2 OUTPUT, @mode = 2
insert into TACF_WFTA_STRENGTH_APP_MEN_INST (id, wf_task_assignment_id, strength_app_men_inst_id) values (@NewID2, @WfTaskAssignmentIDP2, @StrengthAppMenInstIDP1)
CLOSE @MyCursor
DEALLOCATE @MyCursor
END
IF @@error 0
ROLLBACK TRAN t3
COMMIT TRAN t3

the select statement returns multiple values and i have to insert that into the same table. i know cursor is not a good way, but my select statement will always return a max of 10 values. Can you please check where i am going wrong, or a better way.

Can you please help me with this as soon as possible. I would really appreciate this.

Thanks,
Harish
on October 26, 2009 at 8:27 am | Reply Harish

FYI: in my test data, the select returns 2 values, but when i ran the query, i got only one row with @StrengthAppMenInstIDP1 as null, but it had a value.

Dont know where i am going wrong…

Harish
on October 26, 2009 at 8:37 pm | Reply Brian Tkatch

@Harish

Is there a way top combine this into one statement?

INSERT INTO …. SELECT … FROM
on October 27, 2009 at 2:29 am | Reply Harish

@Brian, hmm i dont think so. But i was able to figure out my issue.

The correct one’s is:

DECLARE @MyCursor1 CURSOR
SET @MyCursor1 = CURSOR FAST_FORWARD
FOR
SELECT strength_app_men_inst_id from TACF_WFTA_STRENGTH_APP_MEN_INST where wf_task_assignment_id = @WfTaskAssignmentIDP1
OPEN @MyCursor1
FETCH NEXT FROM @MyCursor1 INTO @strength_app_men_inst_id
WHILE @@FETCH_STATUS = 0
BEGIN
exec @ReturnCode = getid_sel @table = ‘TACF_WFTA_STRENGTH_APP_MEN_INST’, @id = @NewID2 OUTPUT, @mode = 2
insert into TACF_WFTA_STRENGTH_APP_MEN_INST (id, wf_task_assignment_id, strength_app_men_inst_id) values (@NewID2, @WfTaskAssignmentIDP2, @strength_app_men_inst_id)
FETCH NEXT FROM @MyCursor1 INTO @strength_app_men_inst_id
END
CLOSE @MyCursor1
DEALLOCATE @MyCursor1

Thanks for checking out… Harish
on October 27, 2009 at 6:56 pm | Reply Brian Tkatch

@Harish

I’m happy you could figure it out.

I always try making it one query if possible. I could not see it here, because a PROCEDURE is being used in the middle.

INSERT…SELECT is usually faster than a CURSOR. And as one query, everything will be in context too. And for many, it is much easier to read because all the logic is in one statement. It might be worth a bit of your time to review the entire process and see if it can be one statement.
on October 27, 2009 at 7:11 pm | Reply Harish

Thanks Brian, for your time and effort, i’ll re-look into my stored proc and try to make it simple as you suggested…

Thanks again,

Harish
on November 2, 2009 at 7:41 am | Reply Ania

Thanks so much, the first useful bulk insert article!
;)
on November 10, 2009 at 5:33 pm | Reply Jignesh Kyada

Dear All,

can anybody have answer of how to swap data from one identity column to same column in sql server 2005 table
on November 12, 2009 at 8:47 am | Reply Sneha

Hi,

I wanted to know the alter command to add a column in middle of other columns in existing table.
on November 13, 2009 at 6:58 am | Reply Imran Mohammed

@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.
on November 14, 2009 at 5:33 pm | Reply Ehsan

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 !
on November 15, 2009 at 11:17 am | Reply Imran Mohammed

@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
on November 16, 2009 at 4:06 pm | Reply Tejas Shah

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
on December 1, 2009 at 9:07 am | Reply Sourabh

Great post !!!!
on December 2, 2009 at 4:01 am | Reply Clive

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
on December 2, 2009 at 11:22 am | Reply Tejas Shah

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
on December 12, 2009 at 2:52 pm | Reply sana

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.
on December 13, 2009 at 2:05 am | Reply Andrei Hetel

@sana

Quickest method is to use backup – restore. Now, it depends on your business requirements.
on December 13, 2009 at 5:48 pm | Reply tomi

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
on December 14, 2009 at 3:03 pm | Reply kapil

Hi sana best is back up and restore wizard . either you can write a script

restore database
from
with replace
on December 22, 2009 at 5:27 pm | Reply selva

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
on December 29, 2009 at 4:28 pm | Reply Joe

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.
on December 29, 2009 at 7:24 pm | Reply Pinal Dave

Hi Joe,

This can be done as
SELECT tableA.ID, tableB.ID FROM tableA LEFT OUTER JOIN tableB ON tableA.ID = tableB.ID

For more information please see the following article:

http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/

Regards,
Pinal Dave
on December 31, 2009 at 7:01 am | Reply SQLAuthority News – 1200th Post – An Important Milestone Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – 2008 – Interview Questions and Answers Complete List Download 2) SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TA… 3) SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL 4) SQL [...]
on December 31, 2009 at 5:09 pm | Reply Mandar

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?
on December 31, 2009 at 6:06 pm | Reply Pinal Dave

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
- on January 1, 2010 at 8:29 pm | Reply Mandar
  
  Hi Sir,
  
  Thanks for reply. Your articles are amazing.
  
  Regards,
  Mandar Kavishwar
on January 13, 2010 at 4:27 pm | Reply Anil Gautam

Hey Pinal,
Its really grt blog. keep it up man…..
on January 15, 2010 at 3:05 pm | Reply LukmanulHakeem

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
- on January 15, 2010 at 10:30 pm | Reply Brian Tkatch
  
  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 + “‘”;
on January 18, 2010 at 1:51 pm | Reply ix-one

Hello,
I want to insert from another table (2 tables)

-Tbl1.Field1 (Pk), Field2, Field3, Field4(FK)
-Tbl2.Field4 (PK), Field2, Field5, Field6, Field7

I want to insert into Tbl1 but i have “Field2″ in antoher table (tbl2).
I try to used
– (“Insert Into Tbl1(Field1,Field2,Field3)
Values(@Field1,@Field2,@Field3)Select Field2 From
tbl2″,connection)
but there always show message error “Missing semicolon (;) at end of SQL statement”. But if i add the semicolon(;) at the end of SQL statment
– (“Insert Into Tbl1(Field1,Field2,Field3)
Values(@Field1,@Field2,@Field3);Select Field2 From
tbl2″,connection)
then show message error “Characters found after end of SQL statement”

I’m hoping somebody here knows what might be happening.

Thanks,
ix-one
on January 18, 2010 at 6:27 pm | Reply Pinal Dave

Hi,

The error are not from SQL server. They are from connection object that you are using to connect to SQL Server.

Regards
Pinal Dave
on January 21, 2010 at 9:37 pm | Reply Tweety

Hello Dave

I have a strange issue and just wanted to know if it is possible in SQL 2005. Can you please guide me on this.
I have a column with data like
223 245 356
223 356 222
223
456
223 456
etc (these are 3 digit numbers ie; first row contains 3 different values and last row has 2 different values)
Is there a way that I can divide this data and insert into different rows.
Example, in one row I have to insert the first value of the first column, 223 and in the next row I have to insert 245 and in the next 356 and so on. So the data present in this column would go into 10 rows. Am I clear?
Please let me know if this can be achieved. Thank you for your time.

Regards
Tweety.
on January 22, 2010 at 4:22 pm | Reply Pinal Dave

Hello Tweety,

use the below function:

CREATE FUNCTION [dbo].[Split](@List VARCHAR(6000), @SplitOn VARCHAR(5), @MaxRowCount INT)
RETURNS @RtnValue TABLE([Value] VARCHAR(100))
AS
BEGIN
DECLARE @IDENT TABLE (COL1 INT IDENTITY, COL2 BIT)
DECLARE @COUNT INT
SET @COUNT = 0
WHILE @COUNT < @MaxRowCount
BEGIN
INSERT INTO @IDENT VALUES(1)
SET @COUNT = @COUNT +1
END;

WITH CTE(START) AS
(SELECT DISTINCT CHARINDEX(',',','+@LIST+',',Position)
FROM (SELECT COL1 Position FROM @IDENT WHERE COL1CTE.START)-START-1)
FROM CTE
WHERE START<LEN(@LIST)+2
)
INSERT INTO @RtnValue (Value)
SELECT Substr FROM Substrs
RETURN
END

–Use this funtion to split the string as below:
SELECT * FROM dbo.Split ('asd,asda,sdf,sdf,dfg',' ',100)

Regards,
Pinal Dave
on January 29, 2010 at 3:06 pm | Reply shanmuganathan

hi sir,
good noon sir, i want the query details following task..

Task: i have one document *.doc or *.pdf or *.txt anything. it contains the table and the fields are stuid,stuname. Then i create one table in named student and set the fileds as same for the doc.
Then how to data insert document into database table..
on January 29, 2010 at 8:36 pm | Reply Pinal Dave

Hello Shanmuganathan,

The best methos id to use Import/Export wizard to import data from file into SQL Server table. Otherwise there are other options like OPENDATASOURCE or BULK INSERT.
For repeated use create a SSIS package.

Regards,
Pinal Dave