1

Some explanation on why this would be required and why information_schema.columns / syscolumns / spcolumns don't work in my situation:

I have this long procedure that maintains a dynamic table. The procedure saves the table into a temp table, creates an updated one using SELECT INTO and finally INSERTs some old rows from the temp table into the newly created table. This final part is done by looking up the temp table columns from a system table and generating dynamic INSERT INTO sql using a cursor looping through each column.

Now, these tables are often very large and the SELECT INTO takes a long time, which causes locks on some system tables. During this time tables such as information_schema.columns & syscolumns can't be read from. So when multiple tables are handled at the same time, they all stall until the locks are freed, because the procedure currently tries to read columns from the information_schema.columns table

Link where the systables lock issue is explored: https://www.sqlshack.com/sql-server-lock-issues-when-using-a-ddl-including-select-into-clause-in-long-running-transactions/

Any idea on how to approach this?

MeideC
  • 19
  • 2
  • What about using `nolock` hint on those tables? `from information_schema.columns with (nolock) ...` –  Oct 31 '18 at 13:16
  • At the point at which the `SELECT INTO` is executing, you obviously know all of the column names. Why is this information not *retained* through to the later part with the `INSERT`s? – Damien_The_Unbeliever Oct 31 '18 at 13:17
  • 1
    maybe `sp_describe_first_result_set` would help? I'm not entirely sure what/when the problem is occuring. – Thom A Oct 31 '18 at 13:53
  • You can use [`set rowcount`](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-rowcount-transact-sql?view=sql-server-2017) with `select`/`into` to create the output table with a single row. Delete that row and run the query as an `insert`/`into` using the now empty destination table. (From doc: "SET ROWCOUNT stops processing after the specified number of rows.") – HABO Oct 31 '18 at 14:15
  • https://stackoverflow.com/a/52925825/1080354 – gotqn Nov 01 '18 at 06:07
  • what about "exec sp_help tablename" ?? – Eray Balkanli Nov 02 '18 at 13:11

1 Answers1

1

Create the tables dynamically instead of select into.

For example: Instead of

SELECT name INTO #t FROM sys.objects;

Do

CREATE TABLE #t (name SYSNAME);
INSERT INTO #t SELECT name FROM sys.objects;
Sean Pearce
  • 1,150
  • 5
  • 10
  • 4
    This might be a good comment, but as an answer it seems somewhat lacking content. I've decided not to flag it as VLQ, but others might. – Zohar Peled Oct 31 '18 at 13:25