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)
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?
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?
Are you sure?
Post the code used in the trigger
thanks
You need to use OPENROWSET function
Refer SQL Server help file for the sample code
How to use the Insert into select along with direct data coming from the form in SQl Server 2005?
i cant understood what u asked that question “direct data” means
I can’t understand what is “i cant understood”
on March 12, 2010 at 10:27 am | Reply chaitanya kumar svs
i cant understood
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?
u can use open rowset query to transfer ond db to other
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.
By moving, did you mean updating the table2?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger railways_result_trigger on railways_result
after insert
as
if exists(select distinct update_date from railways_result where update_date!=(SELECT CONVERT(date, getdate (), 103)))
begin
delete from current_railways_result
delete from railways_result
end
else
begin
delete from current_railways_result
insert into current_railways_result select *,dense_rank()OVER (PARTITION BY stu_batch_time,exam_date ORDER BY stu_total_marks DESC)stu_rank
FROM railways_result
insert into railways_result_backup select *,dense_rank()OVER (PARTITION BY stu_batch_time,exam_date ORDER BY stu_total_marks DESC)stu_rank
FROM railways_result
;with DelDup as (select row_number() over (partition by
stu_name, stu_roll,exam_date,stu_rank,stu_batch_time order by stu_name,stu_roll,exam_date,stu_batch_time) as RowNo from railways_result_backup)
Delete from DelDup where RowNo> 1
end
first time data not inserted into first table plz help me
This may be the culprit if exists(select distinct update_date from railways_result where update_date!=(SELECT CONVERT(date, getdate (), 103)))
Dont use convert function
my requirement is delete both table data before insert plz help me i need in project sir.
i m creating trigger
Hi devan
select * into Northwind.dbo.employee from pubs.dbo.employee
I think this would work for u
thx,
Karthi.
thankyou for this query
thank you for your querry
legend, already had the table dbo.tblStock in the “new database, simply had to delete that, then run the above query. :)
Hi Karthi,
Thanks , this worked for me.
Hello sir, I want the producure to transfer the data from one to another……….
Plz send the query
Regrads
Sriram
Sriram,
INSERT INTO database1.dbo.table (column1, column2)
SELECT column1, column2 FROM database2.dbo.table
David
wat would be the query if i want to transfer data from one to another n also add some independent values in my other columns…
i.e if i have two tables A and B , say A wid 4 columns and B with 6 columns, transferrin values from 4 columns in A to B… and i want to insert some values into other 3 columns in B…
where is the problem adding the independent values to the select-part?
INSERT INTO database1.dbo.table (column1, column2, column3, column4)
SELECT column1, column2, 1, ‘abc’ FROM database2.dbo.table
this query fails to transfer data from one table to another table across different databases…
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
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
How To Transfer The Data from One Table To Another Table…………
Insert Into TableName1(columnname1,columnname2…)
Select columnname1,columnname2… from TableName2
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.)
You need to apply pagination technique using row_number() function. Refer this post for more informations
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx
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!
select distinct * into Table2 from Table1
This is not correct
Read the question again
The correct answer is
insert into table2(col)
select col from table1 as t1 where not exists
(select * from table2 where col=t2.col)
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.
select distinct * into Table2 from Table1
Thanks SHEEBA
Its useful for me.
Hello,
How Can I copy my local sql server 2005 table’s data to my host database?
Thanks
Take a backup and restore it over host server
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
Does the column has default constraint? If so remove it
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
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
Note that this site is for MS SQL Server
Are you using it?
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
Insert into table1(col_list)
select col_list from table_2
where some_condition
i want to import the data into a table from other table which is in different Database please help me out
Use three part name
Insert into table1(col_list)
select col_list from db_name.dbo.table_2
where some_condition
very bad answer dont give this type of answers.
Why do you think it is a bad answer?
Insert into table1(col_list)
select col_list from db_name.dbo.table_2
where some_condition
My question If Col_list is differred from the other table.
How to populate?
Please help..
You can join the respective tables and take needed columns
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
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.
Hi,
I want to insert data from one server database table
to another server database table in vb6 code
any one can help me
You need to make use of Linked Server
Read about sp_addLinkedserver in SQL Server help file
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
Insert into table(col_list)
EXEC proc
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.”;
}
}
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.
Make another server as linked server. Then write this code
insert into table1(col_list)
select col_list from servername.dbname.ownername.table1
where
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.
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
Insert into table EmployeeInfo(Id,firstname,lastname, Salary) Select salary,1,’aaa’,xyz’, from Payroll_Info where EmpId = 1
HOW TO INSERT VALUES FROM ONE TABLE TO ANOTHER AND ADD THE TIME (GETDATE) AFTER A FORM SEARCH WITH FRONT PAGE
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.
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.
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.
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.
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?
101 character does not matter. It will now affect the performance. But what is the need of having such long name?
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’)
What did you mean by “Its not working?”
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
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
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
Thanks, just what I needed.
Hi Dave,
Nice Blog. Helped Me alot..
Thanks ;-)
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.
Format is
col like '([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9]'
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
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′
)
Can you tell me the naming conversion and the format of data passing in OPENROWSET …what is ‘sa’ stand for?
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
Hi,
I want to insert data from one server database table
to another server database table in vb6 code
any one can help me
I want to insert data from one server database table
to another server database table in oracle.
pls help me…
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
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
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
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???
And I also have already use indexes…
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
hey I got it.Thanks for the post.
Hi
Can we insert data from one table to other which is not the same database?
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 !!!!
hi,
i want check duplicate data while insert into sql table
Can any one help me how to do that
Hi GC (51),
What is the error you are getting?? Would help to solve your problem.
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
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.
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.
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.
i want to copy data from one column to another column inside a table.anyone can help me?
jewel
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?
@ 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.
@ Joji
The best way to copy a table with its indexes, defaults, constraints, triggers and all other stuff related to a table is …
Script the table :
1. In object explorer, right click on the table and then select SCRIPT TABLE AS : CREATE TO
REMEMBER: WHEN YOU TRY TO SCRIPT IN SQL SERVER 2000, make sure you uncheck the option DROP if exists. IF THIS IS CHECKED then you will loose your original table. ( If you run the script in the QA in the same database )
When you get the script, then copy the script and run in Query Analyzer (2000) or SSMS connecting to the database in which you want to create the table.
once you run the script then you will have your table ready with all your constraints, indexes, defaults, triggers….
BUT after table is created, you will NOT use SELECT * INTO statement, now you will use
INSERT INTO new_table_name SELECT * FROM old_table_name
Hope this helps.
@ Mark
This is what I understood, please correct me if I am wrong …
Your explanation gives answer for your question : You said you are trying to update the data in table dbo.sim_control_table, but you know what, you are trying to insert the data when you want to update it.
Solution would be : Dont use Insert statement, use update statement, your problem will be solved.
This is a small example:
CREATE TABLE UEXAMPLE1 ( EID INT, EID1 INT , EID3 INT )
CREATE TABLE UEXAMPLE2 ( EID INT, EID1 INT , EID3 INT )
-> I created two tables which are almost same, This is what you did, two tables with different names but they have same column names and that too in the same order :)
INSERT INTO UEXAMPLE2 VALUES ( 1,2,3)
INSERT INTO UEXAMPLE2 VALUES ( 2,2,3)
INSERT INTO UEXAMPLE2 VALUES ( 3,2,3)
-> I inserted three rows in Uexample2
SELECT * FROM UEXAMPLE2
-> checking values using select statement.
INSERT INTO UEXAMPLE1 VALUES ( 1,3,4)
INSERT INTO UEXAMPLE1 VALUES ( 2,4,4)
INSERT INTO UEXAMPLE1 VALUES ( 3,4,5)
-> Now insert three rows in Uexample1, I wantedly inserted different data in this table, just to distuinguish… but you can see my first column in the two tables its exactly same. This is what you did :)
SELECT * FROM UEXAMPLE1
-> Checking the values in Uexample1.
INSERT INTO UEXAMPLE1 (EID1, EID3)
SELECT EID1 , EID3
FROM UEXAMPLE2
WHERE UEXAMPLE1.EID = UEXAMPLE2.EID
-> This is your T-SQL statement, I did the same thing, I am trying to update the value in Uexample1 from Uexample2 using my first column as macthing, this is what you did in your case.
Know what : I got the same error :)
Error: Msg 4104, Level 16, State 1, Line 2
The multi-part identifier “Uexample1.eid” could not be bound.
SOLUTION : Now let us try update for the same table.
T-SQL code will be changed slightly,
UPDATE UEXAMPLE1
SET UEXAMPLE1.EID1 = UEXAMPLE2.EID1 , UEXAMPLE1.EID3 = UEXAMPLE2.EID3
FROM UEXAMPLE2
JOIN UEXAMPLE1 ON UEXAMPLE1.EID = UEXAMPLE2.EID
-> Instead of INSERT we will use UPDATE and instead of WHERE CLAUSE we will use JOIN CLAUSE.
Check the output.
@ J.Marchena
I tried working on your problem… I dont know how to implement this in Access 2007. I tried in SQL Server 2005.. same applies to SQL Server 2000 too.
I will explain my logic and I will ask the question I have .. because I got some errors while executing quiries…
This is what I did in this example :
1. I create three tables, by name: Uexample1, Uexample2, Uexample3
CREATE TABLE UEXAMPLE1 (EID INT, STATE CHAR(2))
CREATE TABLE UEXAMPLE2 (EID INT, STATE CHAR(2))
CREATE TABLE UEXAMPLE3 (EID INT, STATE CHAR(2))
Same tables with different names.
2. Now I created a trigger on the table UEXAMPLE1. This triggers inserts data in UEXAMPLE2 and UEXAMPLE3 tables when ever you try to insert the data in UEXAMPLE1 table.
CREATE TRIGGER TR_UEXAMPLE1
ON UEXAMPLE1
FOR INSERT
AS
DECLARE @EID INT
DECLARE @STATE CHAR(2)
SELECT @EID= EID, @STATE= STATE FROM INSERTED
INSERT INTO UEXAMPLE2 VALUES (@EID , @STATE)
INSERT INTO UEXAMPLE3 VALUES (@EID , @STATE)
This could be one solution when you insert data in UEXAMPLE1 and you want that data in UEXAMPLE2 and UEXAMPLE3.
But that is not your concern, you said you are trying to copy the data, which means you will not insert data in UEXAMPLE1.
For that we will try the same logic, but this time we will create the trigger on UEXAMPLE2, when ever a row is inserted ( while copying fromUEXAMple1) into UEXAMPLE2 this trigger will insert the same row in UEXAMPLE3
CREATE TRIGGER TR_UEXAMPLE2
ON UEXAMPLE2
FOR INSERT
AS
DECLARE @EID INT
DECLARE @STATE CHAR(2)
SELECT @EID= EID, @STATE= STATE FROM INSERTED
INSERT INTO UEXAMPLE3 VALUES (@EID , @STATE)
So you dont have to write two quiries to copy the data into two tables.
There is one more way you can do this … you can create a view on either UEXAMPLE1 or UEXAMPLE2 and create a INSTEAD OF trigger on any one of the view, will do the same thing.
ISSUES: I am not genius… no where near .. while doing the above examples… I got many errors…
1. like when I use INSTEAD OF trigger on Uexample2, the data doesn;t go in the table UEXAMPLE2 … but it is directly goes to UEXAMPLE3. This is strange.
2. When I execute :
INSERT INTO UEXAMPLE2 SELECT * FROM UEXAMPLE1
and I have trigger on UEXAMPLE2 which inserts records in UEXAMPLE3… I saw that only one row is copied in UEXAMPLE3 but all the rows have been copied in UEXAMPLE2… This is also strange to me … ?
please clarify … I spent enough amount of time on this !
@ viswa (38)
This could be one solution … like you said I used cursor in this … but not sure if there is still any better solution for this …
This is the example :
CREATE TABLE UEXAMPLE1 ( COLA INT, COLB INT )
CREATE TABLE UEXAMPLE2 (COLA INT , COLB INT, COLC INT, COLD INT)
-> created two tables, UExample1 and UExample2 , they have 2 columns same between them, UExample2 has two extra columns.
INSERT INTO UEXAMPLE1 VALUES ( 1 , 123)
INSERT INTO UEXAMPLE1 VALUES ( 2 , 223)
INSERT INTO UEXAMPLE1 VALUES ( 3 , 323)
-> I inserted three rows in UExample1. Three differenrt rows.
INSERT INTO UEXAMPLE2 VALUES ( 1, 244 , 333 , 444)
INSERT INTO UEXAMPLE2 VALUES ( 2, 244 , 333 , 444)
INSERT INTO UEXAMPLE2 VALUES ( 3, 344 , 333 , 444)
INSERT INTO UEXAMPLE2 VALUES ( 4, 444 , 333 , 444)
-> Then I inserted four Different rows in UExample2, first column in both the tables is common and has three different values. This is to create your condition… if it exisits then update if not Dont…. So there are three rows in Uexample1 which exists in UExample2.
CREATE PROC USP_UPDATE
AS
DECLARE @COL INT
DECLARE @COLA CURSOR
SET @COLA = CURSOR FOR
SELECT COLA
FROM UEXAMPLE1
OPEN @COLA
FETCH NEXT
FROM @COLA INTO @COL
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS (SELECT COLA FROM UEXAMPLE2 WHERE COLA= @COL)
UPDATE UEXAMPLE2
SET UEXAMPLE2.COLB = UEXAMPLE1.COLB FROM UEXAMPLE1 WHERE UEXAMPLE1.COLA= @COL AND UEXAMPLE2.COLA= @COL
ELSE PRINT convert(varchar(10), @col )+ ‘ DOES NOT EXISITS IN UEXAMPLE2′
FROM @COLA INTO @COL
END
CLOSE @COLA
DEALLOCATE @COLA
GO
-> I create a stored Procedure which is checking if there is any row in cola in Uexample2 which exists in UExample1.
If yes then It is updating that colmn with the value of UExample1.
If not then it is printing … it does not exists in UEXAMPLE2.
Hope this helps … I am not sure if this is a good solution, since I used Cursor in this …
hello, friends,
I want to insert data into one table(save_documen_v) from another table(document_v).
i have 3 more columns r there in save_document_v then document_v.other columns r same.
i want to insert record of that different column from outside n in the same row i want to insert selected record from document_v table.
if i pass the query–
insert into save_document_v
(member_code,class_no) values (1102,(select
class_no from document_v where (acc_no=’D 1173′)))
then it works.
but when i m trying to insert into more columns like-
insert into save_document_v
(member_code,class_no,acc_no) values (1102,(select
class_no,acc_no from document_v where (acc_no=’D 1173′)))
it not work.
i have 16 columns same in both table.
n 3 more columns in save_document_v(member_code,m_type,display)
if any one has solution plz give me.
thanks.
hi,
related to my previous post..
if in that problem i write this query-
insert into save_document_v (member_code,m_type,display,class_no,acc_no,
author,author_2,title,yr,imprint,guide_1,guide_2,
place,source,clnt_spon,discipline,type,remarks,abstract)
values (1101,’document’,'yes’,(select class_no,acc_no,author,author_2,title,yr,imprint,
guide_1,guide_2,place,source,clnt_spon,discipline,type,
remarks,abstract from document_v where acc_no=’D 1173′))
its give error–
ERROR at line 4:
ORA-00947: not enough values
You know what I would love to figure out is how to write an “execute select” statement. In other words,
execute dbo.doSomething @param1, @param2, @param3
select column1, column2, column3
from table
such that the value of column1 is passed to @param1.
Right now, I write something like the following:
for scripts
select ‘execute dbo.doSomething ”’ + column1 + ”’, ”’ + column2 + ”’, ”’ + column3 + ””
from table
for stored procedures
select @value1 = column1, @value2 = column2, @value3 = column3
from table
execute dbo.doSomething @value1, @value2, @value3
It gets the job done, but I’m *convinced* that there’s a better way I am overlooking. Any suggestions on where to start reading?
Thanks!
@ Kruti
This is what I understood, please correct me if I am wrong,
You have two tables, lets say UEXAMPLE1 and UEXAMPLE2, one of the two tables has 3 extra columns, 16 columns are same in both the tables.
Here is the example …
CREATE TABLE UEXAMPLE1 ( EID1 INT , EID2 INT ,EID3 INT )
CREATE TABLE UEXAMPLE2 ( EID1 INT , EID2 INT , EID3 INT , EID4 INT , EID5 INT)
–> I created two examples UEXAMPLE1 and UEXAMPLE2. In these UEXAMPLE1 has only 3 columns and UEXAMPLE2 has 5 columns out of these 5 columns in second table, three columns are common for both the tables.
INSERT INTO UEXAMPLE2 VALUES ( 1, 2, 3, 4, 5 )
INSERT INTO UEXAMPLE2 VALUES ( 11, 12, 13, 14, 15 )
INSERT INTO UEXAMPLE2 VALUES ( 21, 22, 23, 24, 25 )
INSERT INTO UEXAMPLE2 VALUES ( 31, 32, 33, 34, 35 )
INSERT INTO UEXAMPLE2 VALUES ( 41, 42, 43, 44, 45 )
–> Inserted values in the second table.
Problem : Now when you said you are trying to insert the data from second table into the first table. Your query works fine when you select one column from second table but when you select two columns your query does not works,
Solution : The problem is you cannot use one select statement in that way for more than one column. I am not sure but if you write select query for every column seperated by a comma then hope that will work
like this ( I did not execute this query, I assume this will work ) :
INSERT INTO SAVE_DOCUMENT_V (MEMBER_CODE,CLASS_NO,ACC_NO)
VALUES (1102,(SELECT CLASS_NO FROM DOCUMENT_V WHERE (ACC_NO=’D 1173′)), (SELECT ACC_NO FROM DOCUMENT_V WHERE (ACC_NO=’D 1173′)))
My solution :
For your first query this could be a replacement :
INSERT INTO
UEXAMPLE1 (EID1 , EID2)
SELECT ’10′, EID2
FROM
UEXAMPLE2
WHERE
EID5 = ’15′
Explanation : Here you are using a select statement and you are passing the value which you want to insert in the first column and in the second column you are copying the value from second table by giving a where clause.
For your second query this could be repalcement :
INSERT INTO
UEXAMPLE1 (EID1 , EID2, EID3)
SELECT ’20′, EID2, EID3
FROM
UEXAMPLE2
WHERE
EID5 = ’25′
Explanation : Here you are using a select statement and you are passing the value which you want to insert in the first column and in the second column and third column you are copying the value from second table by giving a where clause.
Hope this helps…
Hi
I wondered if anyone could help me?
Pinal’s blogs have proved very useful so far but I have been struggling to do something in sql server 2005 now for over a week. I’m positive it is something quite simple yet I am a novice at sqlserver and it is making me lose the will to live.
OK
My overall aim is to have two versions of the same database (AdventureWorks) on the same server so that I can run a series of tests using various sql comparison tools and compile a report based on my findings.
Using Pinal’s blog I have so far managed to copy what appears to be a skeleton of the DB. I.e. all of the dbo’s have been successfully copied but I cannot seem to get any data to copy into these tables.
As I said above, I am a novice at SQL and SQL Server so if the answer is obvious please show a little patience.
If anyone can help with getting the data into the tables or suggest a whole new approach which will enable me to have two instances of the same database on the same server I would be much obliged.
The renaming of databases was a whole other kettle of fish over which I effectively wasted 2 days work.
I’m sure this is a simple thing to do but I feel I have exhausted all obvious avenues.
regards
Jobby
hi sir ,
can u pls help me???
how can i retrieve all the data from the db row wise to my asp page table???
INSERT INTO DestinationDatabase.dbo.DestinationTable (FIELDS Name)
SELECT FIELDS NAME
FROM SourceDatabase.dbo.SourceTable
khub saras kaam chhe. mane khub gamyu….!!! tame brahman bhai lago chho…!!!! aatlu saras parinam aapva badal khub khub abhinandan…!
can u tell me how can I copy data from one database table to another database same table… ???
can u tell the types of trigger..
@Priya :
This is what I know, please correct me if I am wrong
There are basically two types of triggers in sql server 2005 (DML & DDL) and only one type (DML) in sql server 2000.
Sql Server 2000.
1. DML Triggers: Again in this we have two more types of triggers,
a) ” INSTEAD OF ” TRIGGERS
b) ” FOR ” or ” AFTER ” TRIGGERS
a) INSTEAD OF : Again in this we have three types of triggers,
1. DELETE
2. INSERT
3. UPDATE
b) ” FOR ” OR “AFTER” TRIGGERS: Again we have three types of triggers just like “INSTEAD OF”
1. DELETE
2. INSERT
3. UPDATE
These are total six types of triggers available in Sql server 2000. These triggers gets executed when ever you insert, update or delete.
Now in sql server 2005, they have created one more Trigger named DML, these triggers gets executed when you execute any DML : create , alter, drop statements.
DDL Triggers: We have many triggers under this category available for all kind of DDL statements, to get more information you can refer to books online… the list is very big…..
Please refer to this example to know more abotut DDL triggers,
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.
Sql Authority is really help me to know different type of query mistry.
In the previous post, there are some mistakes.
By mistake I wrote DML triggers in sqlserver 2005 , it is DDL ( Data Definition Language) – Create , Alter Drop.
2000 – only DML ( Data manupulation Languae) – insert , update , delete.
2005 – DDL and DML.
Hi, I have a query
i have 2 databases ‘A’ and ‘B’ both SQL SERVER 2005,
and i want at such a thing that at some specefied time everyday say (22:00 hours) data to be selected from few columns from table in databse ‘A’ and the to be inserted into specified the table in database ‘B’.
and this process should run self that is self executing.
is this possible
i have one prob is that
i want to select the particualr row from sql database & insert into ms access database how it is possible
how can i write the query in c# appliaction
plz help
i have created student table where student_id is the primary key for that table. another table is logbook_entries where student_id is a primary key and it also a foreign key where it refers student_id in student table. i do not how to retrieve the student_id to logbook_entries from student table.
how can i write the query.
plz help me out.
hi,
i want to write trigger on table of one database for insert which insert that record into table of other database……..
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
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
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.
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
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
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.
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?”
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..
Your site is very clean, and easily readable. Thanks for your generous help. you’re the best!
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.
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…
Thanks all for such a good posts
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.
INSERT INTO TABLE 2(SELECT * FROM TABLE 1);
Works great on an Oracle DB.
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
How to insert data(only one column) from source table to another table
its very urgent need please send me the query.
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
@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,
good,
Hello sir,
I want a I want update a recored from another table at once using a select query. So please tell me query
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
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.
@ 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.
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?
@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 :)
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
surely just write a dts that goes:
insert into tableA
select recordnames from tableB
???
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
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………………:-)
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?
@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.
@ 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.
begin tran
insert into table1
select max(id)+1 as id ,’name’,'rollno’ from table1
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
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
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..
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
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???
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
@shameer
How can you insert data in the column when you dont have the column in the destination table.
Thanks,
Imran.
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
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
Hi Dave,
How do you insert data from text box into table?
Regards,
Zulfi
@Zulfi Asdani
You can do it in two ways,
1. Use bulk copy command from command prompt or sql server and import that data from text into sql server.
2. create a dts package, select source as text file and destination as SQL Server and you can import all data into sql server from text file ( very easy).
Try looking on internet you can find many examples.
Hope this helps,
Imran.
how to run a insert query so as to insert a column from remote server to local DB, this column contains some sql queries so these queries should also get executed while inserting the column.
Hi,
have 3 tables in one server database and want to copy that data in to my Local database (b’cos I want to work with that local data with out effect that original data)
Please help me.
@Anu
You can use SSIS or DTS to do that.
IF you are using SQL server 2005, right click the database–>Task–>Export. Then everything is straigth forward from there on wards.
hope this helps
Hello
I am very new to write SP,so need guidance in doing the following.
I need to write a stored procedure,which will insert data into 3 tables.
These are the 3 table
Table-TOCHeaderSchema
Columns-TOCHeaderSchemaID
-TocHeaderScemaNameText
-CreatedByLID
Table-TOCHSElement
Columns-TOCHSElementID
-TOCHSLevel
-TOCHSPrefixText
-TOCHSCounterStyleID
-TOCHeaderSchemaID
Table-TOCHSCounterStyle
Columns-TOCHSCounterStyleID
-TOCHSCounterNumber—-this is autogenerated
-TOCHSCounterLabelText
Basically these tables are changing the numbering of elements.
like for example.
this is the numbering of a document named Transportation TP 1.1.1
so this will be changed to 1.A.1 or 1.a.1 depending upon the choice.
so the table TOCHSCounterStyle is like
TOCHSCounterStyleID —1
TOCHSCounterNumber—-1,2,3,4,5(autogenerated)
TOCHSCounterLabelText—-A,B,C,D,E
TOCHSCounterStyleID —2
TOCHSCounterNumber—-1,2,3,4,5(autogenerated)
TOCHSCounterLabelText—-a,b,c,d
TOCHSCounterStyleID —3
TOCHSCounterNumber—-1,2,3,4,5(autogenerated)
TOCHSCounterLabelText—-i,ii,iii,iv,v…
Thank You
@ blue star
Need more information ….
Its really confusing !!! Please provide more information with input and how you want your output to be.
Thanks
Imran.
Hello Pinal Dave,
i have a problem regarding data transfer.
The scenario is there are 2 database DB1 and DB2.
Both the database having a table TAB1 with same structure.
Now when data is inserting into DB..TAB1 we are firing a Trigger TGR1 to insert the same data into DB2..TAB1. But the concern is on performance…
So is there any other way to insert the data in DB2..TAB1 after inserting into DB1.TAB1?????
If is there any error while inserting into DB2..TAB1, trasaction under Trigger will be ROLLBACKED.
plz help soon its urgent.
Thanks in advance
Hi , thank for the script in your document, very useful.
My friend and i tried it, and did work but the next thing we want to do was to add a new column to the table having received the data, and update that column the same way as in the insert into , but it happens that the data are appended from the last row of the table , leaving the two initials table blank.
That the script:
//Create a new Table
create table Info
(FirstName VARCHAR(100), LastName VARCHAR(100))
//Inserting the data
INSERT INTO Youssef(FirstName, LastName)
SELECT FirstName, LastName
FROM AdventureWorks.Person.Contact
//Altering the Table
alter table Info
add Email VARCHAR(100)
Here is where i get stack, it update from the last row leaving the Firstname and Lastname blank
update Info
set Email = (SELECT EmailAddress
FROM AdventureWorks.Person.Contact)
i don’t want to use a procedure and looping construct.
thanks
@@Imran
How Can I copy my local sql server 2005 table’s data to my host database?
It would be helpfull if you can provide me the code.
Currently im doing this by using Import/Export Wizard and its a very long process.
Thanks in Advance!!
Hello Dave,
I’m having two tables
Table1: Table2:
ID Date Month Jan Feb Mar Remarks
1 1 Jan xx xx xx xxxxx
2 14 Jan xx xx xx xxxxx
3 26 Jan xx xx xx xxxxx
4 14 Feb xx xx xx xxxxx
5 13 Mar
I want distinct values of Month in Table1 to be the Column Name of Table2 as above said:
can you help me out
Thanks & Regards
Surendar K
Hi,
I need a help
I had a Query that when we delete the data from any table
from a particular database in SQL Server 2005, the deleted data has to insert in the DeleteLogTable, using triggers
DeleteLogtable Columns
TableName ColumnName DeletedValue
Plz solve my problem.
Very usefull Notes
Hi Dave,
I have 2 tables as @temp1 and @temp2 such as
@temp2
id name
1 pavan
2 raj
2 kumar
2 pav
1 tamma
and @temp1
id city
1 nagpoor
2 poona
and want new table @result as
id city name
1 nagpoor pavan,tamma
2 poona raj,kumar,pav
please tell me how to do this with stored procedure or UDF
Hi,
pavan mainde
I hope this query may help u to get ur output.
Select T2.Id, T2.Name, T1.City from @Temp1 T1, @Temp2 T2
where T1.Id= T2.ID
after about 5 years of writing database applications , i am still confused with insert with select!!
Hi,
I need a help
I had a Query that when we delete the data from any table
from a particular database in SQL Server 2005, the deleted data has to insert in the DeleteLogTable, using triggers
DeleteLogtable Columns
TableName ColumnName DeletedValue
Plz solve my problem.
Hi Dave
I have to move some tables from one databes to another on the same SQL server 2005.
Actually I have to do this a few times, move it from DB1 to DB2, from DB2 to DB3, from DB3 to DB4…, all of them on the same server.
The respective databases and tables have different names.
In fact only the beginings or the endings of the names are different.
I was thinking to this with INSERT INTO – SELECT FROM
statements. I have moved data from DB1 to DB2, that was no problem. To move it again I have to write new script with the new names.
Can I somehow use one script, where I can do something about the table and database names. The manes of the columns are all the same in all databases.
Can you please help me. I have about 50 tables. How can I automate this procces?
Should I use BCP instead?
I would really appriciate your help.
Grateful M
could u pls guys help me with a insert procedure that will allow 2 records to be inserted inone column
Hi Dave,
i am asking this query from 29 Sep 2008.
Plz help me to solve the problem.
I need a help
I had a Query that when we delete the data from any table
from a particular database in SQL Server 2005, the deleted data has to insert in the DeleteLogTable, using triggers
DeleteLogtable Columns
TableName ! ColumnName ! DeletedValue
Plz solve my problem.
If guys any one has any idea on this plz give me the reply.
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
I want to insert records from more than one table into a new table..
can anyone help??
thanks in advance !!!!
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
Hi
Thanks for the post. Helped me a lot. Seems to have generated a few questions though…
Thanks
Paul
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
list advanced t-sql querying methods
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!!!!
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.
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!
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
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!!!!
Hi
PL help me, i want export data through query in SQL
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.
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
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
how i can insert data from one server database table
to another server database table through sql query.
Read about sp_addLinkedServer in SQL Server help file
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
Thank you, this was exactly the procedure I was looking for and worked perfectly for me!
Good job!
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.
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
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
@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
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
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!
Very nice blog. I appreciate your efforts. It works for me.
Can you please let me is it possible with one query?
Kunal Mehta
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.
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
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.
I’ve now opened connections to both databases but still have no success. (Neither database has a password….)
Love you work Dave,
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
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..
How do you read data automatically from a table with a newly inserted data on it?
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
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
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′
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
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.
Thanks Mr Imran Mohammed, It is working I was missing a dot, it should be double dot
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!
@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.
[...] SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE Insert multiple records using SELECT statement. [...]
[...] (Read More Here) [...]
[...] Instead of using LOOP to insert data from Table B to Table A, try to use SELECT statement with INSERT statement. (Read More Here) [...]
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
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!
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
and my sql query is
select accnt_name into testtable
from inturi.c_account
where row_id=’460-2BF’
@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.
@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.
@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.
@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.
Hi!!
i have just started working on SQL i need sum guide line how to create own table can u help me …
RAFAY PERVAIZ.
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
Hello,
I want to update my table a from database A with table b from database B.
Has anyone any tips?
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
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….
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.
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 .
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 ??
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
thanks vry much..it helps me a lot…
This Data Has completely resolved my problem.
For this kind help thanking you so much
how to write a cursor to loop through the records of one table and insert them into a temp table?
Can anybody tell me how to write a cursor to loop through the records of one table and insert them into another table?
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
@tdcl
Simple example of cursor:
http://blog.sqlauthority.com/2007/01/01/sql-server-simple-example-of-cursor/comment-page-1/
~ IM
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
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?
excellent post! helped me tons.
thanks.
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))
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 :)
How To Protect Password in database using sql query
2005.
Pl help me.
@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.
I want to insert data from one server database table
to another server database table in sqlserver 2000.
pls help me…
@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
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
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.
@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.
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.
Thx for this one, it’s really interesting
Thx again
pls reply me i want to connect my excel file with my sqlserver 2005,
so tell me how to that!!
@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
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!
Hi dave,
How to copy data from columns which are on a different server.
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
how to multiple rows in the sql 2000through vb6.0 with the concept of RDBMS.
please help me
Its a very helpfull article. Very simple and to the point. Thanks.
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
Thanks Pinal,
Will select into statement copy constraints also?
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
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.
[...] 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 [...]
you are the best
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
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!
SEND ME A FUNCTION PROCEDURE
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
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
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
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
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
Hi Dave,
How do we pronounce u’r name.
thanks in advance. :)
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
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
@Harish
Is there a way top combine this into one statement?
INSERT INTO …. SELECT … FROM
@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
@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.
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
Thanks so much, the first useful bulk insert article!
;)
Dear All,
can anybody have answer of how to swap data from one identity column to same column in sql server 2005 table
Hi,
I wanted to know the alter command to add a column in middle of other columns in existing table.
@Sneha.
I am not aware of any direct way of doing this.
You could do this from SQL Server Management studio easily, but if you see Generate Script for this, you would see that
1. Your original table is copied into a temp table,
2. Original table is dropped and
3. A new table is created with column added with the specified column location and
4. Then data is copied from temporary table to this new table.
5. And then name of this new table will be renamed to your original table.
~ IM.
i have to same database with exactly same tables!
i want to add data from DB A into DB B.
i want to check if the primary key of a table in DB A doesn’t exist on DB B insert that record else update that record!
help me !
@Ehsan.
What is the concern. You explained your scenario. But what is that you want from us.
You want us to write a script for you or are you looking for any solution from us on how you can implement your requirement.
Please briefly tell us what is that you want us to do ? what kind of help you are looking for.
~ IM
Hi Ehsan,
You can do this as:
–Insert code
INSERT INTO TableA(columns)
SELECT Columns
FROM TableB b
WHERE NOT EXISTS(
SELECT a.Id
FROM TableA a
WHERE a.ID = b.ID
)
–Update code
UPDATE TableA
SET Columns = b.Columns
FROM TableA a
INNER JOIN TableB b ON a.ID = b.ID
Let me know if you have any questions.
Tejas
SQLYoga
Great post !!!!
Hi Tejas Shah,
I have a few slow performing T-SQL cursor stored procedures that I want to replace with normal T-SQL (i.e. remove the cursors). Can you provide examples.
Thanks
Hi Clive,
First of you need to remove cursors.
What you need to do is:
Populate Table variable or Temp Table with the data, and then loop it thru as:
Please find: h ttp://www.sqlyoga.com/2009/12/sql-server-how-to-remove-cursor.html
Tejas
hi i want to ask one question. plz can u people tell me how to copy whole data from one server to another server using SQL both at diffrent places.
@sana
Quickest method is to use backup – restore. Now, it depends on your business requirements.
Hi all,
I am working to an c# win form application with sql server.
I have to table “father” and “child”, 1:n.
I am populating forms with data and after i have to transfer data into database. i have some text object for “father table” and one datagridview with more lines for “child table”.
I want to use a Sql Server procedure. How can I pass all informtion (text object and datagrid), as parameters, to the procedure?
Is it possible to use XML.
For populating the grid I use SqlDataAdapter and DataSet.
thanks in advance
tomi
Hi sana best is back up and restore wizard . either you can write a script
restore database
from
with replace
insert into Emp_Perform_SecA_Details(empcode,CycleStartDate) values (’000740′,select settingvalue from applicationsettingmaster where settingname=’AppStartDate’)
it is getting error, what is the solution for that
Correct way is
insert into Emp_Perform_SecA_Details(empcode,CycleStartDate)
select ’000740′,settingvalue from applicationsettingmaster where settingname=’AppStartDate’
I want to select all from table A and select table B where A.id=B.id
like
table A
id=1
id=2
id=3
id=5
table B
id=2
id=3
id=4
out put should be
A B
1
2 2
3 3
5
can any one tell what the query is.
Hi 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
[...] 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 [...]
Hi,
I have one problem.. Problem is-
I want to insert records into one table say “NewCountry” from other “Country” table but this “Country” table is located at different MS-SQL server so how can i insert records?
Hi Mandar,
If you want to do only through t-sql query then either use OPENDATASOURCE function or create a linked server and then use OPENQUERY function or by 4 part naming.
If you are not sticked with T-SQL then better use import/export wizard and you can save it as a SSIS package for future use.
Regards,
Pinal Dave
Hi Sir,
Thanks for reply. Your articles are amazing.
Regards,
Mandar Kavishwar
Hey Pinal,
Its really grt blog. keep it up man…..
In my project I want to insert data to a table from another table in the database
I used query as follows
String st = “select code from state where name=’” + this.statename.Text + “‘”;
String str = “insert into branch(Code,Name,metro,state)values (‘” + this.txtbranchcode.Text + “‘,’” + this.txtbranchname.Text + “‘,’” + this.cmbmetro.SelectedIndex + “‘,’” + st + “‘)”;
but compile time no error
at run time it is showing error as syntax error at Karnataka
here Karnataka means my statename.text
pls correct me error
Change: “‘,’” + st + “‘)”;
To: “‘,(” + st + “))”;
The SELECT statement needs to be in parenthesis itself.
Another way to do it would be to only use the SELECT statement, and not use the VALUES clause at all:
String str = “insert into branch(Code,Name,metro,state) select ‘” + this.txtbranchcode.Text + “‘,’” + this.txtbranchname.Text + “‘,’” + this.cmbmetro.SelectedIndex + “‘,’” + code from state where name=’” + this.statename.Text + “‘”;
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
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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger railways_result_trigger on railways_result
after insert
as
if exists(select distinct update_date from railways_result where update_date!=(SELECT CONVERT(date, getdate (), 103)))
begin
delete from current_railways_result
delete from railways_result
end
else
begin
delete from current_railways_result
insert into current_railways_result select *,dense_rank()OVER (PARTITION BY stu_batch_time,exam_date ORDER BY stu_total_marks DESC)stu_rank
FROM railways_result
insert into railways_result_backup select *,dense_rank()OVER (PARTITION BY stu_batch_time,exam_date ORDER BY stu_total_marks DESC)stu_rank
FROM railways_result
;with DelDup as (select row_number() over (partition by
stu_name, stu_roll,exam_date,stu_rank,stu_batch_time order by stu_name,stu_roll,exam_date,stu_batch_time) as RowNo from railways_result_backup)
Delete from DelDup where RowNo> 1
end
sir i m creating trigger for delete two table records before insert,based on update date in table,insert into both table n get rank from first table backup to another table
but its not working ,
plz help me sir i need for project
thanks and regards
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.
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
Thank you Pinal…..
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..
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
i=how do i insert data from one table in database A to another table in database B. Both these tables are located on the same server.
Use four part name,
Insert into Databasename1.owner1.table_name1
select * from Databasename2.owner2.table_name2
~ IM.
how insert column in the middle of the table in sql?
The ordinal position of the column doesn’t matter as long as you use it in the proper place in the SELECT statement
If you still need it, do it via management studio
Hello Kunal,
Please visit this page:
http://blog.sqlauthority.com/2008/04/08/sql-server-change-order-of-column-in-database-tables/
Regards,
Pinal Dave
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?
Hello, I am desparate. I have been working this insert statement for three days and I am just getting more confused. I get one error, think I have it fixed, get another error, get that fixed and come back to the same error again.
I am trying to set up a database to track lab samples. I have a table for the SampleID (autonumber, ArtID (number), SamplePoint (Text), with a few other fields. I am trying to insert the SampleID, ArtID and the SamplePoint into a table that the technicians will be using to enter their test results that will be tied back to the samples taken table. Here is my latest code.
Private Sub Combo65_AfterUpdate()
Dim dbs As Database
Set dbs = CurrentDb
dbs.Execute “INSERT INTO FGResultsTable.(SampleID,[ArtID],[SPID]);” & _
“SELECT FGSamplesTaken.[SampleID],FGSamplesTaken.[ArticleNo], FGSamplesTaken.[SPID] FROM FGSamplesTaken;”
dbs.Close
End Sub
Please help me so I can stop screaming at my puppies.
Well you have at least two syntax errors in your stament. Here’s the corrected version. Compare it to your version and you’ll see where the problems are.
dbs.Execute “INSERT INTO FGResultsTable(SampleID,[ArtID],[SPID]) ” & _
“SELECT FGSamplesTaken.[SampleID],FGSamplesTaken.[ArticleNo], FGSamplesTaken.[SPID] FROM FGSamplesTaken”
why sql express edition cannot open the table i made. i made a table named tblAlex, every time i open the table the error occur: This is the error:
Unspecified error
(MS Visual Database Tools)
——————————
Program Location:
at Microsoft.VisualStudio.DataTools.Interop.IDTTableDesignerFactory.BrowseTable(Object dsRef, Object pServiceProvider)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.OpenTableNode.CreateDesigner(IDTDocToolFactoryProvider factoryProvider, IVsDataConnection dataConnection)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.CreateDesigner()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ScriptFactory.CreateDesigner(DocumentType editorType, DocumentOptions aeOptions, Urn parentUrn, IManagedConnection mc)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection mc, DocumentOptions options)
How to solve this problem?
i have a main table, wherein i want to delete a row based on selection from user interface.
i want to move that row to duplicate table of main say (Main_Dup)
my idea is to restore it when needed i.e., move gain the row from main)Dup to main table .
how to get this?
i am using datetime datatype for the entity/column start_date
when i am inserting only date the default time also inserting..
i just want to insert date no need of time what i have to do sir
in sqlserver2005..
Hello Santu,
Time part is always remaiin associated with date value in datatime data type column. But you can remove the time value by storing 0 with convert function as below:
convert(varchar(10), getdate(),101)
Regards,
Pinal Dave
Hi Pinal,
How are you? I have one doubt.How can i do design the tables for country,state,city.i mean these tables are separate.
Table Structure :
Country – Table
Con_Id
Con_Code
Con_Name
State – Table
Sta_Id
Sta_Name
Con_Id
City – Table
Ci_Id
Ci_Name
Sta_Id
Duplicates are not allowed.How can i write SP.
parameters (Con_Name,Sta_Name,Ci_Name)
hello
i am sandeep (bigb)
i want to copy data in one table and insert aonther table.
this table have same feilds
can you give me a solustion.
thank you
bigb
How to copy record one table and insert another table
syntax
:
insert into newtablename (new table feilds) select feild1,feild2,feild3 from tablename
example :
insert into employee_backupfile (id , name ,salary) select id, name, salary from emp2
if i want to insert data in multiple tables in a single query then how it is possible?
if i want to insert data in multiple tables in a single query then how it is possible?if yes then pls msg me in my emailid i need this.
Hello Sarika,
You can insert into two tables using OUTPUT clause as below:
insert into MyTable1
output inserted.Col1, inserted.Col2 into MyTable2
values (10,’wow’)
Regards,
Pinal Dave
Wow…SQL is becoming more fun,
Never heard of this before, is this new to SQL Server 2008.
Another way of doing this would be to create Instead of/ For trigger on table 1 and insert data into table 1, trigger will insert data into table2.
~ IM.
here also we can insert multiple records through a single insert statement if we don’t us where clause?
hi ,
i have a table of 16 column in sql that i am uploading excel file in using asp.net . all the column contains value either ‘yes’ or ‘no’.
i need to calculate the percentage of ‘yes’ in each column and i need to transfer the calculated percentage value to another table .
and one more thing , that percentage will be calculated using the “group by” clause .
please help me..
Hello Madhu,
i have created one test table say ‘test’ and i have inserted some records into it. Following is just sample of data that i have created..
ID IsActive IsVisible
1 yes yes
2 no yes
3 yes no
4 no no
5 yes no
6 yes yes
7 no no
8 yes no
9 yes no
10 yes yes
Now write query as-
SELECT IsActive,CONVERT(VARCHAR(5),(COUNT(IsActive)*100)/(SELECT COUNT(ID) FROM test))+’%’ AS PercentageWiseYes_No FROM test
GROUP BY IsActive
After executing this query i am getting result as follows-
IsActive PercentageWiseYes_N0
no 30%
yes 70%
I think your requirement was like this.
I am also new in MS-SQL Server but i think this query helps you.
Thanks.
Mandar Kavishwar.
Sir ur Code is very Effective but I want to transfer the data from one column of one table to another column of another table in existing database.
kindly Please Reply
You need update statement
update t1
set t1.col1=t2.col1
from table1 as t1 inner join table2 as t2
on t1.keycol=t2.keycol
Sir ur Code is very Effective but I want to transfer the data from one column of one table to another column of another table in existing database. without making any temporarily table
kindly Please Reply
Hi.
I have 3 tables that I’m working with. I need to update all records in Table1 with the value from a similarly named column in Table3. However, I need to use a value from a column in Table2 to get to the right record in Table3.
The common column in Table1 and Table2 is recid.
The common column in Table2 and Table3 is VendorSKU.
Finally, I need to delete all the records from Table3
Will this work correctly in a Stored Procedure?
UPDATE Table1 SET Table1.Cost = Table3.Cost
FROM Table3 c, Table2 p
INNER JOIN Table1 m ON m.recid = p.recid
INNER JOIN Table2 ON p.VendorSKU = c.VendorSKU
DELETE FROM Table3
Thanks,
Alan
I have 2 table in sql server freetrial and freetrial_backup, freetrial table contain startdate and enddate when enddate is over it should transfer all data to freetrial_backup table pls help me(startdate is registrationdate and enddate is 2days after registrationdate)
You need to make use of a job to do this
Hi Sharad,
If this is the case that you want to copy one column from one table to another table, then you can directly write an update statement, if there is one-to-one relation between these tables. Else you need to write custom logic, based on requirement.
You can use UPDATE in this case.
Thanks,
Tejas
hi,
I need to insert the data from table1 to table to.
Table1 has a primary key, and which is a foreign key in the table2.
I want to retrieve the data of primary key from table1 and insert that to table2.
Also table2 has a independent columns , so I need to insert the all columns.
Kindly please reply,
sradha.
Hi,
I don’t quite follow. If you need just some value from table1 then just fetch it make insert to table2. For example:
declare @id int
select @id = id from table1 where …
insert into table2 select @id, other, values, …
Better run that inside a transaction though.
But I think you need to elaborate your question a bit since I don’t see what could be the problem here.
DUDE! This query Rocks! It modified the column names for my use and it worked.
Thanks again
Hi Dave,
I want to import data from one main table say ‘A’ to some 200 tables with some condition like if part_no=1 then it will go to part1 table.
I am not interest to write insert query 200 times.
Plz help me on this.
Debasish
Run this
copy the result
Run them
select 'insert into part'+cast(number as varchar(10))+' select * from where part_no=A'+cast(number as varchar(10))
from master..spt_values
where type='p' and number between 1 and 200
Hello,
I have a small question.I have 2 tables.One is a main table and the other is a temp table.The main table is a table which gets populated from the data in the temp table.(This is done on a daily basis).
If we perform a select operation on Main table it lists all the data since years.
I need help with 2 things.
1) I just need to get data from today(I can set this query to system data I think).
2) I need to make sure that data in Main table is not duplicated.(That is main table should not have duplicated rows).
Two points can be seen as
insert into main_table(columns)
select columns from temp_table
where
date_col>=dateadd(day,datediff(day,0,getdate()),0)
and
date_col<dateadd(day,datediff(day,0,getdate())+1,0)
I want to insert one database comlete data into other databse ex. test.district into finla.district
What i can do
insert into finla..district(columns)
select columns from test..district
You are fabulous…I have always got what I was looking for here
hi,
can any one help me pl!!
in sql 2005.i have created a table with a column name as rid,total,cur_value and balance.
In balance column, this total value minus(-) cur_value is stored.(eg.10000-100=900).
900 is stored in balance column.
1)now,after each transaction i wanna store this balance value into the total column again automatically…how to do it (eg.now automatically this 900 must go to total column again)?
2) how to get a particular value in a table using aggregate as condition ?(e.g select name from table where MAX(id))
3)is it possible to raise alarm or alert msg in sql server 2005 ???
i have created a table in sql 2000 and i have transfered few
rows into it from other table with in the same database,now i want to push few more rows into the same table from diffirent database, is it possible in sql 2000?
You can use three part names
dbname.ownername.objectname
Are there any limitations for amounts of data that can be copied using or methods with Simple recovery mode? If copied table has 300GB of BLOBs can we still use option to copy all data to another table or we should copy by portions?
I think that we need to create table with name TestTable as shown below in Methode 2. Right?
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
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
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
i have created two table tab1 with colomn name call_no, title, author_name, edition and second table tab2 colomn call_no, user_name, title, author_name. i want to insert title and author_name from tab1 to tab2. how can do it?
Insert into table2( title ,author_name )
select title ,author_name from table1
I want to fetch the unique record from a table. I can use distict but it return records with only single column. Requirement is that all column’s values will come. I have three column emp_id, name and user_id.
Any help will be appreciable.
Thank you.
You need to post some sample data with expected result to help you
hi
itry to insert content of one table to another but my query has error
my query is below:
and error is :
i know that tables have connected toghether
but can any man help to solve this prob????
please i need!!!!!
What was the error you got?
This is a different subject.
I have some very large table which I will like select all the data in those tables to different tables by using select into. Also, I want to set the row count to 100000 at a time until all the rows a select into the new table with a loop. Here is the code I have and it is not working properly. Can you help?
SET ROWCOUNT 100000
DECLARE @RCOUNT INT
SET @RCOUNT = 10000
WHILE @RCOUNT = 10000
BEGIN
BEGIN TRAN
INSERT Tbl_archive2
SELECT * FROM archive
SELECT @RCOUNT = @@rowcount
COMMIT TRAN
CHECKPOINT
END
go
WHILE @RCOUNT = 10000
should be
WHILE @RCOUNT <= 10000
I have the cross dock making scans per HU’s. Number of scans = 6 that can happen at different times. The key is based on 2 fields 1> TPP and 2> HU
Hence I should have a data line as follows:
TPP HU Receive_Dt Stage_dt Load_Start Loading Loaded_Dt Load_Complete
HOU 123 12-10-2009 12-11-2009 12-21-2009 12-21-2009 12-21-2009 12-21-2009
But since the scans are done at multiple time stamps my database fills up like this
TPP HU Receive_Dt Stage_dt Load_Start Loading Loaded_Dt Load_Complete
HOU 123 12-10-2009 12-11-2009 – – – -
HOU 123 – – 12-21-2009 – – -
HOU 123 – – 12-21-2009 12-21-2009 12-21-2009 12-21-2009
The other problem is the sheer volume of HU’s processed hence I do not know an ideal way to create a stored procedure that will index each key and plug in the dates accordingly. Any tips or examples you can share?
I perform this operation many times but never heard that SQL can create New Table for me as you described in Method 2.
Thanks you sir
Note that the target table will not have any indices, constraints,etc if you used method 2
i want a single query “to delete a record from table1 and insert that deleted record to table2″
or
“i want to move a record to another table”
please help me
Hi Mukherjee,
You can use this query to move data from one table to another table:
DELETE
FROM SourceTable
OUTPUT
deleted.Column1
,deleted.Column2
INTO DestinationTable
Thanks,
Tejas
SQLYoga.com
I had created a table student.and exected it. i then wanted to add more columns to the student table but i get a ‘create table denied’ message
hi,
i want to insert row in a table where two values are given which are to be inserted as it is but thje third value is to be fetched from the other table, how can i do this using Insert statement.
insert into abc (aa,bb,cc)
values (‘xx’, ‘yy’, –)
insert into abc (aa,bb,cc)
select ‘xx’, ‘yy’, col from other_table
Hi,
I have a MDF file (database) and I want to copy the contents of a table in .mdf to a similar table in a .sdf file.
I tried ==>
SELECT *
INTO sdfDB.ScannerData_Test
FROM mdfDB.ScannerData_MDF
also i tried ==>
INSERT INTO sdfDB.ScannerData_Test
SELECT *
FROM mdfDB.ScannerData_MDF
But no use. is this kinda copy not allowed on sdf files.
If not, how do I copy the contents to this compact Database (.sdf)
Thanks,
Vishruth
Hello,
I have 2 databases and they are
#1 an mdf file and
#2 an sdf file.
How to I transfer the contents of a table in .mdf to a similar table in .sdf database.
I tried both mentioned methods in the first article, but it doesn’t really work for me.
Is it because of the compact edition (.sdf) database what am using.
Please suggest.
Thanks,
Vishruth
Use Simplest Way:
SELECT * into NewTable FROM Old_Table
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
It is Running
One question related to your post …I have question table and answer table with quesid as foriegn key in answer table. there is another similar table structure and I want to insert from one table to another table i am using following query for it…
–DECLARE @QuestionID AS BIGINT
–INSERT INTO QBQuestions (AreaID,UserID,QuestionType,QuestionTextActive)
–SELECT AreaID,UserID,QuestionType,QuestionText,Active
–FROM ContributedQuestions
–WHERE CQuestionID in (18,19,20)
–SET @QuestionID = SCOPE_IDENTITY()
–INSERT INTO QBAnswers ( Answer,QuestionID,IsCorrect)
–SELECT Answer,@QuestionID,IsCorrect FROM ContributedAnswers
–WHERE CQuestionID in (18,19,20)
Now to insert mulitple row at one time I used query like this
DECLARE @lclMySQL as varchar(MAX)
SET @lclMySQL = ‘INSERT INTO QBQuestions (AreaID,UserID,QuestionType,QuestionText,Active)’
SET @lclMySQL = @lclMySQL +’SELECT AreaID,UserID,QuestionType,QuestionText,Active FROM ContributedQuestions’
SET @lclMySQL = @lclMySQL + ‘SET IsActive =0 WHERE CQuestionID IN (‘+@QuestionID+’)
‘ where I am using @question id as string of quesiton id but in this case how can I insert question id as foriegn key to Answer table??? Is there some way in which i can use IN keyword in where condition and also insert forign key value in to answer table?????
hai sir, i want the coding for update the results from the another table
General approach
update t1
set t1.col=t2.col
from table1 as t1 inner join table2 as t2
on t1.keycol=t2.keycol
how to retrive data through using join condition.
in table A i am having std no, std name, address.
in table B I am having host id, std no, age, division
now i want std name who are the people more than 30 age
can u please help me with query
select t1.* from tableA as t1 inner join tableB as t2 on t1.stdno=t2.stdno
Hi friends. I’ve a dought on sqlserver2000. As i’m going to do my major project, i wenna know how to add images into sqlserver 2000. and how to retrive. Please send me simple codes for that.
For instants.
{
con SqlConnection;
con= new SqlConnection(server=”myserver”; uid=”sa”; pwd=”sa”;database=”mydatabase”,con);
}
plz tell me..
Better approach is to store only the physical path of the image in the table and store actual image in the Server’s directory
1. Friends I need to write a procedure using cursor by for loop.
Below are the scenario:
1.. Data will be pulled from table1
2. Use for loop for cursor into table1(Table1 records needs to go to table2 ,table3),
3. Will also need to go into table2
4. Will also need to go into table3
Kindly help.
Why do you need a cursor?
This is as simple as
insert into table2(columns)
select columns from table1
where some_condition
insert into table3(columns)
select columns from table1
where some_condition
I tried this method, but it did not work for me.
USE KudlerAccounting
GO
INSERT INTO Accounts2004(Expenditures)
SELECT Expenditures
FROM ExpendTotals2004
WHERE Account > 0
Error: Cannot insert the value NULL into column ‘Account’, table ‘KudlerAccounting.dbo.Accounts2004′; column does not allow nulls. INSERT fails.
The table Accounts2004 has one more non-null column. You need to add data to that column too
Hello ,
What is way to Avoid Log in case ‘ insert into’ operation .
Hello, am using this query in sybase
insert into table2 SELECT * FROM table1
The two tables have the same table structure
Getting below error
An explicit value for identity field in table2 can only be specified in a insert statement when a field list is used
can anyone help to proceed?
thanks
You need to list out the column names for table2
insert into table2(col1,..colN) SELECT col1,…,ColN FROM table1
Thanks Madhivanan,
Used the query in this format
insert into table2(col1,..colN) SELECT col1,…,ColN FROM table1
There is one another problem now
Error:
Explicit value specified for the identity field in table2 when ‘SET_IDENTITY_INSERT’ is OFF
On checking table details sp_help table2 i did not get any information on this
Is this a trigger avoiding insert into the table or do i need to ask the DBA to set the value for this to ON
You need to omit the identity column from the column list
I think i need to do
1)set identity_insert requests_archive on
2) insert data
3) set identity_insert requests_archive off
i guess its correct?
If you want indentity column values, you can do that. Otherwise just exclude the identity column in the column list
Hi,
I have to write one procedure to insert records from table on one server DB(DEV server) to table with same name and structure on another server DB(TEST server).
for eg. there is a table named employee on DEV server and TEST server both. and i have made some enteries in employee table on DEV server and now using procedure just want to move some particular records to employee table on TEST server DB.
Please help me out
thanks,
Ritu
to insert data from one table to another table in Oracle.
INSERT INTO TABLE1 (SELECT * FROM TABLE2)
You dont need a brace
Hi.
Pinel I have a problem my problem is that I have two servers one is local server and another is Linked server.I send a table from local to linked server with same columns,Local server(parent) has a primary key with Identity column now I created triggers for Insert and Update and reflect data on linked server.Now I want that I will also created trigger for Insert and Update but in opposite direction.
that is
A->B and
B->A
my problem is that when I fired trigger then there is message of Primary key violation how can i fixed this problem.plz send me info of bidirection trigger or any plan on my EMAILID
and my work is on sql server 2000.
hi.
I want to move a specific data from one table to another
I have the same problem to get new data from database and insert it into another database (same server) I just want the most effective way to transfare the new inserted data and make check upon it before add to my database.
please advice
To Mona,
/*Inserting data from one database to another database.*/
use Database1
select*into Database2.Dbo.Table2 from Table1
select*from Database2.Dbo.Table2
– Here is :
Database1:- is a database in which a table named ‘Table1′.
Dbo:- default schema for all the databases.
Table1:- a table in Database1
Database2:- a new database
Table2 :- a new table in Database2
– This query will insert the data from Database1 ‘s table called ‘Table1′ to another database ‘Database2′ ‘s table i.e. in Table2.
Hope it will helpfull.
Thank you
I’m really greatful to this great blog
Dear sir
I have one full/incri .bak file form nrega offline sql server 2005 server.
how it is combile/merge in my nrega offline sql server 2005 same database without any data lose.
The problem are that.
In my block 54 village. block user work on 38 village data enty and i work on 16 village date entry.
how i combine my backup .bak file in block user data. without any lose.
good morning sir
this query is very good
i want to ask a one question in this query
(1) two tables employee or salary. totoal row in employee 300
no column or no row in salary
i used that select * into salary from employee
(2) i have added 100 rows in employee
i want a that only 300 to 400 rows come in salary
not to 1 to 300 rows
i used that many query but give me some erro
plz sir help me about this query
If you have identity column/unique column in the table you can make use of that
sorry sir i don’t know happy about your answer
Do you have an identity or unique column in the table?
good morning sir
i have no use identity or unique column in table but i use primary key on (sal_id)
i don’t about use of identity or unique
thankyou sir
Is primary key an incremental number for each insert?
Which version of SQL Server are you using?
I have get new data from other database and want insert it into my database
(same server) what is most effective way to transfare the new data in my database.
without my data lose. please advice
Do you want transfer only one table or many tables?
Hello sir,
I want Transfer all tabel.
Actually a have a full backup of other end server.
I am Want Joint that data in my Data.
good morning sir
i have table employee in cms database
column name
employee-code
11-234-45645
11-567-13678
11-269-09456
11-256-08654
11-450-24654
total column 400
output only 3 to 8 no come means 234-45
567-13
269-09
plz sir send me answer this question
thankyou sir
What is the logic used behind the result?
hello sir
sir i want a result in 3 to 8 no come .if value are string
used select substring(column_name,3,8) value wii be come
3 no between 8 no come in this query
This is not clear. Can you post some sample data with expected result?
Hi! Newbie here, but this blog is very helpful. Have to dbs on remote server, and decided to test procedure before going live:
1. Copy tableA to db2 from db1
Used following successfully:
SELECT * INTO trsql_TEST.dbo.Products FROM trsql.dbo.Products
2. Copy duplicate table with new name to db2 from db1
SELECT * INTO trsql_TEST.dbo.Products_2 FROM trsql.dbo.Products
So now I have two tables (Products & Products_2) with identical structure in the same db (trsql_TEST).
3. Now I want to copy a field to Products from Products_2… I tried UNSUCCESSFULLY the following:
INSERT INTO Products (stock) SELECT stock FROM Products_2
The result is that the stock field (column) is copied successfully, but ALL OTHER DATA IS NOW NULL !!!
Help? Oh – Im using Studio Express and MS SQL 2005 dbs
TIA – eric
OOPS – Just realized that 3. above (INSERT INTO Products (stock) SELECT stock FROM Products_2) is actually appending data –
It doubles the # or rows, which are all NULL except the “stock” column, which it is picking up from the “Products_2″ table. The original rows are still there, unaltered.
Hi
i m asking one question that i have a grid view with bubble event like check box .and i want to retrieve all the records on the another page when i checck box and if i select more than one check box then all the name of we want display in the dropdown list another page Please solve my problem imidiatly.
I m waiting!!!!!
Thanks
You need to send required values as parameters to the stored procedure. Show us the current code you are using
i want u help me about increase automatic 00000001
Keep an identity column of INT datatype and do the formation at front end application
Hi All
I would like to write a trigger on insert or update
so it will write the recored from the table in the MS SQL database to the table in Oracle
can some one help me in that
the trigger must be written inthe MS SQl database and it should write the record to the Oracle table
Regards
This is very bad design. Why do you want to do this via a trigger?
ok how do i do it if
so it will write the recored from the table in the MS SQL database to another the table in a differnt database in MSSQL on the same server
Regards
I tried this
CREATE TRIGGER test ON TA_PUNCHES FOR INSERT,UPDATE
as
begin
Insert into TestPunch.TA_PUNCHES select * from inserted ins
end
I have 6 table
table 1 (1id, 1Name)
table 2(2id, 2Name)
table 3 (3id,3Name)
table4(4id,4Name)
table 5 (1Name, 2Name, 3Name, 4Name)
now i want table6 to be
table6(1id,2id,3id,4id) how can i do that
can anyone please help me with this
Join all the tables and take relevent ids from each table
Hi,
i want to know that
how to insert data into 3 tables by one insert query.
pl help me
This is not possible. You need to write three statements
write 3 statements separated with ‘ ; ‘ at the end of each statement
and Execute in EXEC().
Ex.
Exec(‘Insert into tbl1 values(); ‘Insert into tbl2 values(); ‘Insert into tbl3 values();’)
Thanks.
This is a real thing I need but I couldn’t find it anywhere.
Hi all,
i have a problem with this statement:
INSERT INTO buchung_betr(BUDAT)
SELECT BUDAT from Belegdaten
sql server says: invalid column name although the column BUDAT exists in my database.
Please help!
Make sure that column exists in both the tables
Merci beaucoup
hi pinal and madhivaan sir
this time i am doing job in jla_logistics pvt ltd
i am database handler in this company
this company used tally 7.2 i want to say that
tally means maintain full information of company a/c
may i use sql server 2005 in this company
if yes some me give example
company profile -transportation
may use sql server 2005 in this company
hello pinal and madhivaan sir,
First, sorry about my poor english.
If it’s possible, I want to know that:
How can I use the statement insert into table1 (field1,field2,field3,…) select variable1, table2.field2, table2.field3,….. when my table1 has a PK and autoincrement field? (Attention: field1 isn’t the PK and autoincrement field).
When I execute the query returns:
Subquery returned more than 1 value. This is not permitted when the subquery returns follows =, !=, <, , >= or when the subquery as used as an expression.
I’m using SQL Server 2005 Express.
Thanks,
i’m trying to make a procedure based on below instruction but it seems its not working can you help me.
create a parameter that will accept a target database that user wish to saved his output table.
Finally, call the stored procedure in other database aside from master.
Hope you can help me thanks
Hi Sir,
Here My Table like this:
First i’m creating table for Empdetails and defining datatypes and values like this:
Insert into Empdetails values(Eid int primary key,Ename varchar(20) ,Designation varchar(20) ,Doj int,Salary int,Deptno int)
I’m creating Another Table like EmpInformation
From the above table changing like
“Here Salary datatype is integer but i want change “double” &
Doj datatype convert to DateTime format”
Insert into Empdetails values(Eid int primary key,Ename varchar(20) ,Designation varchar(20) ,Doj DateTime,Salary Double,Deptno int)
And copy the all records from Empdetails table to new EmpInformation table
It’s Possible r not
plz Reply me
You need to use CAST function
cast(Doj as datetime)
how to create command for datetime double
What did you mean by datetime double?
how to initialize the date and double
hi pinal sir.
plz help me
how to do this.
i have two database
database table
mm emp
mn emp1
database mm hava a table emp and have a data this
id name address
1 mohit delhi
2 mona haridwar
and second database emp is blank
id name address
so i want to data transfer one tabel to another database.how to do this.i am using this
select * into Northwind.dbo.employee from pubs.dbo.employee
but it’s not working
any guy’s plz help me. or send me code
The “Method 2 : SELECT INTO ” is only a school case because none constraints are copied (no primary key, no default, no foreign key).
In the true life, every table has constraints.
I think the best to said is : “never use this method instead script the initial database then use Insert Into ie the Method 1″.
hi, I have to unit five tables into a new table in sql server management studio 2008
Insert into oldtest select * from
(select * from newtest union all select * from testdaytop union all select * from testdaytrp union all select * from testdayfrp union all select * from testdayfip)
can anybody help
It should be
Insert into oldtest
select * from newtest union all select * from testdaytop union all select * from testdaytrp union all select * from testdayfrp union all select * from testdayfip
Hi madhivanan
You suggested query is not working all the tables have same columns and actually I have to extract data from MYSQL server which I extracted in above five tables and now I want to join them in a single table in SQL server management studio 2008
Insert into oldtest
select * from newtest union all select * from testdaytop union all select * from testdaytrp union all select * from testdayfrp union all select * from testdayfip
What did you mean by “it is not working”? Did you get any error
Helpful post, thank you Dave
This was a great help Pinal – Thanks!
Hi,
I need to generate a database script with table values also.. that is table creation + insert values like pubs database..
pls guide
There is an option if you use version 2008
I need to do this in sql server 2005.
Is there s any way..
I need to do this in sql server 2005.
Is there s any way..
Which one are you referring the queries posted in the post or the option to export data along with script?
I have two instance of sql server 2005 in the same box.
When i create recode in table of first instance at same time using trigger i want to transfer that record in another instance of sql 2005.
Waiting for your reply.
This may cause performance issues. One option is to make use of a linked server
How can I insert data in the sql server database table from the access database table???
Use import/export wizard or openrowset
Hi Dave,
Is is possible to insert data from a table to another, where these two tables are located in different databases, and databases are located in different servers?
basically inserting from one server to another.
source table is in a remote server and destination table is in the local server.
also I connect to these 2 servers using different connections.
1 Use linked server
2 Use four part name in the query
insert into table(col)list)
select col_list from server.db.owner.table
Hi I’ve one table called HISTORY. This table is heavily used in production environment. Now i Want to create another table of same structure. Also i want to move data from this table to another created table. also created table must be updated by HISTORY.
plz help!!!!!!!!
Its a kind of Archiving of table.
1 Create a new table
2 Insert into new_table(col)list)
select col_list from history
creating a table is not a problem………. moving data dynamically is required.
I ll really appreciate if u can help out that…. let me give u one more info that..this table(history) contains more than 5 lakh rows and constantly increasing.
Scenario-
History more than 5 lakh record(rows) and increasing.
now i want to move data from history table to New_histroy table….and truncating the data from history @ weekly basis.
so whatever data come to history table will be moved to new table @ sunday 00:00 am. deleting the data which is moved from history table.
History table gets data 24/7.
You can create a stored procedure that copies data from history to other table. Schedule it as a job so that it will run periodically
that is what i want…….c whenever record reaches more than 400000 move the data into another table.
if (select count(*) from table) >400000
–your insert statement
@varun.
5 Lakhs Records is very normal. But size of the table for holding those 5 Lakhs Record matters…
Anyways,
You already finalized that you want to go with Archiving ???
Table Partition would be a good option.
Archiving Solution,
Even after you acrhive History Table, you need to update Indexes. Do you re-organize or rebuild Indexes on that table. Because lot of inserts and delete creates lot of fragmentation.
Depending on what locks you are using when inserting data into table, you will be able to delete data from history table after archiving.
Look at this sample Archiving procedure below
http://vyaskn.tripod.com/sql_archive_data.htm
I am not clear if I answered your questions, in case not, please ask your question clearly.
~ IM.
@Imran Mohammed
this is good…thanks……… scenario is like this………..
I’ve table called HISTORY…..it has more than 500000 rows….. what i want is whenever rows reaches more than 500000 …..move 250000 rows to ARCHIVED_HISTORY. Also delete the data from HISTORY table which has moved.
Moving of rows shud be LIFO.
Environment is Production Database.
Please send the procedure.
PLZ help.
@ Imran Mohammad
I have two Tables Subject and subjectDim(SubID,SubName, Level,Total Marks) i have to insert data into SubjectDim where SubID is an auto number and subName will be selected from Subject and Level is ‘HSSC1′ and TotalMarks are 100.
I need Urgent help plz reply me soon how to write this Query.
Thanks in Advance
i have two tables: ITEM_HISTORY and ITEM
columns in ITEM_HISTORY : ITEM_ID, SOURCE_ID, TIME_STAMP. etc.
ITEM columns: ITEM_ID, MODEL_ID, LAST_UPDATE, LOCATION
LOCATION from ITEM is same as SOURCE_ID from ITEM_HISTORY
I need to get the ITEM_IDs for a SOURCE_ID for a time interval of like every few days..and i need to get the records inserted in a new table called PH_ABC with ITEM_ID, PART_ID, TIME_STAMP,YEAR_CODE, JULIAN, LAST_UPDATE
here, i cant loop the items and i am getting only the last item from the whole set of items. and i dont want to get the duplicates.
ALTER PROCEDURE [dbo].[insertitems](@PSourceID varchar(100),@PStartDate datetime,@PItemID varchar(100) out)
AS
BEGIN
DECLARE @VItemID varchar(100),@VTimeStamp datetime,@VJulian varchar(5),@VJulian1 varchar(100), @VYearPart int, @VDayPart int, @VZeroDate datetime, @VDate datetime,
@VStartDate datetime, @VEndDate datetime
SET @VStartDate = @PStartDate
SET @VEndDate = DATEADD(dd,5,@VStartDate)
SELECT @VTimeStamp = TIME_STAMP
FROM ITEM_HISTORY
WHERE SOURCE_ID = @PSourceID
IF((CONVERT(varchar(10),@VTimeStamp,120)>= @VStartDate) AND (CONVERT(varchar(10),@VTimeStamp,120) <= @VEndDate))
BEGIN
SELECT @VItemID = ITEM_ID
FROM ITEM_HISTORY
WHERE SOURCE_ID = @PSourceID AND
TIME_STAMP = @VTimeStamp
PRINT 'ITEM' + ':' + @VItemID
INSERT INTO PH_ABC(ITEM_ID,TIME_STAMP)
VALUES(@VItemID, @VTimeStamp)
SET @VJulian = SUBSTRING(@VItemID,3,5)
SET @VJulian1 = '20' + @VJulian
SET @VYearPart = CAST(LEFT(@VJulian1,4) AS int)
SET @VDayPart = CAST(RIGHT(@VJulian1,3) AS int)
SET @VZeroDate = DATEADD(yy,-1 * DATEDIFF(yy,0,GETDATE()),GETDATE())
SET @VDate = DATEADD(dd,@VDayPart – 1, DATEADD(yy,@VYearPart-YEAR(@VZeroDate),0))
PRINT 'Date' + ':' + CAST(@Vdate AS Varchar(100))
DECLARE @VModelID varchar(100), @VLastUpdate datetime
SELECT @VModelID = MODEL_ID,
@VLastUpdate = LAST_UPDATE
FROM ITEM
WHERE ITEM_ID = @VItemID AND
LOCATION = @PSourceID
PRINT @VModelID
UPDATE PH_ABC
SET PART_ID = @VModelID,
JULIAN = @VJulian,
YEAR_CODE = @VYearPart,
LAST_UPDATE = @VLastUpdate
WHERE ITEM_ID = @VItemID AND
TIME_STAMP = @VTimeStamp
END
ELSE
PRINT 'No items with that location'
END
please help me with this….i have been trying for the last 5 days…
Hi,
Please I have a table with and identity column. When I try to insert 5,000 records from another table with the Insert Into and select statement. It takes forever to finish.
But when i create a new table without any identity column, this same script doesn’t take a minute to finish.
Please can you help me
Can you post the code that has this problem?
hi,
i have two datagridview which is dgv1 and dgv2.
how can i insert row from dgv1 into dgv2. both of them is data bound.
let say, i have two table in database, beverage1 and beverage2. beverage1 consist of attributes of ID, Name and Price.it also has the data in it.
in beverage2 consist the same attributes but no data in it.
In dgv1 will display the beverage1. hence, how should i click entire one row and insert into dgv2.
hi,
i have two datagridview which is dgv1 and dgv2.
how can i insert row from dgv1 into dgv2. both of them is data bound.
let say, i have two table in database, beverage1 and beverage2. beverage1 consist of attributes of ID, Name and Price.it also has the data in it.
in beverage2 consist the same attributes but no data in it.
In dgv1 will display the beverage1. hence, how should i click entire one row and insert into dgv2.
Hi Anne,
You can create a collection of datarows from the first grid [from these datarows you can fetch row specific attribute values], then you may add a ‘row click’ funtionality to your grid on which you may add each row instance to the second grid also you can update database from there.
Hope this gives you an idea to solve your problem.
If you have any further queries please tell me.
Regards,
Suvradeep Banerjee
thanks,
its really useful for me.
i have two tables: table1 and table2
copy one table to another existing table
INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1
Hello i have a request for the gurus
i want to insert rows into a table where data are coming from 2 other tables
Table1
DB_id, DB_name, owner
Table2
server_id, server_name, sql_instance
Table3
server_id, DB_Name, DB_id
I want to insert data into table3 from data Table1 and table2
how can i achieve this ??
Thanks for all your help
Hi
How to use index while executing select query
I have an xml file. I read the nodes of XML file into cte. My problem is that i need to load this data into an exisiting table and there is a stored procedure which i have to use to get the id value from its output parameter.
Do i have to loop the cte to insert the record or is there any direct way to insert the cte data into table ?
[...] Here is quick example from my earlier article SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO T…. [...]
Thank you!!
Hi
I have one table it is having only one colun length is 120 char. the data is like this
column name ID
d1012052011B10005280915A10004380920A10004360920
in the above data :-
d10 is code
12052011 is date
B100 is type
0528 is empcode
0915 is time
A100 is type
0438 is empcode
0920 is time
a100 is type
0436 is empcode
0920 time
like the above id fields is having length of 120 chara.
so, I want tha data is:-
empcode,date,type,time
0528 12/05/2011 B100 0915
0438 12/05/2011 A100 0920
0436 12/05/2011 A100 0920
so, pl help with sql query
Ashok
Hi All,
I have a scenario where i need to create a table using only the key columns [some specific columns] of another table. Is it possible?
Any help would be appreciated.
Thnaks in advance
You can create a view using the specific columns
i have a table with no keys. the table size is about 40 million. i get a record which first needs to be checked for duplicate values in the table (agaisnt 4 of 10 columns) and if there are no duplicates, then i need to insert the value into the table.
it take a large time if i use where clause or if exists etc. what alternate ways are there to check duplicates before inserting
thanks
@Balu.
Don;t event think of a trigger. That will kill your performance.
1. I would suggest you use a unique Constraint on the table. That would be the quickest way.
2. You can use Check Constraint with a combination of Scalar function.
This Scalar Function should accept column value as input and check for uniqueness in that table, and return either 1 or 0. This returned value will then be checked by Check Constraint defined on that column before inserting record in the table.
Do you have Clustered Index on this table by any chance ?
~ IM.
Thank you. The table is indexed . Point is, composite keys are not feasible due to the some limitations and any other combinations i am trying , the result is crossing 8 to 10 seconds which is not allowed in the system. I was thinking about hash tables?
If TestTable is exist already with 0-row or record if you want to insert all records from Contact table then you have to check both the table structure (Number of column and associated datatypes) & finally you can use the below query to insert rows from source table to destination table without mentioning the field or column name.
INSERT INTO TestTable
SELECT *
FROM Person.Contact
WHERE EmailPromotion = 2
If any of the table set the replication mode then you have to mention the column name because at the you should not use keyword “*”, below query will help you.
INSERT INTO TestTable (F1,F2)
SELECT F1,F2
FROM Person.Contact
WHERE EmailPromotion = 2
Thanks,
S.Venkatesh
hello pinal and madhivaan sir,
my name is happy
sir i knew sql server,ms-Access i have exp in only for sql in windows xp
but i have changed my company/ company use windows 7. sql server not downloading of windows 7/ i dont have use of oracle (database) plz sir help me about this topic
sir i have many querires, backup point,csv into sql server, fuction from our website
plz give me some oracle site when i learn oracle 10 g,9g, because company use oracle or DB2 universal database, sir plz help me about this topic
thankyou sir
hello sir,
i want to transfer my data from one server to another server.
is this possible. if is this possible then please give me solution how to transfer data from one server table to another server
thanks
Jonish Aggarwal
Thank a lot… this article
Hi am santhosh,
I have a scenario like this..
I have two tables personal_details and academic_details with same columns S_No attributes and details
Obviously some rows of both the tables match with each other like name,age etc
So i want to run a trigger like whenever an update takes place in academic_details it will make the corresponding updates in personal_details only for the rows which are common.
the problem is that these rows are placed in different positions.
to give an example the record with values 5 as S_No,educational qualification as attribute and +2 as details is placed at 5th position in academic_details where as the same is placed at 11th position in personal_details,still i want a trigger to update the details column in personal_details……
Can anyone help me??
I use this code,
mysql> delimiter //
mysql> create trigger acatoper after update on academic_details for each row
-> begin
-> update personal_details,academic_details set personal_details.details=academic_details.details where personal_details.attributes=acad
emic_details.attributes;
-> end;
-> //
the query is ok…runs fine
but if tryto update the table academic_details,i get the following error..
mysql>delimiter ;
mysql> update academic_details set details=’+1′ where S_No=5;
ERROR 1442 (HY000): Can’t update table ‘academic_details’ in stored function/trigger because it is already used by statement which invoked t
his stored function/trigger.
Hello Sir,
what i need is we are creating two tables in the second table some id field is there particular id can be stored in first table how can it possible
I have a two tables, lets say TableA, TableB
TableA Columns:
TLID,TaskID, AID, Detail
TableB Columns:
TaskID, Title, Subject,AID
I have added a column AID to TableA, And I want to copy data from TableB which has AID, with same TaskID on both tables,
Please give a query…
Thanks.
Update A
set AID=B.AID
from tableA as A inner join tableB as B on A.TaskId=B.TaskID
Hello, i could use some help
I have two tables, Living Room, And Main,
They both have Work_Order as a primary key,
I have built the work_Order in Main to be a Foreign key in Living Room
What i would like to have happen, is when ever a write a new row into Main. I I would like the database to also write a new row in living room, with the same Primary key. (the rest should be null).
Thanks
You can do it via an INSERT trigger on living table
Thankyou very much Sir, this is quite informative.
[...] This method is used when table is not created earlier and it needs to be created when data from one table must be inserted into a newly created table from another table. The new table is created using the same data types as those in selected columns. (Read more here) [...]
thanks to this site..it really help me a lot…
hello pinal dave , madhivanan sir
i am happy
i have one table employee from cms company
i want to show 25 rows in the table
then use select top(25) * from employee
if i want to show 15 to 40 rows in the table
then i used that but give me error
plz sir help me this query
You need to use pagination. Refer point 4
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx
thankyou madhivanan sir
you and pinal sir good author of sql server
you have solved my problem
hello sir
i don’t use of procedure and triggers
i have read many articles but nothing
first
view use virtual table means shortuct key
index use arrange the table ato z
update use update the table
but procedure and triggers what use of sql server
i knew that procedure and triggers are very most important role in sql server
plz sir help me about this question pinal sir and madhivanan sir
happy
use this query select * from tablename
How to copy tables into another database by using the IN clause?
Why do you want to use IN clause?
You can just do
Insert into table(cols)
select cols from db..table where …
hello pinal dival and madhivanan sir
i have create table employee
(
emp_id int,
emp_doj datetime
);
i used that
insert into employee values(01,’15-12-2011);
but doj output is different 3637-1
plz sir correct my doj problem
select sum(sal_salary) as total salary from employee
if i want to sum 10 to 50 cell what will be use of this query
how is the use of automatic date come in the doj column
plz sir help me about this query
thankyou sir
happy
You need to use a single quote around a date value
For summation, search for pagination in this site
Also you should express dates in YYYYMMDD format
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx
i want to insert a bulk data to another table, but other fields are same… here the column name ‘task_id’ only is same for both table
i tried like this:
INSERT INTO [dbo].[active_task]
([user_id]
,[active_flag]
,[date_entered]
,[tracking_status]
,[task_id] )
VALUES
(’1′
,’True’,
SYSDATETIME(),
’1′
,select task_id from task)
but i cant get the solution, any alternative way?
INSERT INTO [dbo].[active_task]
([user_id]
,[active_flag]
,[date_entered]
,[tracking_status]
,[task_id] )
select
’1′
,’True’,
SYSDATETIME(),
’1′
, task_id from task
Hi All,
Inserting data from one table to another table without duplication.
Everybody asking transfer data from one table to another table without duplication of rows. so here is the example which i ve implemented.
[SSDB].[DBO].[PERSON]=destination table.
[CONFIGDB].[DBO].[CFG_PERSON] =source table.
INSERT INTO [SSDB].[DBO].[PERSON]
(dbid, tenant_dbid, last_name, first_name, address_line1, address_line2,
address_line3, address_line4, address_line5, office, home, mobile, pager, fax, modem,
phones_comment, birthdate, comment_, employee_id, user_name, password, is_agent,
is_admin, state, csid, tenant_csid, place_dbid, place_csid, capacity_dbid, site_dbid,
contract_dbid)
SELECT *
FROM [CONFIGDB].[DBO].[CFG_PERSON]
WHERE NOT EXISTS(SELECT *
FROM [ssdb].[dbo].[PERSON]
WHERE ([CONFIGDB].[DBO].[CFG_PERSON].[DBID] = [ssdb].[dbo].[PERSON].[DBID]
)
)
thanks.
varun Gaur
hello pinal dival and madhivanan sir
i used that single quote but error has come(doj datetime)
create table record
(
emp_code int,
emp_doj datetime,
emp_city varchar(60),
emp_age int
);
insert into record values (001,’1582011′,’faridabad’,25);
error
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
how to come dafault datetime come in the emp_doj column
plz give me example
2 i have search that pagination but noting else
plz sir i want to sum only for 15 cell to 40 cell in sal_salary column
You should always express date in YYYYMMDD format
insert into record values (001,’20110815′,’faridabad’,25);
Also read this post to know why you should use YYYYMMDD format
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx
Does it make sense to reference a temp table with dbo.#temp_table?
Yes there is no problem with it
Is my query correct.. Its not working.. please help..
insert into [TblPlayer] (OldId,RegNo,FirstName,MiddleName,LastName,FathersName,DateOfBirth,Category,Phone,Address,
Village,CurrentClubRegistrationDate)
select pid,pid,fname,mname,lname,fathers_name,category,dob,category,contact,address,village,dor
from [players]
What did you mean by it is not working? Did you get any error message?
i m getting the following error:
The data was truncated while converting from one data type to another
hello pinal dave and madhivanan sir
my sql server set up is not working properly
i want to free full download sql server 2005 version
plz sir give me site of sql server 2005 version
sir its very urgent
plz sir help me
can easily find out the link in microsoft site. Have you tried it?
hello madhivanan sir
i have download sql servre 2005 express edition but management studio has not come,i have no idea which part download of sql server, i have tried that but sql server setup has not come, plz sir which part download of sql server setup include management studio, sorry sir i am irritating you, plz sir help me about this question
happy
You can download it via
http://www.microsoft.com/download/en/details.aspx?id=8961
INSERT INTO new_table (Foo, Bar, Fizz, Buzz)
SELECT Foo, Bar, Fizz, Buzz
FROM initial_table
Hi Dave,
insert into table2
select col1,col2,col3.. from table1 where col=’xyx’
its not working for me in some situations.
My table1 contains some null columns also,
its working when my col2 and col3 is with data and if these two columns are null its not working for me.
its showing x rows affected. but the data was not moving
Check if table2 has any constraints that prevent data from being copied
Hi experts,
I have 1 orders table
orders table
=========================================
OrderID OrderDate ProductName QtySold Amount
1 11/11/11 item1 2 70
2 11/11/11 item2 7 90
I want to copy this details on another table Order details table
like this :–
Order details table
=====================================================
OrderdetailsID OrderDate CustomerName ProductName QtySold Amount
1 11/11/11 Jane Austen item1,item2 9 160
What will b the Query for this ??
Thanks in advance
So very helpful! Thanks so much!
Hi All,
I have one requirement like from 1 database i have to copy the data from some tables to another server database(taking backup on during night on every day.But not all tables).
Here the table structure in two servers are same.But there is a primary key and foreign key relationship.So, i cannot use direct query to copy the data.Because while copying it will say duplicate records cannot insert.
I have created a SP in that i will drop tables first and select * into and adding constraints to that tables. It will work fine.But taking long time.
If you have any idea/option that will take less time, let me know.
Thanks,
Hanuman
Why dont you update existing data and add new data? The general logic is
update t1
set t1.col1=t2.col1, t1.col2=t2.col2,…
from table1 as t1 inner join table2 as t2 on t1.keycol=t2.keycol
insert into table1 (columns)
select columns from table2 as t2 where not exists
(select * from table1 where keycol=t2.keycol)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger railways_result_trigger on railways_result
after insert
as
if exists(select distinct update_date from railways_result where update_date!=(SELECT CONVERT(date, SYSUTCDATETIME (), 103)))
begin
delete from current_railways_result
delete from railways_result
end
else
begin
delete from current_railways_result
insert into current_railways_result select *,dense_rank()OVER (PARTITION BY exam_date ORDER BY stu_total_marks DESC)stu_rank
FROM railways_result
insert into railways_result_backup select * from current_railways_result
end
sir, i m trying to delete all record from current_railways_result,railways_result and insert into railways_result
when insert into particular table both table records delete but not inserted into
railways_result
plz help me sir
tanks in advance sir..
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger railways_result_trigger on railways_result
after insert
as
if exists(select distinct update_date from railways_result where update_date!=(SELECT CONVERT(date, SYSUTCDATETIME (), 103)))
begin
delete from current_railways_result
delete from railways_result
end
else
begin
delete from current_railways_result
insert into current_railways_result select *,dense_rank()OVER (PARTITION BY exam_date ORDER BY stu_total_marks DESC)stu_rank
FROM railways_result
insert into railways_result_backup select * from current_railways_result
end
i m trying to delete data from current_railways_result, railways_result before
new insert
data deleted from both table,but when i going to insert new data today first time data not insert in table,second time inserted in table plz help me sir
using trigger
Works exactly as described. I’m brushing up on my T-SQL skills, this was exactly what I wanted to replace the Access “Append” queries.
Thanks alot.
Hello sir I am new to SSIS and I have very simple task to do that I can easily do in SQl 2008, but for my work I have to make in SSIS.
I have one source and destination table both in SQl 2008 and I want to Insert data from source table to destination. I tried a lot of work around in SSIS but not able to do that , Can you please tell me how to do that in SSIS. I will really arrpiciate you. here is my script that is working well in SQL.
INSERT INTO [EnrollmentDM].[dbo].[table 1]
([AUTHRZ_INFO_QUAL]
,[SEGMNT_ID]
,[AUTHRZ_INFO]
,[SEC_INFO_QUAL]
,[SEC_INFO]
,[INTERCHNG_ID_QUAL1]
,[INTERCHNG_SENDR_ID]
,[INTERCHNG_RECVR_ID]
,[INTERCHNG_ID_QUAL2]
,[INTERCHNG_DT]
,[INTERCHNG_CNTL_STD_ID]
,[INTERCHNG_CNTL_VER_NUM]
,[INTERCHNG_VER_NUM]
,[ACKMNT_RQST]
,[USAGE_IND]
,[COMPNT_ELEMNT_SEPRTR]
,[MBR_ID]
,[CASE_NUM]
,[SEQ_NUM]
,[FILE_ID]
,[BGN_SEGMNT_ID]
,[REC_INSRT_DT])
(select SUBSTRING(SEGMNT_DTLS,5,2) as AUTHRZ_INFO_QUAL,SEGMNT_ID,
SUBSTRING(SEGMNT_DTLS,8,10) as AUTHRZ_INFO ,SUBSTRING(SEGMNT_DTLS,19,2) as SEC_INFO_QUAL,
SUBSTRING(SEGMNT_DTLS,22,10)as SEC_INFO,SUBSTRING(SEGMNT_DTLS,33,2) as INTERCHNG_ID_QUAL1 ,
SUBSTRING(SEGMNT_DTLS,36,4) as INTERCHNG_SENDR_ID ,SUBSTRING(SEGMNT_DTLS,55,15) INTERCHNG_RECVR_ID ,
SUBSTRING(SEGMNT_DTLS,52,2) as INTERCHNG_ID_QUAL2 ,SUBSTRING(SEGMNT_DTLS,71,6) as INTERCHNG_DT,
SUBSTRING(SEGMNT_DTLS,83,1) as NTERCHNG_CNTL_STD_ID ,SUBSTRING(SEGMNT_DTLS,85,5) as INTERCHNG_CNTL_VER_NUM,
SUBSTRING(SEGMNT_DTLS,91,9) as INTERCHNG_VER_NUM,SUBSTRING(SEGMNT_DTLS,101,1) as ACKMNT_RQST ,
SUBSTRING(SEGMNT_DTLS,103,1) as USAGE_IND, SUBSTRING(SEGMNT_DTLS,105,1) as COMPNT_ELEMNT_SEPRTR,
MBR_ID,CASE_NUM,SEQ_NUM, FILE_ID,BGN_SEGMNT_ID,REC_INSRT_DT
from table 2 where SEGMNT_ID =’ISA’)
thank for sharing
I need to create a new table called “costumer” that included some of columns from the “user table”, and also “project table”. I built my suppliers table with spesific column names and I need to fill its column by using data of the other tables. Finally I am trying to finish; when user create a new account and project, the costumer table automatically fill with some of other two tables varieties with different column names.
INFO: I have three different user types such as “suppliers”, “costumers”, “managers”. I am holding their information(include user types) in one table called users.
Please help me if you have free time.
sir
I want to insert column form table into another table
for this I wrote like this
SELECT column_name(s)
INTO new_table_name
FROM old_tablename
but i am getting error like ‘undeclared variable : name of new table ‘
plZ suggest me how to do this
Than U
t the full code you have used. There is no variable in this code
Dear Mr.Pinal,
We need your help, we have 3 table : Sales Last Year, Sales Actual, and Sales Target.
and then we need to get result for new table, the new table is “Sales YTD”,
Sales YTD is cumulative from Sales Actual.
could you give me the query for Sales YTD(Year-To-Date).
[email removed]
Thanks,
Regards.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
namespace Assignment.Classes
{
public class DBCon
{
public static string con = “Data Source=HP-PC;Initial Catalog=AssignmentDB;Integrated Security=True”;
//s public static string con = “Data Source=192.168.0.3;Initial Catalog=AELANK;User ID=sa;Password=sssa;Connection Timeout = 2000000″;
//public static string con = “Data Source=HASHENDRA-PC\\SQRSERVER;Initial Catalog=AELANK;Integrated Security=True”;
public static SqlConnection conn = new SqlConnection(con);
public static SqlCommand comm = new SqlCommand();
public static SqlDataAdapter adapt = new SqlDataAdapter();
public static DataSet dtset = new DataSet();
public static string user;
public static string department;
public static string custommer;
public static string selectedDate;
public static string FromDate;
public static string ToDate;
public static string email;
public static string ddlStateSales;
public static string ddlStateCus;
public static string SalseManager;
public DBCon()
{
}
public static DataTable SQLDTT(string strSQL)
{
conn.Close();
if (conn.State.ToString() == “Closed”) { conn.Open(); }
SqlCommand comm = new SqlCommand();
SqlDataAdapter adapt = new SqlDataAdapter();
comm.CommandTimeout = 0;
comm.CommandText = strSQL;
comm.Connection = conn;
adapt.SelectCommand = comm;
DataTable DataTable = new DataTable();
adapt.Fill(DataTable);
//comm.CommandText = strSQL;
//comm.Connection = conn;
//adapt.SelectCommand = comm;
//dtset = new DataSet();
//adapt.Fill(dtset);
//conn.Close();
return DataTable;
}
public static DataSet SQLDT(string strSQL)
{
conn.Close();
if (conn.State.ToString() == “Closed”) { conn.Open(); }
SqlCommand comm = new SqlCommand();
SqlDataAdapter adapt = new SqlDataAdapter();
comm.CommandTimeout = 0;
comm.CommandText = strSQL;
comm.Connection = conn;
adapt.SelectCommand = comm;
DataSet dtset = new DataSet();
adapt.Fill(dtset);
//comm.CommandText = strSQL;
//comm.Connection = conn;
//adapt.SelectCommand = comm;
//dtset = new DataSet();
//adapt.Fill(dtset);
//conn.Close();
return dtset;
}
public static int GetNumOfRec(string strSQL)
{
/// Use for get No of Records in SELECT command
//try
//{
int intResult = -1;
conn.Close();
if (conn.State.ToString() == “Closed”) { conn.Open(); }
strSQL = DBCon.SQLFormat(strSQL);
SqlCommand sqlCmd = new SqlCommand(strSQL, conn);
intResult = (int)sqlCmd.ExecuteScalar();
sqlCmd.Dispose();
return intResult;
//}
//catch (Exception objError)
//{
// //MessageBox.Show(“System Error – ” + objError.Message.ToString(), “Application Error”, MessageBoxButtons.OK, MessageBoxIcon.Error);
// return -1;
//}
}
public static string SQLFormat(string strSQL)
{
strSQL = strSQL.Replace(“\r”, ” “);
strSQL = strSQL.Replace(“\n”, ” “);
strSQL = strSQL.Replace(“\t”, ” “);
strSQL = strSQL.Replace(” “, ” “);
return strSQL;
}
public static string SQLER(string strSQL)
{
conn.Close();
if (conn.State.ToString() == “Closed”) { conn.Open(); }
strSQL = SQLFormat(strSQL);
SqlCommand sqlCmd = new SqlCommand(strSQL, conn);
string strResult = sqlCmd.ExecuteScalar().ToString();
sqlCmd.Dispose();
return strResult;
}
public static SqlDataReader DataReader(string strSQL)
{
conn.Close();
if (conn.State.ToString() == “Closed”) { conn.Open(); }
strSQL = SQLFormat(strSQL);
SqlCommand sqlCmd = new SqlCommand(strSQL, conn);
SqlDataReader dataRed = null;
sqlCmd.CommandTimeout = 0;
dataRed = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
sqlCmd.Dispose();
return dataRed;
}
public static SqlDataReader oSQLER(string pSQL)
{
conn.Close();
if (conn.State.ToString() == “Closed”) { conn.Open(); }
SqlDataReader objReader;
SqlCommand objCommand;
// conn.Close();
// if (conn.State.ToString() == “Closed”) { conn.Open(); }
objCommand = new SqlCommand(pSQL, conn);
objReader = objCommand.ExecuteReader();
objReader.Read();
return objReader;
}
public static int SQLCX(string strSQL)
{
if (conn.State.ToString() == “Closed”) { conn.Open(); }
strSQL = SQLFormat(strSQL);
SqlCommand sqlCmd = new SqlCommand(strSQL, conn);
sqlCmd.CommandTimeout = 0;
int intResult = sqlCmd.ExecuteNonQuery();
sqlCmd.Dispose();
return intResult;
}
public static int SQLCXRemoveRow(string strSQL, string item)
{
if (conn.State.ToString() == “Closed”) { conn.Open(); }
strSQL = SQLFormat(strSQL);
SqlCommand sqlCmd = new SqlCommand(strSQL, conn);
sqlCmd.CommandTimeout = 0;
sqlCmd.Parameters.AddWithValue(“@value”, item);
int intResult = sqlCmd.ExecuteNonQuery();
sqlCmd.Dispose();
return intResult;
}
}
}
Good One !
ur code is work well…
but image datatype it gives an errer..
it’s copies the rows … but images are stored in Table not displayed in image control……
plz reply ….
Hey Dave,
It would be great if you could add an update to your post to explain that this will not work if there is a TRIGGER on that table which does not take multirow operations into consideration. The user can of course consider disabling/re-enabling the trigger as part of their script.
Regards,
Colly
hi sir how can i send mail by triger
HI Pinal,
we are using sql server 2005,actually frequently we have to restore production database to acceptance environment ,sometimes database in acceptance have more tables than prod database. in that case i just use import\export wizard or select into command to transfer extra tables in acceptance to a temporary created database in same server, but while doing that size of the table in temp database is less than actual one.So ,while looking table structure there is no index’s,primary keys,foreign keys etc. could ypu please post your suggestion
Hello sir…! I have little problem about database in server 2005..!
I have a Table1 and there are 3 columns like, EmpID,EName,Designation..
Designation column is complete NULL.. here i want to add values from different table which is Table 2. there are 2 columns like EmpID and Designation…!
so what will be queary to add Designation values in Table1 from Table2 Designation column on the basis of EmpID!
you can try this code
update table1 set Designation=(select Designation from table2 where Empid=’value1′) where EmpId=’YourEmpid’
Hi,
I am having three tables named like tbl_report1,tbl_report2,tbl_customer .my issue is i want to combine these tables data and structure into one table like tbl_company.is it possible?.if possible in the sense can you please give me the suggesstion.please do the needful.
tbl_report1 columns:
invoiceno(foreign key fk,particulars,quantity,perprice,amount,orderno,compname,invoicedate,ondate,invoiceid.
tbl_report2 columns:
compname,compaddress,invoiceno(primarykey),orderno,invoicedate,ondate,amountstatus,netvat,netamount,tamount
from the above three tables i want to copy their data and structure into tbl_company.how can i proced.
how to insert data into another database? How about that? can you please help me.. Thanks
Use three part name
insert into dbname.ownername.table_name(cols)
select cols from table
Hi All
I’m inserting Data From One Table to Another in different databases in the same server, after this is done i have to delete the copied data from the source table. The records are selected based on few constraints in the WHERE clause This table has several dependent tables that also have to be moved.
I have chosen the INSERT INTO…SELECT method because:
1) I can monitor errors during the process
2) I can use transactions and error handlers for testing purposes and to ensure all or nothing will be copied.
I’m having the following dilemas because of this choice:
1) Performance is not the best.
2) Where to run the delete operation? A) Right after every single INSERT statement B) After all thousands INSERT statements.
3) If i need to COMMIT/ROLLBACK INSERTs on target table and DELETEs on source tables, I wonder if SQL Server is able to handle both of them within the same TRANSACTION statement, or i have to first finish one transaction and then change database context (USE statement i.e.) to commit the operations in the other database.
In the end, seems that for this kind of problem, the approach i have chosen has a high cost since the numbers of inserts are about 228000 (I’m copying one row along with dependent rows per each iteration of a loop), this makes me wonder if i shouldn’t just copy records from one table at the time, using INSERT INTO… SELECT …WHERE ID IN (SELECT IDS FROM SOURCE TABLE)
I forgot to add that my database is a SQL Server 2005
How to copy data from one column to another column in same table but it should update automatic using time of our system.
hello sir my name is happy.
Q1. how to create some table backup in sql server 2005
becuase i knew that if full backup use database right click tasks and go for backup, translog backup
Q2. what is use of stroed procedure in sql server. what is main differnce between view and stored proceduure and fuctions
plz sir help me about question
i want to move xml to sqldatabase they move propely bt every time they fetch the old data also..i want to insert only updated value everytime nt the old 1′s
/My Code is here
string xmlfile = Server.MapPath(“~/ResumeFolder/Export1.xml”);
try
{
using (SqlConnection con = new SqlConnection())
{
con.Open();
using (DataSet dsTemp = new DataSet())
{
dsTemp.ReadXml(xmlfile);
using (DataTable dt =dsTemp.Tables[0])
{
using (SqlBulkCopy sb = new SqlBulkCopy(con))
{
sb.BatchSize = 50;
sb.DestinationTableName = “TBQUANTITY”;
sb.ColumnMappings.Add(“Id”, “Id”);
sb.ColumnMappings.Add(“ProductName”, “ProductName”);
sb.ColumnMappings.Add(“CategoryName”, “CategoryName”);
sb.ColumnMappings.Add(“Country”, “Country”);
sb.ColumnMappings.Add(“OrderDate”, “OrderDate”);
sb.ColumnMappings.Add(“OrderTime”, “OrderTime”);
sb.ColumnMappings.Add(“UnitPrice”, “UnitPrice”);
sb.ColumnMappings.Add(“Quantity”, “Quantity”);
sb.WriteToServer(dt);
}
}
}
}
}
catch
{
}
Hi there very nice website!! Man .. Excellent .. Wonderful .. I will bookmark your blog and take the feeds also?I am glad to find so many helpful information here in the post, we want develop more techniques on this regard, thank you for sharing. . . . . .
select * into TABLE2 from TABLE1
Hi ,Can anybody know how to compute minimum value in a cube.As while cube uploads it data, it loads zeros at the places of Null …I want to calculate minimum value excluding these zeros. Thanks
Thanks..It worked..
i want to insert a vedio mp4 format into database(sqlserver) any one help me for this
Hi everyone.
I have two tables ,
table1 and table2
suppose table 1 contains emp id proj id and table 2 contains emp id,project id and updated date.the updated date of 2nd table will only change when there is change in project_id on 1st table.
Kindly help
Can anyone tell me how can i insert value from one table to another with other values like….
@status int
insert into Table2(FirstName,LastName,Status)
select FirstName,LastName from Table1,@status
I don’t know exact syntax kindly help me…
Can Anyone Help Me…?
In my problem data of one column in a table has to be moved to another column of same table depending on the time. Means for every day at 12:00Am data of column1 has to be moved to column2.
Hi, i am using sql for databse and C# in visual studio……i want to update table1 which contains 4 columns(eg Name, CNIC, Attendance, Date) i want to select Name and CNIC culimns’ value from table2 and and want to enter values of attendance and date my self….please tell me how should i do this?
i have try this..
INSERT INTO Dailyreport (Name,CNIC,Attendance,Date) VALUES (SELECT Name,CNIC FROM Registration,(‘Present’, ‘” + date + “‘)) WHERE CNIC=” + n.ToString();
but this does not work……….
please help me………
Incorrect syntax near ‘,’.
this is the error (exception) of the above post …….
hello sir,
i have a doubt regarding inserting records into two tables and my doubt is that …….
let us consider the following two tables,
the first table is “pradeep” …
SQL>select * from pradeep;
SNO NAME PHNO
— ——————– ———-
1 pradeep 1
2 prasad 2
3 lavanya 3
4 tulasi 4
5 ravi 5
my second table is … “prasad” …
SQL> desc prasad
Name Null? Type
—————————————– ——– —————————-
SNO NUMBER
NAME VARCHAR2(20)
PHNO NUMBER(10)
ADDRESS VARCHAR2(20)
my condition and doubt is that ………….. in the above mentioned tables, the two columns i.e., SNO,NAME (COMMON COLUMNS IN BOTH TABLES),HERE I WOULD LIKE TO COPY THOSE TWO PARTICULAR COLUMN DATA FROM PRADEEP TO PRASAD WHERE I WOULD LIKE TO INSERT THE DATA INTO THE ANOTHER COLUMN I.E.,ADDRESS (COLUMN IN SECOND TABLE(PRASAD)) ………………
THE BOTH OPERATIONS I.E., COPYING RECORDS FROM ONE TO ANOTHER TABLE AND INSERTING VALUES TO ONE TABLE HAS TO DONE SIMULTANEOUSLY WITH SINGLE QUERY …………..
IS IT POSSIBLE IN PRACTICAL?
AM WAITING HERE FOR YOUR REPLY ………………………..
THANKS IN ADVANCE
REGARDS,
[email removed]
Hi, i need to insert data from table in one database to another database with some where conditions. Both tables have same columns (tablename,column name ,datatype etc).
Regards,
chella
Have another server as linked server and write
insert into source_table(col_list)
select col_list from servername.dbname.ownername.source_table where
Thanks! Just what I needed.
I have searched and tried a lot can anyone help me.
my case is very complicated. I need to insert all the column values from a select * statement in one column of another table in multiple rows.
hope you all understood what i want.
ex.
table1(a,b,c)
temptable(col1)
i need each columnar data of table1 as 3 record in temptable.
any solution?
hi,
is there any possible way to create table from select * from another table without create any table structure……
select * into new_table from old_table where 1=0
hello every one i have a doubt…..can u plz clarify it………………….
my doubt is that………………..
How to create a new table by copying particular records from another base table depending on the row number of the base table……….for example consider the following tables.
table 1: pradeep (base table)
empname empno1 loc
———————————————-
azar 9156 hyd
sachin 9458 chennai
shewag 1245 banglore
dravid 2589 vizag
pradeep 2122 guntur
table 2: prasad (which has to be created by copying the records from the base table)
the table2 prasad should be as mentioned below:
empname empno1 loc
———————————————-
azar 9156 hyd
sachin 9458 chennai
shewag 1245 banglore
Motto: i have to create a new table (i.e., Prasad) by copying only the first
3 records from the base table (i.e., pradeep), it is very easy if we know
the relevant data .
Conditions:
(1). I have to copy the relevant data from base table to new table
depending on the row number but not the data which is stored in
the base table,i assume that i don’t know the data until & unless it
is copied into the new table. So this operation has to be performed
depending on the row number.
(2) It is very easy to copy structure & records separately into new table
,but here i want to perform both operations (i.e.,copying structure
& copying records) had to be done at a time.
(3) Here there is a possibility to copy multiple records from base table
to new table.
++++++ hope every one understood my requirements …………………………………….
thanks in advance ……………… am waiting for your replies…………………
regards,
[email removed]
(if any one has any doubt in my requirements or any doubts in my explanation ..plz free to ask me)
perfect..!!! good job…
Hi Dav,
I have 3 tables with the columns below
1.ecatalog_itsc_extension
—————————–
Catalog_ID
2.eCatalog_Item_Type_Approval_Type
——————————
Approval_Type_ID
Catalog_Item_Type_ID
ISO_alpha_2_country_code
Sequence
Retired_Date
Create_Date
Last_Update_Date
Last_Update_By_Text
3. eCatalog_Catalog_Item_Approval_Type
————————————–
Approval_Type_ID
Catalog_ID
Sequence
Retired_Date
Create_Date
Last_Update_Date
Last_Update_By_Text
So i want to update the 3rd table with 1st table coulmn “Catalog_ID” as 3rd “Catalog_ID” and from 2nd table all records to 3rd table based on the “ISO_alpha_2_country_code”. (County code as “US” or “MX”, or “CA”)
Could you please help me with the query
I want inset data from table1 to table2 both tables are same but data types are differnt how do i do this?
i want to create a trigger when an article is inserted into database then give permission/access to a testuser
I add two columns in my existing table(table A) with ten columns but now I am wondering how can I insert values into these new columns with insert and select statement as I need to extract values for these two new columns from another table (table B). There are two similar columns between table A and table B.
Thank you very much for this helpful and clear article :)!
This other article you posted (and the comments in it) was helpful also and kind of goes hand in hand with this current one:
http://blog.sqlauthority.com/2007/03/28/sql-server-fix-error-8101-an-explicit-value-for-the-identity-column-in-table-can-only-be-specified-when-a-column-list-is-used-and-identity_insert-is-on/
I experienced the “explicit value for the entity column” issue and went to that article :).
how to insert data one table to another table
insert into target_table(cols)
select cols from source_table
create a table TB1 with 10 rows of data
create another table TB2 with no rows inserted init..
and to copy the contents of TB1 inti TB2 then use this syntax
” insert into TB2 select * from TB1 “
Don;t forget to specify the column names
insert into TB2(col1,col2,..) select col1,col2,.. from TB1
If I want take backup 3-4 tables using each day ?? can scheduled plan ???
Hi All,
I’ve two tables (table A, table B ) table B is empty with column names,
I need to copy the table a column’s data to table B by one- one column.
I used the command for first time copy the one column data with – Insert into table B (column) =select column from table A
it was successfully copied.
except copied column, all columns were filled with “Null values”
again need to copy the second column from table A to table B
above command is not working as like first time, how I copied
Please help.
Thanks,
Chary K
Insert into B(col1,col2,…)
select col1,col2,… from A
Thank Chary k and Mdhivan
But I do not create any table in server is that possible alternate way ???
Hi Dev,
if i am trying to retrieve one row from on database (DB1 hosted in a server A) and inserting to another database (DB2 hosted in server B). how i can i achieve this through my vb application?
Please help me
thanks
manzoor
i have 2 table let us say customer and queuemail. i have to retrieve all email id of that column from customer table and inserted into queuemail with additional columns as mentioned below. when a user in customer table registered in before today’s date on i.e,[CreatedOnUtc] field then i want to send message to all the users.
Please help me out on this. i dont want gmail pattern answers.
this is for my application itself not globally.
Queuemail table
SELECT TOP 1000 [Id]
,[Priority]
,[From]
,[FromName]
,[To]
,[ToName]
,[CC]
,[Bcc]
,[Subject]
,[Body]
,[CreatedOnUtc]
,[SentTries]
,[SentOnUtc]
,[EmailAccountId]
FROM [QueuedEmail]
customer table
SELECT TOP 1000 [Id]
,[CustomerGuid]
,[Username]
,[Email]
,[Password]
,[PasswordFormatId]
,[PasswordSalt]
,[AdminComment]
,[LanguageId]
,[CurrencyId]
,[AffiliateId]
,[Active]
,[Deleted]
,[IsSystemAccount]
,[SystemName]
,[LastIpAddress]
,[CreatedOnUtc]
,[LastLoginDateUtc]
FROM [Customer]
Grate I stop using cursors for transfer data between 2 tables, and the execution plan is now visible (before the max was 255 records).
Hi Pinal,
Nice article ,, I am struggling with almost same issue .. I need to insert data from different tables into one table and each column in my Table is a grouping of some data (As subqueries doesn’t allow order by clause inside queries ) . can you please suggest some good way to approach this issue.
Looking forward to your reply
Thanks in advance
Roma Mehta
Hi,
I need to compute statistics from two tables and update into the third table.
example:
INSERT INTO table1(date,col1_summ)
SELECT date,sum(table2.col1)+sum(table3.col2) FROM TABLE2,TABLE3
WHERE date=”somedate”;
What is the correct query to do something like this?
Please advise.
how to split the data in raw to colum
Hi, I want to update a database on a remote server based on an insert from a different server
Is this possible, if so what is the code for doing this? Please help…
Hi,
Is there any way for below issue.
I want to insert a row from one table to anther without mentioning columns name having similar schema but for some fields i want to use some local variable.
I want to avoide to mention columns name because table have more than 900 columns
900 columns? Thats too many. Make use of the result of the following query
select column_name+’,’ from information_schema.columns
where table_name=’your table’
I am trying to insert data in a table table1 of data base db1 from table 2 of database db2 . But I find error token line ,tablename=table2
“INSERT INTO db1.table1
(Client, date)
SELECT Cin, Nom
FROM db2.table2″
hi I want to append data from the table enquirymaster2 to enquirymaster1,
I have field companyname, mobile.
I used this but it is deleting pervious data
from enquirymaster1
INSERT INTO test.dbo.EnquiryMaster1
(Company, MobileIT)
SELECT Company, MobileIT
FROM test.dbo.EnquiryMaster2
It will not delete previous data. After inserting to table, do SELECT * FROM table and see if there are data
Sir,
I have 2 servers( ex. Server1 & server2 with same database name). I want to replicate the data from perticular table (ex. table 1 with server1) from server1 to server2(ex. Table1 with server2). Please tell me how to do the same.
Thanks.
Anil
I have a table that contains many columns for quarterly periods beginning in 4Q2009 going out to 4Q2015 – I want to create a user form with a dropdown where a user can select a quarter and the query will return all columns up to the user selected value. Example: the user selects 3Q2012 and the query returns all columns from 4Q2009 – 3Q2012.
Hi Pinal,
I have one question in my mind.that select * into will insert data row wise or it will insert data once completely.
Thank u
Thank you, saved a lot of time – especially i needed the part about inserting when created earlier, thx
Thank you! Your example was just what I needed. Your site is a major resource for me!
HELLO I AM SANJAY AND YOUR SITE IS BES
[...] I have written the similar article a few years ago - SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO T…. The article has been very popular and I have received many interesting and constructive comments. [...]
Thx Pinal
HI I HAVE TW0 EXCELS.. I WANT TO COPY FROM ONE EXCEL TO OTHER WHICH IS EMPTY
cAN YOU HELP ME
You can very well use SSIS file system task for this
Sir i need a solution, there are 3 places where my application is installed(offline).
and in one place internet does not work regularly.
i want to connect database in the evening to sync or update that all day data…///
is there a solution for this?
Read about Replication
Hi All,
Thank you for good article..
In my project I have 3 table in sqlite database (Category,FoodType,SavedD), the first one contain Category_id and CategoryName which show in TableView and the second table (type_id,Category_id,Type,Quantity,NumberCalories) and this data show it in the second TableView, and display the cell data in label in ViewController, how i can save this data in a new table.
How i can save the data display in the labels which depend in Type_id?
I use the below query, but it doesn’t save any data and when i change Type_id to 3 it saving the third information when the Category_id=1.
****const char *statement= “INSERT INTO SavedD (type_meal,Quantity,NumCal) SELECT Type,Quantity,NumberCalories FROM FoodType WHERE Type_id=?”;
can any one help me insert data from one table to another depending on Type_id…
Thanks
I have a query i am trying to import 7 different excel files into 7 different tables, upon there import i need the data from all 7 to be imported into 1 single master table then dropped, my issue is the column headings in the 7 files are all different, yet all headings exist in the master table
In short how do i loop through each table copying the data into the master where the column match, then drop the tables once all data is present in the master table.
good day sir,
it is possible to move the records from 1 table to another in a single query?
Example:
SELECT ID, NAME, BDATE
INTO NEWTABLE
FROM OLDTABLE
//then delete the records in the old table
Perry,
Suggest you try an insert statement instead:
Insert into NewTable (ID, ‘Name’, ‘BDate’)
Select (ID, ‘Name’, ‘BDate’) from OldTable
If the columns are identical and in the same order in both tables, you can skip the column names in your insert statement:
Insert into NewTable
Select * from OldTable
Use the Select Into clause when your new table is not yet created; use the Insert Into clause when you are transfering data between existing tables.
Forgot to remove paratheses on Select statement. Should look like:
Insert into NewTable (ID, Name, BDate)
Select ID, Name, BDate from OldTable
SELECT * INTO TATADO FROM CUSTOMER_TABLE WHERE CST_SLNO!=0
I am trying to insert values into a table in a different database, but I get an error “Incorrect syntax”, I notice that if I take the “database.dbo.” in front of the table it works fine
insert into table (column1, column5, column8) values (‘x’, ’4′, ‘m’)
but if I add the “database.dbo.” prefix to the table it gives me the error.
insert into database.dbo.table (column1, column5, column8) values (‘x’, ’4′, ‘m’)
I need to put the database.dbo. in front of it so it finds the table since it is located in a different database from where my store procedure is located.
Do you know if that is because of rights to the table?
Do you know how to avoid that error?
select * into Table_2 from Table_1 where 1=2
This will create a structure of a Table_1 into Table_2 without moving the data.
What do you do with timestamp erorrs? Using * instead of implicit fields to save time.
“Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.”
Muchas gracias amigo.
Yahh!! Really Its working.Thanks
Hi,
i am creating procedure like this:
ALTER PROCEDURE [dbo]. A
@currentDate DATETIME
AS
BEGIN
Delete from TT_21_STOCK_01_STATUS
INSERT INTO TT_21_STOCK_01_STATUS(BinCode, SKUCode, BatchCode, ConfirmedQuantity, UnconfirmedPutaway, UnconfirmedPicked, BlockedQty, STK_Defragmentation_IN, STK_Replenishment_IN,
STK_Aggregation_IN, STK_Optimize_IN, STK_Total_IN, STK_Defragmentation_OUT, STK_Replenishment_OUT, STK_Aggregation_OUT, STK_Optimize_OUT,
STK_Total_OUT)
SELECT BinCode, SKUCode, BatchCode, ConfirmedQuantity, UnconfirmedPutaway, UnconfirmedPicked, BlockedQty, STK_Defragmentation_IN, STK_Replenishment_IN,
STK_Aggregation_IN, STK_Optimize_IN, STK_Total_IN, STK_Defragmentation_OUT, STK_Replenishment_OUT, STK_Aggregation_OUT, STK_Optimize_OUT,
STK_Total_OUT FROM TT_21_STOCK_02_BACKUP where StockBackupDateTime= @currentDate
END
procedure execute correctly but can’t insert record…….
plz Help me
I am trying to copy data from one server’s view to another’s table. I am sure they have the same structure. For security reasons, the servers CAN NOT be linked.
I tried to use MS_Access. I cannot import the data to there and upload to newtable, but cannot because MS_Access limits to 255 columns.
I tried linking the two tables and doing a DoCmd.RunSQL (“INSERT newtable SELECT * FROM oldtable”) and it failed with error 3162 (inserting a null into a non-variant).
Any suggestions? THANKS!
Sir; I’m new to SQL Server so as I type SQL statements that I’ve used on other platforms, I find syntax errors. I google my problem and keep ending up on this web site. Thanks so much from Memphis for all the help you’ve given me!
i have table1(idno, firstname, secondname, lastname, age, address)
insert three records to this table?
create other table2 consist of having columns(age, firstname, ido)
insert into the data of table1 into table2?
quick answer
sir, i want to copy only two column from table1 to table2 and rest all column in table2 i want to filled it dynamically through query.plese help me how can i fetch only two column data from table 1 and insert into table2 along with other data in table 2.i have 5 column in table 1 and 5 column in table 2 in which two columns in both table are same and i want to copy these two columns in to other table.
HI Dave,
I’m trying to do this.
INSERT INTO table_1 (column_1, column_2, PlusOneMoreColumn) VALUES
(SELECT * FROM table_2, “ValueForTheExtraColumn”)
table_1 has column_1, column_2, PlusOneMoreColumn
table_2 has column_1, column_2
Is this possible?