1

I have a query that joins 7 tables with a total of 852 columns in SQL Server 2008 R2 Express. Im trying to create a new table with the output of that query

Tried using INTO but as some columns in the tables have repeating names its not working 

USE VistaBI
SELECT *
INTO NewTable
FROM         dbo.Agente FULL JOIN
                      dbo.Cte ON dbo.Agente.Agente = dbo.Cte.Agente 
                      FULL JOIN
                      dbo.CteEnviarA ON dbo.Agente.Agente = dbo.CteEnviarA.Agente 
                      FULL JOIN
                      dbo.Unidad ON dbo.CteEnviarA.Unidad = dbo.Unidad.Unidad 
                      FULL JOIN
                      dbo.Venta ON dbo.Agente.Agente = dbo.Venta.Agente 
                      FULL JOIN
                      dbo.VentaD ON dbo.Agente.Agente = dbo.VentaD.Agente 
                      FULL JOIN
                      dbo.Art ON dbo.VentaD.Articulo = dbo.Art.Articulo
                      FULL JOIN 
                      dbo.Alm ON dbo.Venta.Almacen = dbo.Alm.Almacen

Is there a way to create the table automatically, instead of having to type each of the 852 columns with a unique name and datatype?

Improve this question
6
  • What is your desired result for repeating column names? The column names in the resulting table must be unique, so what would you want them to be when they repeat? You can probably do something with dynamic sql. However, I bet it would be a more productive use of your time to think about WHY you want a table with 852 columns, and whether there might not be a better way to achieve that ultimate goal. Commented Apr 3, 2019 at 17:48
  • Hi Tab, i dont care much about the column names, i will import all this information into a BI tool so i would need the 852 columns. Commented Apr 3, 2019 at 18:24
  • Can you be more specific about what "import into a BI Tool" means? Maybe you create a view instead of a new table? What is the reason you think you need to create this table as opposed to some other solution? Commented Apr 3, 2019 at 18:26
  • Hi Tab, the full scope of this is that we have to publish this new table into Azure, the whole database has 1043 tables and i only need the information of these 7 tables mentioned. this table will be published in Azure and then connected to PowerBI to do the required analysis Commented Apr 3, 2019 at 18:30
  • Why not publish the individual tables in Azure, and write a view or stored procedure that does this select? PowerBI can use the view or proc. Commented Apr 3, 2019 at 18:31

3 Answers 3

Reset to default
2

I did it dynamically, try this code: Column names will become tablename + column name so no column name will be duplicated.

DECLARE @strColumns varchar(max) = ''

SELECT @strColumns = COALESCE(@strColumns + ',', '') + A.TableName + '.' + QuoteName(Column_Name) + ' AS ' + A.Alies
FROM (
SELECT Column_Name, 'Agente' TableName, 'Agente_' + QuoteName(Column_Name) AS Alies FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'Agente' AND DATA_TYPE != 'timestamp'
UNION ALL
SELECT Column_Name, 'Cte' TableName, 'Cte_' + QuoteName(Column_Name) AS Alies FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'Cte' AND DATA_TYPE != 'timestamp'
UNION ALL
SELECT Column_Name, 'CteEnviarA' TableName, 'CteEnviarA_' + QuoteName(Column_Name) AS Alies FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'CteEnviarA' AND DATA_TYPE != 'timestamp'
UNION ALL
SELECT Column_Name, 'Unidad' TableName, 'Unidad_' + QuoteName(Column_Name) AS Alies FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'Unidad' AND DATA_TYPE != 'timestamp'
UNION ALL
SELECT Column_Name, 'Venta' TableName, 'Venta_' + QuoteName(Column_Name) AS Alies FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'Venta' AND DATA_TYPE != 'timestamp'
UNION ALL
SELECT Column_Name, 'VentaD' TableName, 'VentaD_' + QuoteName(Column_Name) AS Alies FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'VentaD' AND DATA_TYPE != 'timestamp'
UNION ALL
SELECT Column_Name, 'Art' TableName, 'Art_' + QuoteName(Column_Name) AS Alies FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'Art' AND DATA_TYPE != 'timestamp'
UNION ALL
SELECT Column_Name, 'Alm' TableName, 'Alm_' + QuoteName(Column_Name) AS Alies FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'Alm' AND DATA_TYPE != 'timestamp'
) AS A

SELECT @strColumns =SUBSTRING(@strColumns,2, len(@strColumns) - 1)
SELECT @strColumns


