Link to home
Create AccountLog in
Avatar of Richard Lloyd
Richard Lloyd

asked on

Avoid duplicate fieldnames on SQL into query with wildcard

Hi


I need to find a way to perform an MS SQL select into a query where I have duplicated field names from various table joins and I am selecting " * from " each of the tables in the query.


Is there a way to append the table name before the field name?


table1:

id, value, createddate
1, xxx, 2023-05-24,
2, yyy, 2023-05-23,

Open in new window


table 2:

id, information, createddate, table1id
101, aaaa, 2023-05-24,1,
102, bbbb, 2023-05-23,2,

Open in new window


I need the resulting column headers to be:

table1_id, table1_value, table1_createddate, table2_id, table2_information, table2_createddate
....

Open in new window

My query needs to be in the format


select table1.*, table2.* into temptable from table1 inner join table2 on table1.id=table2.table1id

Open in new window

 but I get errors like:


The column 'Id' was specified multiple times for ....

Open in new window


The query is very "dynamic", so the field names will change frequently, so I need to use the wildcard. I know that the problem can be overcome with defined field names.


Any advice would be very appreciated



Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Interesting q.  


Could you call a stored proc before running your INSERT code?  Honestly, the easiest way to do this would be have a stored proc that takes source table name(s) and output table name as params and creates the table for you.  Your other code could then INSERT into that (new) table.

Avatar of Richard Lloyd
Richard Lloyd

ASKER

I could use a store procedure, but would not know where to start!


Create a table the the sys info with all the columns as table_field and then insert in to it?

I can work on creating that proc now, since you've confirmed your requirements.

Awesome Scott!

Well, not "now" as in right now, but "now" as in once I understood the requirements :-). 

Do you need the code to keep computed columns?  I've got the code working except for those.

I am using the function dbo.DelimitedSplit8K to split the @source_tables param;  Google it to get the function source code.


I'm hoping the param names are self-explanatory.  If not, naturally feel free to ask me any qs you may have.


USE master;

SET ANSI_NULLS ON;

SET QUOTED_IDENTIFIER ON;

--

GO

CREATE PROCEDURE dbo.sp_construct_table

    @source_tables varchar(8000),

    @destination_table varchar(260),

    @sql nvarchar(max) = NULL OUTPUT,

    @replace_destination_table bit = 0,

    @keep_identity_setting bit = 0,

    @force_nullability bit = NULL,

    @drop_computed_columns bit = 0,

    @drop_max_columns bit = 0,

    @select_final_sql bit = 1,

    @exec_final_sql bit = 1

AS

/* WARNING!!: Computed columns may not be copied correctly.!! */


/*

DECLARE

    @source_tables varchar(8000),

    @destination_table varchar(260),

    @sql nvarchar(max) = NULL,

    @replace_destination_table bit = 0,

    @keep_identity_setting bit = 0,

    @force_nullability bit = NULL,

    @drop_computed_columns bit = 0,

    @drop_max_columns bit = 0,

    @select_final_sql bit = 1,

    @exec_final_sql bit = 1

SELECT

    @source_tables = 'dbo.table1,dbo.table2',

    @destination_table = 'new_table'

*/


DECLARE @collation_name nvarchar(126);

DECLARE @column_ordinal int;

DECLARE @computed_collation_name nvarchar(126);

DECLARE @computed_definition nvarchar(max);

DECLARE @computed_uses_database_collation bit;

DECLARE @computed_is_hidden bit;

DECLARE @computed_is_nullable bit;

DECLARE @computed_is_persisted bit;

DECLARE @is_computed_column bit;

DECLARE @is_hidden bit;

DECLARE @is_identity_column bit;

DECLARE @is_nullable bit;

DECLARE @name nvarchar(128);

DECLARE @object_id int;

DECLARE @source_table nvarchar(260);

DECLARE @system_type_name nvarchar(256);


DROP TABLE IF EXISTS #source_tables;

CREATE TABLE #source_tables (

    id int NOT NULL IDENTITY(1, 1),

    source_table varchar(260) NOT NULL PRIMARY KEY

    );


DROP TABLE IF EXISTS #column_definitions;

