Coding Article #: 80 - Published On: July 06, 2017 @ 18:14:49 PM - Last Updated on: July 07, 2017

Supported Files
No Files for this Article.

Using this simple statement, you will be able to grab records from a table and insert them records into another table, within a different database.

In this example, we have 2 tables with the same column names, ColOne and ColTwo.
We are taking the values from TableOne and inserting (Copying) the records over to TableTwo.

insert into TableTwo (ColOne, ColTwo) select ColOne, ColTwo from [DatabaseOne].[dbo].[TableOne] 

For local, meaning if you are running this code on your own network, and both Databases reside on the same server, as the script is running, you can use it like this.

ASP classic.

<%
        Set sql= Server.CreateObject("ADODB.Command")
        sql.ActiveConnection=Conn
        sql.Prepared = true
        sql.commandtext="insert into TableTwo (ColOne, ColTwo) select ColOne, ColTwo from [DatabaseOne].[dbo].[TableOne] "
        sql.execute
%>

ASP.NET (VB)

<%
        strSQL = "insert into TableTwo (ColOne, ColTwo) select ColOne, ColTwo from [DatabaseOne].[dbo].[TableOne] "
        objCmd = New OleDbCommand(strSQL, Conn)
        objCmd.ExecuteNonQuery()
%>

Stored Procedures

In SQL Server Management Studio (SSMS), run the following code, to create a Stored Procedure, under the Database Folder.
We are naming this Stored Procedure, getLocal.

create procedure getLocal
as
insert into TableTwo (ColOne, ColTwo) select ColOne, ColTwo from [DatabaseOne].[dbo].[TableOne] 

Once you run the query, you should get a [Command(s) completed Successfully]
To make sure the Store Procedure saved successfully, do the following.



You will now be able to run the stored procedure

In SSMS, you can now run the following, in a Query Window under the TableTwo Database.

getLocal

ASP classic Stored Procedure.

<%
        Set sql= Server.CreateObject("ADODB.Command")
        sql.ActiveConnection=Conn
        sql.Prepared = true
        sql.commandtext="getLocal"
        sql.execute
%>

ASP.NET (VB) Stored Procedure

<%
        strSQL = "getLocal"
        objCmd = New OleDbCommand(strSQL, Conn)
        objCmd.ExecuteNonQuery()
%>