EXEC( 'SELECT 
    '+ @strColumns +' INTO ##temp
    FROM dbo.Agente 
    FULL JOIN dbo.Cte ON dbo.Agente.Agente = dbo.Cte.Agente 
    FULL JOIN dbo.CteEnviarA ON dbo.Agente.Agente = dbo.CteEnviarA.Agente 
    FULL JOIN dbo.Unidad ON dbo.CteEnviarA.Unidad = dbo.Unidad.Unidad 
    FULL JOIN dbo.Venta ON dbo.Agente.Agente = dbo.Venta.Agente 
    FULL JOIN dbo.VentaD ON dbo.Agente.Agente = dbo.VentaD.Agente 
    FULL JOIN dbo.Art ON dbo.VentaD.Articulo = dbo.Art.Articulo
    FULL JOIN dbo.Alm ON dbo.Venta.Almacen = dbo.Alm.Almacen')

SELECT * FROM ##temp
DROP TABLE ##temp
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thanks Hasan, unfortunately when I try your solution i get an error message that a table can only have one timestamp type column, not sure if its related to your code or that tables structure
i can help you, let me update my code, there are multiple timestamp columns, we need to exclude that
@rzbartali code updated, try now and let me know if you get any other error
Tip: The best practice when assembling object names into dynamic SQL statements is to use QuoteName() to avoid problems with odd names, e.g. New Table with a space or reserved words like From.
You probably don't want to apply QuoteName to a part of the column name, e.g. 'Agente_' + QuoteName(Column_Name) and then repeat the process with A.TableName + '.' + QuoteName(Column_Name).
|
0

select column name explicity rather than using *

below i put an exmple by using two tables

select 
    a.person_id,
    a.city,
    city.city_id
into 
    mytable 
from 
    table1 a
inner join 
    city on a.city = city.city

so in your case 

SELECT table_name.col1,table_nae.col2..... use explicitly column name in selection
INTO NewTable
FROM         dbo.Agente FULL JOIN
                      dbo.Cte ON dbo.Agente.Agente = dbo.Cte.Agente 
                      FULL JOIN
                      dbo.CteEnviarA ON dbo.Agente.Agente = dbo.CteEnviarA.Agente 
                      FULL JOIN
                      dbo.Unidad ON dbo.CteEnviarA.Unidad = dbo.Unidad.Unidad 
                      FULL JOIN
                      dbo.Venta ON dbo.Agente.Agente = dbo.Venta.Agente 
                      FULL JOIN
                      dbo.VentaD ON dbo.Agente.Agente = dbo.VentaD.Agente 
                      FULL JOIN
                      dbo.Art ON dbo.VentaD.Articulo = dbo.Art.Articulo
                      FULL JOIN 
                      dbo.Alm ON dbo.Venta.Almacen = dbo.Alm.Almacen
Improve this answer

4 Comments

what if there are 100s of columns to be fetched? As mentioned .. 852
@Rhythm write 100 columns name by using table_name.columname
yes that solution I know, I was wondering if there is some distinct type function to pull unique columns automatically instead of writing every column name explicitly .. I guess there isn't
This doesn't attempt to answer the question: "Is there a way to create the table automatically, instead of having to type each of the 852 columns with a unique name and datatype?"
0

What you could do is create a table initally and then use dynamic alter statements to add the number of columns you need an example would be :

CREATE TABLE MYTABLE 
(
A INT,
B INT
)


DECLARE @I INT = 1, @COLNAME VARCHAR(20),@SQL VARCHAR(MAX)
WHILE (@I < 852)
BEGIN
SET @COLNAME = CAST(@I AS VARCHAR(20))


SET @SQL ='
ALTER TABLE MYTABLE
ADD [' + @COLNAME + '] VARCHAR(MAX);'
PRINT @SQL
EXEC(@SQL)
SET @I = @I + 1

END

Then just change the insert statement to:

INSERT INTO mytable
SELECT *
FROM   dbo.agente
       FULL JOIN dbo.cte
              ON dbo.agente.agente = dbo.cte.agente
       FULL JOIN dbo.cteenviara
              ON dbo.agente.agente = dbo.cteenviara.agente
       FULL JOIN dbo.unidad
              ON dbo.cteenviara.unidad = dbo.unidad.unidad
       FULL JOIN dbo.venta
              ON dbo.agente.agente = dbo.venta.agente
       FULL JOIN dbo.ventad
              ON dbo.agente.agente = dbo.ventad.agente
       FULL JOIN dbo.art
              ON dbo.ventad.articulo = dbo.art.articulo
       FULL JOIN dbo.alm
              ON dbo.venta.almacen = dbo.alm.almacen

This will give you results with column names as 1,2,3.... etc but it is a dynamic name that will keep you from having to manually name your columns.

Improve this answer

2 Comments

Thanks, but unfortunately i would need a semi descriptive name as this is going to be used for a BI tool
@rzbartali then why does your response to my comment on your question say "I don't care much about column names"? You make it very hard for people to help you when you keep changing the question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.