CREATE TABLE #column_definitions (

    object_id int NULL,

   is_hidden bit NULL,

   column_ordinal int NULL,

   name sysname NULL,

   is_nullable bit NULL,

   system_type_id int NULL,

   system_type_name nvarchar(256) NULL,

   max_length smallint NULL,

   precision tinyint NULL,

   scale tinyint NULL,

   collation_name sysname NULL,

   user_type_id int NULL,

   user_type_database sysname NULL,

   user_type_schema sysname NULL,

   user_type_name sysname NULL,

   assembly_qualified_type_name nvarchar(4000) NULL,

   xml_collection_id int NULL,

   xml_collection_database sysname NULL,

   xml_collection_schema sysname NULL,

   xml_collection_name sysname NULL,

   is_xml_document bit NULL,

   is_case_sensitive bit NULL,

   is_fixed_length_clr_type bit NULL,

   source_server sysname NULL,

   source_database sysname NULL,

   source_schema sysname NULL,

   source_table sysname NULL,

   source_column sysname NULL,

   is_identity_column bit NULL,

   is_part_of_unique_key bit NULL,

   is_updateable bit NULL,

   is_computed_column bit NULL,

   is_sparse_column_set bit NULL,

   ordinal_in_order_by_list smallint NULL,

   order_by_list_length smallint NULL,

   order_by_is_descending smallint NULL,

   tds_type_id int NULL,

   tds_length int NULL,

   tds_collation_id int NULL,

   tds_collation_sort_id tinyint NULL

)


SET NOCOUNT ON;


IF @exec_final_sql = 0

    SET @select_final_sql = 1;   


INSERT INTO #source_tables

SELECT LTRIM(RTRIM(ds.Item))

FROM dbo.DelimitedSplit8K(@source_tables, ',') AS ds

ORDER BY ds.ItemNumber


--SELECT * FROM #source_tables ORDER BY id


INSERT INTO #column_definitions (

    is_hidden, column_ordinal, name, is_nullable, system_type_id,

    system_type_name, max_length, precision, scale, collation_name,

    user_type_id, user_type_database, user_type_schema, user_type_name, assembly_qualified_type_name,

    xml_collection_id, xml_collection_database, xml_collection_schema, xml_collection_name, is_xml_document,

    is_case_sensitive, is_fixed_length_clr_type, source_server, source_database, source_schema,

    source_table, source_column, is_identity_column, is_part_of_unique_key, is_updateable,

    is_computed_column, is_sparse_column_set, ordinal_in_order_by_list, order_by_list_length, order_by_is_descending,

   tds_type_id, tds_length, tds_collation_id, tds_collation_sort_id    

    )

EXEC sys.sp_describe_first_result_set @tsql = N'SELECT * FROM dbo.dbs'

UPDATE #column_definitions

SET object_id = OBJECT_ID('dbo.dbs')

WHERE object_id IS NULL;


INSERT INTO #column_definitions (

    is_hidden, column_ordinal, name, is_nullable, system_type_id,

    system_type_name, max_length, precision, scale, collation_name,

    user_type_id, user_type_database, user_type_schema, user_type_name, assembly_qualified_type_name,

    xml_collection_id, xml_collection_database, xml_collection_schema, xml_collection_name, is_xml_document,

    is_case_sensitive, is_fixed_length_clr_type, source_server, source_database, source_schema,

    source_table, source_column, is_identity_column, is_part_of_unique_key, is_updateable,

    is_computed_column, is_sparse_column_set, ordinal_in_order_by_list, order_by_list_length, order_by_is_descending,

   tds_type_id, tds_length, tds_collation_id, tds_collation_sort_id    

    )

EXEC sys.sp_describe_first_result_set @tsql = N'SELECT * FROM dbo.ctsi_ignores'

UPDATE #column_definitions

SET object_id = OBJECT_ID('dbo.ctsi_ignores')

WHERE object_id IS NULL;


DECLARE column_cursor CURSOR LOCAL FAST_FORWARD FOR

SELECT st.source_table, 

    cd.object_id, cd.is_hidden, cd.column_ordinal,

    cd.name, cd.is_nullable, cd.system_type_name, cd.collation_name,

    cd.is_identity_column, cd.is_computed_column,

    cc.collation_name, cc.is_nullable, cc.definition, cc.uses_database_collation, cc.is_persisted

FROM #column_definitions cd

INNER JOIN #source_tables st ON OBJECT_ID(st.source_table) = cd.object_id

LEFT OUTER JOIN sys.computed_columns cc ON cd.is_computed_column = 1 AND cc.object_id = cd.object_id AND cc.column_id = COLUMNPROPERTY(cd.object_id, cd.name, 'ColumnId')

ORDER BY st.id, cd.is_computed_column, cd.column_ordinal;


SET @sql = CASE WHEN @replace_destination_table = 1 THEN 'DROP TABLE IF EXISTS ' + @destination_table + ';' + CHAR(13) + CHAR(10) ELSE '' END + 

    'CREATE TABLE ' + @destination_table + ' ( ' + CHAR(13) + CHAR(10)


