Following three questions are many times asked on this blog.
How to insert data from one table to another table efficiently?
How to insert data from one table using where condition to another table?
How can I stop using cursor to move data from one table to another table?
There are two different ways to implement inserting data from one table to another table. I strongly suggest to use either of the methods over the cursor. Performance of following two methods is far superior over the cursor. I prefer to use Method 1 always as I works in all the cases.
Method 1 : INSERT INTO SELECT
This method is used when the table is already created in the database earlier and the data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are not required to list them. I always list them for readability and scalability purpose.
USE AdventureWorks GO ----Create testable CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100)) ----INSERT INTO TestTable using SELECT INSERT INTO TestTable (FirstName, LastName) SELECT FirstName, LastName FROM Person.Contact WHERE EmailPromotion = 2 ----Verify that Data in TestTable SELECT FirstName, LastName FROM TestTable ----Clean Up Database DROP TABLE TestTable GO
Method 2 : SELECT INTO
This method is used when the table is not created earlier and needs to be created when data from one table is to be inserted into the newly created table from another table. The new table is created with the same data types as selected columns.
USE AdventureWorks GO ----Create a new table and insert into table using SELECT INSERT SELECT FirstName, LastName INTO TestTable FROM Person.Contact WHERE EmailPromotion = 2 ----Verify that Data in TestTable SELECT FirstName, LastName FROM TestTable ----Clean Up Database DROP TABLE TestTable GO
Both of the above method works with database temporary tables (global, local). If you want to insert multiple rows using only one insert statement refer article SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL.
Reference: Pinal Dave (https://blog.sqlauthority.com)
677 Comments. Leave new
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
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?