Replace first FROM in sql query

Question

I need to write a query engine on a web app, what needs to be accomplish is that a user can enter any SELECT statement into a textbox and then the results should be created into a new table.

This is my function I have created but it only support SQL Server 2012 and I want similar to this function but only it should support SQL Server 2005 and above:

CREATE FUNCTION [dbo].[CustomQueryTableCreation]
(
    @TableName varchar(max),
    @sql NVARCHAR(MAX)
)
RETURNS 
  @TableBuilder TABLE
  (
     DS varchar(max)
  )
  BEGIN
  INSERT INTO @TableBuilder
  SELECT 'CREATE TABLE dbo.' + @TableName+'(';

  INSERT INTO @TableBuilder
  SELECT 
   CASE column_ordinal 
     WHEN 1 THEN '' ELSE ',' END 
     + name + ' ' + system_type_name + CASE is_nullable 
     WHEN 0 THEN ' not null' ELSE '' END
  FROM 
  sys.dm_exec_describe_first_result_set
  (
    @sql, NULL, 0
  ) AS f
  ORDER BY
  column_ordinal;

  INSERT INTO @TableBuilder
  SELECT ');';

  RETURN 
END

What I want to do now is that I want to search through my query and replace the FIRST FROM with INTO NewTable FROM.

The query can contain multiple joins.

Should I control this with SQL or C#?

Answer 1

I had a similar problem with the 2005 Environment. If you save the Select query to a table, and use the following built in procedure to execute the query:

EXECUTE sp_executesql @Query

Here is the MS docs: http://msdn.microsoft.com/en-us/library/ms188001%28v=sql.90%29.aspx

Edit

Keeping this in mind, can take the SQL dumps and Create OpenRowset Queries to take the SQL and dump them into a TempTable, and from the Temp Table to a permanent table if required.

I created the following SP's to assist with getting the info to a permanent table. First the procedure to execute the specific SQL Statement

CREATE PROCEDURE [dbo].[spExecuteRowset]
(
    @Query NVARCHAR(MAX)
)
AS
BEGIN

--Execute SQL Statement
EXECUTE sp_executesql @Query

END

Then the OpenRowset SP:

CREATE PROCEDURE [dbo].[spCustomquery]
(
    @ProQuery NVARCHAR(MAX),
    @Tablename NVARCHAR(MAX) 
)
AS 
BEGIN

--Insert the info into a Specidied Table
DECLARE @Query NVARCHAR(max)
SET @Query = 'SELECT * INTO #MyTempTable FROM OPENROWSET(''SQLNCLI'', ''Server=localhost;Trusted_Connection=yes;'','' EXEC [YOUR DATABASE].dbo.spExecuteRowset' +''''+@ProQuery+''''') SELECT * INTO '+ @Tablename +' FROM #MyTempTable'

--FOR DEBUG ONLY!!!!
PRINT @Query

EXEC [YourDatabase].dbo.spExecuteRowset @Query

END

This takes it from the #tempTable to A Physical Table.

Here are some docs on OpenRowset.

Answer 2

You have no guarantee that the first from in a query will accept an into, because you can have a subselect in the select statement. In addition, you could have a field name like datefrom that throws things off too.

But, assuming you have "simple" SQL statements, you can do it as:

select stuff(@query, charindex('from ', @query), 0, 'into '+@Table+' ')
from t;

EDIT:

The following is what you really want to do:

select *
into @Table
from (@query) q;

Using the subquery solves the problem.