Bulk Insert table in one database to another in a different database

Question

User1717218719 posted

Hi ,

I am looking to first create a temp global table (##Test) in database x, then bulk insert data from tbl123 in database y to the table ##Test.

I am not sure if this is possible or if it is how I would go about it. Any information help and or code would be greatly appreciated.

Thanks

Answer 1

User753101303 posted

Hi,

And which problem do you have or do you expect? Try perhaps:

--DROP TABLE ##demo
IF OBJECT_ID('tempdb..##demo') IS NULL CREATE TABLE ##demo(a varchar(10),b varchar(10))
DELETE FROM ##demo
BULK INSERT ##demo FROM 'c:\data.txt'
SELECT * FROM ##demo

If you need further help please be more explicit about which problem you are running into...

Answer 2

User765422875 posted

If you need to rapidly bulk insert data into SQL Server you can use SqlBulkCopy. Not sure if it supports writing to a global temp table.

https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?view=netframework-4.8

Answer 3

User1717218719 posted

Hi patriceSc,

thak

I am looking to bulk copy from sql table not from a file.

thank you for your reply. I am looking to bulk insert from sql table in database a into database b ie. two differet connection strings. I am using vb.net also

Answer 4

User753101303 posted

And the target server can reach the source server? If yes you could use for example https://www.sqlserver-dba.com/2014/05/connect-sql-servers-without-using-linked-servers-openrowset-and-opendatasource.html or https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine?view=sql-server-ver15 and use an INSERT INTO MyTable(a,b,c) SELECT a,bc FROM Source, source being taken from a server, db, schema, table name....

This way you'll move data directly without going through your web server.

Answer 5

User1717218719 posted

Thank you patrice I tried to create a linked server but I do not have the permission to do this ?

Answer 6

User288213138 posted

Hi E.RU,

I am looking to bulk insert from sql table in database a into database b ie. two differet connection strings

You can use ado.net SqlBulkCopy to insert data from one database table to another database table.

The code:

string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            string constr1 = ConfigurationManager.ConnectionStrings["constr1"].ConnectionString;
            using (SqlConnection con1 = new SqlConnection(constr1))
            {
                SqlCommand cmd = new SqlCommand("select * from TableA", con1);
                con1.Open();
                using(SqlDataReader rdr = cmd.ExecuteReader())
                {
                    using (SqlConnection con = new SqlConnection(constr))
                    {
                        using(SqlBulkCopy bc=new SqlBulkCopy(con))
                        {
                            bc.DestinationTableName = "TableB";
                            con.Open();
                            bc.WriteToServer(rdr);
                        }
                    }
                }             
            }

Best regards,

Sam

Answer 7

User753101303 posted

Yes, you need to be allowed to do that. See with your DBA. 

Meanwhile and if you don't have a high volume you could go through your web server as shown above. Depending on what is done it could be also likely optimized (you are trying to sync a table with a source table  ?).

Answer 8

User1717218719 posted

Hi Patrice,

thanks for your sample code I am using VB though, I tried to use an online converter for your c# code but recieved an error if possible could you provide this example in vb?

Thanks so much

Answer 9

User1717218719 posted

I have tried the below code and recieve an error "cannot access destination table. I am using a global temp table. Any idea as to why this is happening ?

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim strOleCon As String = "Data Source=x;Initial Catalog=x;Trusted_Connection=True;"
        Dim strOleCon1 As String = "Data Source=x;Initial Catalog=x;Trusted_Connection=True;"

        Dim conConn As SqlConnection = Nothing
        Dim comComm As SqlCommand = Nothing
       
        '--Create Table 
        conConn = New SqlConnection(strOleCon)

        conConn.Open()

        comComm = New SqlCommand
        With comComm

            .Connection = conConn
            .CommandType = CommandType.Text
            .CommandText = "CREATE TABLE ##Test(Nbr varchar(13))"
            .ExecuteNonQuery()

        End With
        conConn.Close()


        Using con1 As SqlConnection = New SqlConnection(strOleCon1)
            Dim cmd As SqlCommand = New SqlCommand("select Nbr from tblTest", con1)
            con1.Open()

            Using rdr As SqlDataReader = cmd.ExecuteReader

                Using con As SqlConnection = New SqlConnection(strOleCon)

                    Using bc As SqlBulkCopy = New SqlBulkCopy(con)
                        bc.DestinationTableName = "##Test"
			bc.BatchSize = 50000
                        con.Open()
                        bc.WriteToServer(rdr)
                    End Using
                End Using
            End Using
        End Using
    End Sub

Answer 10

User288213138 posted

Hi E.RU,

E.RU

I have tried the below code and recieve an error "cannot access destination table. I am using a global temp table. Any idea as to why this is happening ?

The temporary tables are not stored in the current database, there are stored in the system database tempdb, so you can't get the table by ado.net.

Best regards,

Sam

Answer 11

User1717218719 posted

hi SamWu

thanks for your reply unfortunatly I am not familiar with ado.net, could you provide some sample code as to how I would do this?

thanks :)

Answer 12

User288213138 posted

Hi E.RU,

Sorry, I wrote it wrongly, It should be that ado.net is not able to get temporary tables in the database.

My suggestion is that you can create a table in the database.

Best regards,

Sam

Answer 13

User1717218719 posted

Hi Sam

I am using ASP.NET VB can I access my global temp table using this ?

Answer 14

User288213138 posted

Hi E.RU,

You can create your global temp table in Stored Procedure, then call the Stored Procedure by ado.net.

This is my test code:

Stored Procedure:

CREATE PROCEDURE GetTA
AS
BEGIN
CREATE TABLE ##TableA
(
emp_id INT IDENTITY PRIMARY KEY,
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(30) NOT NULL,
hire_date DATETIME NOT NULL,
job_title VARCHAR(50) NOT NULL
)

INSERT INTO ##TableA
VALUES ('Smith', 'James', '3/1/2016', 'Staff Accountant'),
('Williams', 'Roberta', '2/7/2004', 'Sr. Software Engineer'),
('Weinberg', 'Jeff', '1/2/2007', 'Human Resource Manger')

select * from ##TableA
END
GO









<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
            <asp:GridView ID="GridView1" runat="server"></asp:GridView>

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

    Using con As SqlConnection = New SqlConnection(constring)

        Using cmd As SqlCommand = New SqlCommand("GetTA", con)
            cmd.CommandType = CommandType.StoredProcedure
            con.Open()
            Dim idr As IDataReader = cmd.ExecuteReader()
            GridView1.DataSource = idr
            GridView1.DataBind()
        End Using
    End Using
End Sub

The result:

Best regards,

Sam