how to create temp table using jdbc java

Question

I need to create a temp table in order to store some ids which i will process under a later query. I am receiving error

com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

When i execute my query for the creation of #temp table inside my sql. I don't need any resultset from this execution just need to create a temporary table with records. Please guide.

Code for my main query:


    String queryTempTable = "SELECT TOP (2) A.Id INTO #temp\n" +
                            "FROM  SALESDM.dbo.FactSales A\n" +
                            "INNER JOIN  SALESDM2.dbo.FactSales B\n" +
                            "ON A.Id = B.Id\n" +
                            "AND (\n" +
                            " A.sysDateModified = B.sysDateModified\n" +
                            " OR A.Id = B.Id\n" +
                            " OR A.ModifiedDatetime = B.ModifiedDatetime\n" +
                            " )";


                            System.out.println(queryTempTable);

                            if (conn == null) {
                                System.out.println("Unable to create Connection");
                            } else {
                                Statement stmtTempTable = conn.createStatement();
                                stmtTempTable.executeQuery(queryTempTable);
                            }

Check if this answers your question . https://stackoverflow.com/questions/4728495/temporary-tables-using-jdbc-with-null-resultset/17826631 — Harmandeep Singh Kalsi, May 25 '20 at 03:38

score 2 · Answer 1 · answered May 25 '20 at 03:37

2

You should use executeQuery only when you are retrieving data and want a ResultSet.

If you are modifying data, then you should use execute:

stmtTempTable.execute(queryTempTable);

answered May 25 '20 at 03:37

Brice Frisco

409
1
4
15

Now its not showing any error but its not creating any #temp table inside my db. – Amad Bin Mumtaz May 25 '20 at 03:48
Try removing the `INTO` from the SELECT statement, and see if your query is actually returning any results to insert into the temp table. – Brice Frisco May 25 '20 at 03:54
you mean to remove "INTO #temp" ? yes its working and giving me the results which i need to dump inside the temp table. – Amad Bin Mumtaz May 25 '20 at 04:16
Try adding `AS x` to the end of the query. `SELECT... INTO... FROM... AS x` and see if that works. – Brice Frisco May 25 '20 at 04:21
I think adding "AS ..." will act as an Allias but its already the main query with one SELECT clause. I tried this but it's making my query wrong. Further, I worked with creating VIEW instead of a #temp table and your suggested modification "execute()" in my code worked there. Thanks. – Amad Bin Mumtaz May 26 '20 at 22:58

score 1 · Answer 2 · answered May 25 '20 at 03:48

1

If possible create a view using the given query? This will act as a temporary table. And call the view later based on your requirement.

answered May 25 '20 at 03:48

Rajkumar

134
2

Thanks @RajKumar, I tried creating a view as a workaround for #temp table and it worked. – Amad Bin Mumtaz May 26 '20 at 22:50

how to create temp table using jdbc java

2 Answers2