locked
Bulk Insert table in one database to another in a different database RRS feed

  • 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

    Friday, November 15, 2019 9:11 AM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 25, 2019 10:56 AM

All replies

  • 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...

    Friday, November 15, 2019 5:21 PM
  • 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

    Friday, November 15, 2019 7:52 PM
  • 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

    Monday, November 18, 2019 11:54 AM
  • 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.

    Monday, November 18, 2019 12:15 PM
  • User1717218719 posted

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

    Monday, November 18, 2019 12:17 PM
  • 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

    Tuesday, November 19, 2019 9:18 AM
  • 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  ?).

    Tuesday, November 19, 2019 9:55 AM
  • 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

    Friday, November 22, 2019 9:54 AM
  • 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
    

    Friday, November 22, 2019 11:04 AM
  • 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

    Monday, November 25, 2019 9:29 AM
  • 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 :)

    Monday, November 25, 2019 9:35 AM
  • 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

    Monday, November 25, 2019 9:40 AM
  • User1717218719 posted

    Hi Sam

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

    Monday, November 25, 2019 9:49 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 25, 2019 10:56 AM