OPEN column_cursor;

WHILE 1 = 1

BEGIN

    FETCH NEXT FROM column_cursor INTO @source_table, 

        @object_id, @is_hidden, @column_ordinal, 

        @name, @is_nullable, @system_type_name, @collation_name,

        @is_identity_column, @is_computed_column,

        @computed_collation_name, @computed_is_nullable, @computed_definition, @computed_uses_database_collation, @computed_is_persisted

    IF @@FETCH_STATUS <> 0

        BREAK;

    IF @drop_max_columns = 1 AND @system_type_name LIKE '%(max)%'

        CONTINUE;

    /* since @source_table can include a schema, make sure we use only the table name to prefix the column name */

    SET @source_table = PARSENAME(@source_table, 1);

    IF @force_nullability = 0

    BEGIN

        SET @is_nullable = 0

        /*SET @computed_is_nullable = 0*/

    END /*IF*/

    ELSE

    IF @force_nullability = 1

    BEGIN

        SET @is_nullable = 1

        SET @computed_is_nullable = 1

    END /*ELSE*/

    IF @drop_computed_columns = 0 AND @computed_definition IS NOT NULL

        SET @computed_definition = REPLACE(@computed_definition, '[', '[' + @source_table + '_')


    SET @sql = @sql + 

        CASE WHEN @is_computed_column = 1 THEN CASE WHEN @drop_computed_columns= 0 THEN 

            '[' + @source_table + '_' + @name + '] ' + 'AS ' + @computed_definition + ' ' +

            CASE WHEN @computed_uses_database_collation = 1 THEN '' ELSE ISNULL('COLLATE ' + @computed_collation_name + ' ', '') END +

            CASE WHEN @computed_is_nullable = 0 THEN 'NOT NULL ' ELSE '' END +

            CASE WHEN @computed_is_persisted = 1 THEN 'PERSISTED ' ELSE '' END +

            ', ' + CHAR(13) + CHAR(10) 

            ELSE '' END

        ELSE

            '[' + @source_table + '_' + @name + '] ' + 

            @system_type_name + ' ' +

            ISNULL('COLLATE ' + @collation_name + ' ', '') +

            CASE WHEN @is_nullable = 0 THEN 'NOT ' ELSE '' END + 'NULL ' +

            CASE WHEN @keep_identity_setting = 1 AND @is_identity_column = 1 THEN 'IDENTITY ' ELSE '' END +

            ', ' + CHAR(13) + CHAR(10) 

        END

    /* new table can have only one IDENTITY column, so don't keep any identity the first one, even if @keep_identity_setting is on */

    IF @keep_identity_setting = 1 AND @is_identity_column = 1

        SET @keep_identity_setting = 0;

END /*IF*/

CLOSE column_cursor;

DEALLOCATE column_cursor;


SET @sql = LEFT(@sql, LEN(@sql) - 4) + ' )';


IF @select_final_sql = 1

    SELECT @sql AS [--sql]


IF @exec_final_sql = 1

    EXEC(@sql);


SET NOCOUNT OFF;

RETURN 0;

GO

EXEC sys.sp_MS_marksystemobject 'dbo.sp_construct_table' --<< -- !!CRITICAL, DON'T leave off!! (except when ALTERing the original proc)

Wow!!!!!!!!!! What can I say! I was not expecting all that!' Ill give it a go...


I do use computed columns, but I'll test without them to start with. 


If I wanted to run it over, say table1, table2 and table3, all inner joined on an id column in each table, how should I call the procedure? Something like this?


sp_construct_table  table1,table2,table3  Newtable  select * from table1 inner join table2 on table1.id=table2.id........table3 etc

   

  These look like I can ignore and use the defaults:

    @replace_destination_table bit = 0,

    @keep_identity_setting bit = 0,

    @force_nullability bit = NULL,

    @drop_computed_columns bit = 0,

    @drop_max_columns bit = 0,

    @select_final_sql bit = 1,

    @exec_final_sql bit = 1

First, create the required dbo.DelimitedSplit8K function in your destination db.


Then:


USE [your_db];

GO

CREATE FUNCTION dbo.DelimitedSplit8K

/* Google to get the source for this, or I can post it if you prefer */

GO


USE [your_db];

EXEC dbo.sp_construct_table 'table1,table2,table3', 'newtable'


If the computed column(s) don't work, then try:


USE [your_db];

EXEC dbo.sp_construct_table 'table1,table2,table3', 'newtable', @drop_computed_columns=1

 

ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account

Amazing!


I'm going to have to wait until Thursday to give this a try. Bear with me.


Thanks so much!