The result of a qry as a wry

Why not?

Create Procedure proc_name as
Select * from Table1 union all
Select * from Table2 union all
Select * from Table3 union all
Select * from Table4

Do you mean this?

CREATE PROCEDURE dbo.YourProcedureName
AS
BEGIN
Select * from Table1 union all
Select * from Table2 union all
Select * from Table3 union all
Select * from Table4
END

Select allOpen in new window

Calling the SP:
EXEC dbo.YourProcedureName

Of course, to use SELECT *  is not good coding practice.

hi guys...No, guess I wasn't exact enough in my request...

I don't want to have to retype the 'Select * from ....'

I want (in a perfect world)..results themselves to be already in the SP...basically all the select statements somehow as some sort of Dynamic SQL thing.. hopefully that makes sense

You don't need to create your own procedure. Store the query into a string variable and call the sp_ExecuteSQL with the variable as a parameter:

DECLARE @sql nvarchar(1000)
SET @sql = 
'Select * from Table1 union all
Select * from Table2 union all
Select * from Table3 union all
Select * from Table4'

EXEC sp_ExecuteSQL @sql

Select allOpen in new window

Of course, use your own code to populate the @sql string value-

When i do as you suggest.. I get '

Subquery returned more than 1 value, This is not permitted when the subquery follows =,!=..

There is no subquery in above unions... Instead of   EXEC sp_ExecuteSQL @sql   place the command  

PRINT @sql

and post the result.

Also please tell what SQL Server version do you use:

SELECT @@version

If all you are trying to do is keep from retyping common SQL commands, use script files.  Save the commands in a .sql file and execute them when necessary.  Then you have them forever and don't need to worry about procedures.

I'm not a SQL Server person so don't know the tools but it seems like SQLCMD can do this pretty easily and SSMS can if you enable SQLCMD commands.

I am not sure I am being clear enough....So I have included the RESULT that I need to incorporate.. It is truly a qry that gets me this Exact information.. I created a qry that really shows this below information.. but it may show more (Table5, Table6).. I need it be dynamic so I don't have re-run the underlying qry and then copy this result and run to get the true result from the below union


Select * from Table1 union all
Select * from Table2 union all
Select * from Table3 union all
Select * from Table4'
Screen-Shot-2020-04-21-at-7.38.38-AM.png

same result when I ran with Print @sql...

the version is 2014 (SP3-GDR)KB4505218)-12.0.6108.1 X64

Maybe if you post the query which produced the above result we can help better…
You nay try something like this:

DECLARE @sql nvarchar(max)
SET @sql = N''
SELECT @sql = @sql + ' ' + <<place the expression for Det column here>>
  FROM YourTable
PRINT @sql

Select allOpen in new window

I believe the question is how to dynamically build and execute the union all query from a list of tables or tables in a schema.

That is outside my SQL Server abailites.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
--EXEC dbo.your_proc_name_here
GO
CREATE PROCEDURE dbo.your_proc_name_here
    @table_prefix nvarchar(100) = 'MrpForecast[0-9][0-9]%',
    @where nvarchar(4000) = NULL,
    @order_by nvarchar(200) = NULL
AS
SET NOCOUNT ON;
DECLARE @sql nvarchar(max)
SELECT @sql = STUFF(CAST((
    SELECT ' UNION ALL SELECT * FROM [' + name + '] ' +
        CASE WHEN @where = '' OR @where IS NULL THEN ''
             ELSE 'WHERE ' + @where END
    FROM sys.tables
    WHERE name LIKE @table_prefix
    ORDER BY name
    FOR XML PATH(''), TYPE)
    AS nvarchar(max)), 1, 11, '')
IF @order_by <> ''
    SET @sql = @sql + ' ORDER BY ' + @order_by
PRINT @sql
EXEC(@sql)
/*end of proc*/
GO

Hi Scott,

I got this error when running the script
Msg 457, Level 16, State 1, Line 3
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in UNION ALL operator.

Ouch.  That's a pain.  The easiest way out is to use separate UNION ALL statements, one for each collation.  It's interesting that such similarly named tables would have different collations.  Is that intentional?!

not sure if its intentional.. but it's what I inherited   :(

OK.. stupid question time

How can I "use separate UNION ALL statements, one for each collation"...not sure I follow that...

That's not easy to do, and even then there'd be no easy way to combine them.

Create and run this proc so we can see how many columns of each collation we're dealing with.

CREATE PROCEDURE dbo.your_proc_name_here_metadata
    @table_prefix nvarchar(100) = 'MrpForecast[0-9][0-9]%'
AS
SET NOCOUNT ON;
DECLARE @sql nvarchar(max)
SELECT @sql = '
    SELECT
        t.name AS table_name,
        c.collation_name AS column_collation_name,
        COUNT(*) AS column_count
    FROM sys.tables t
    INNER JOIN sys.columns c ON c.object_id = t.object_id AND
        c.collation_name IS NOT NULL
    WHERE t.name LIKE ''' + @table_prefix + '''
    GROUP BY
        t.name,
        c.collation_name
    '
PRINT @sql
EXEC(@sql)
/*end of proc*/
GO

here it is
Table_List.xlsx

For now there's only one table that's not Latin1_General_BIN.  So, for right this minute, you can use the code below (NOTE: this code only expects a single table to not match Latin1_General_BIN; won't work if 2+ tables have that).

You can set @exec_sql = 0 and @print_sql = 1 to show the final UNION ALL sql without actually running it (yet).

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER  PROCEDURE dbo.your_proc_name_here
    @table_prefix nvarchar(100) = 'MrpForecast[0-9][0-9]%',
    @exec_sql bit = 1,
    @print_sql tinyint = 1, /*1=final sql only; >1 all sql*/
    @where nvarchar(4000) = NULL,
    @order_by nvarchar(200) = NULL
AS
SET NOCOUNT ON;
DECLARE @sql nvarchar(max)
DECLARE @sql2 nvarchar(max)
SELECT @sql = STUFF(CAST((
    SELECT ' UNION ALL SELECT * FROM [' + name + '] ' +
        CASE WHEN @where = '' OR @where IS NULL THEN ''
             ELSE 'WHERE ' + @where END
    FROM sys.tables t
    WHERE name LIKE @table_prefix AND
        EXISTS(SELECT 1 FROM sys.columns c WHERE c.object_id = t.object_id AND
            c.collation_name = 'Latin1_General_BIN')
    ORDER BY name
    FOR XML PATH(''), TYPE)
    AS nvarchar(max)), 1, 11, '')
IF @print_sql > 1
    PRINT 'sql = ' + @sql
SELECT @sql2 = STUFF(CAST((
    SELECT ', [' + c.name + '] ' +
        CASE WHEN c.collation_name IS NULL THEN '' ELSE 'COLLATE Latin1_General_BIN ' END
    FROM sys.tables t
    INNER JOIN sys.columns c ON c.object_id = t.object_id
    WHERE t.name LIKE @table_prefix AND
        EXISTS(SELECT 1 FROM sys.columns c WHERE c.object_id = t.object_id AND
            c.collation_name <> 'Latin1_General_BIN')
    ORDER BY t.name, c.column_id
    FOR XML PATH(''), TYPE
    ) AS nvarchar(max)), 1, 2, '')
IF @print_sql > 1
    PRINT '@sql2 = ' + @sql2
SELECT @sql2 = CASE WHEN @sql IS NULL THEN '' ELSE ' UNION ALL ' END +
    'SELECT ' + @sql2 + 'FROM [' + t.name + '] ' +
        CASE WHEN @where = '' OR @where IS NULL THEN ''
             ELSE 'WHERE ' + @where END
FROM sys.tables t
WHERE t.name LIKE @table_prefix AND
    EXISTS(SELECT 1 FROM sys.columns c WHERE c.object_id = t.object_id AND
        c.collation_name <> 'Latin1_General_BIN')
SET @sql = ISNULL(@sql, '') + ' ' + ISNULL(@sql2, '')
IF @order_by <> ''
    SET @sql = @sql + ' ORDER BY ' + @order_by
IF @print_sql > 0
    PRINT '@sql = ' + @sql
IF @exec_sql = 1
    EXEC(@sql)
/*end of proc*/
GO

Assuming that one table's collation was a mistake, how hard is it to just change it to match the rest?

That isn't really the problem.  The problem is the code running against that table which expects data to be case insensitive.  If you change collation to BIN, it will be case sensitive.  That could cause massive logic errors in queries.

So this is a great answer.. edited slightly to ignore a letter after the last number that is associated with MrpForecast...

Is there anyway to add a

Select * into ConsolidatedMrp ?

Thank you Scott, You nailed this.. Very much appreciated

Hi there, So I have found this to be very useful...

is there anyway I can make this so it only does the SELECT * INTO ConsolidatedMrp piece of the Stored Procedure..

I would rather it not show the 'Results' of all the lines in the print screen  when I run the Exec MRPf?

I tried editing the

@exce_sql bit = 1
@print_sql tinyint = 1, /*1=final sql only;  >1 all sql */

and it doesn't seem to change.. all 1 million plus records show in the results...I would rather it say 'Completed', but still push the 1 million records to the ConsolidatedMrp table.

Thank you

I would need to see the exact code you are running to be sure what to change.

Run it with @exec_sql = 0 and @print_sql = 1 and send me the print outs of the SQL that you get back from that.

Return Value
0

Well does that mean that it ran as I had hoped.. ..?

:)


So fast I didn't realize what the hell happened

No, that will just print out the SQL.  You'd probably want to be in SSMS so you can see the results of the PRINT statements in the result window below the query.

is it possible to not have the PRINT within the results...maybe just say a '1100000 records'....completed or something simple

For testing purposes, you can run it in SSMS without ANY actual data rows returned.

Or just verify yourself that the query that is actually running inserts the rows properly.

Note that if the table already exists, you can't do a
"SELECT * INTO dbo.existing_table_name FROM".  
You would have to change it to:
"INSERT INTO dbo.existing_table_name SELECT * FROM ...