YASQ: Yet another SQL question mssql/tsql

[rsutoratosu]

Is there a way to find all the columns in a table and build a dynamics insert select string out it ?
If a table has col A, B, C
FIND A + B + C or more and build a dynamic sql string to INSERT into another table ?

Basically

INSERT INTO NEWTABLE (A, B, C)
SELECT A, B C FROM OLDTABLE

Can't generate enough cash flow, I need to write a script that builds that and run it as billable hours... I mean the 2 table will never change, the amount of columns will never change. so I cant just do a regular insert & select.

 

[Cogman]

I'm not sure I understand what you are asking

I mean the 2 table will never change, the amount of columns will never change. so I cant just do a regular insert & select.

Are you saying that the contents of the tables never change or that the schema of the tables never change? If the schema doesn't change, why wouldn't simply writing your example work? That'd be my preferred solution.

In any event, SQL Server's metadata is highly queryable. I'd start with this blog post and work from there.

SQL Server INFORMATION_SCHEMA Views | See if a Table Exists

INFORMATION_SCHEMA Views allow you to find what tables are in your schema. Read this tutorial to learn how to utilize this SQL Server tool.

chartio.com

 

[pauldun170]

Is there a way to find all the columns in a table and build a dynamics insert select string out it ?
If a table has col A, B, C
FIND A + B + C or more and build a dynamic sql string to INSERT into another table ?

Basically

INSERT INTO NEWTABLE (A, B, C)
SELECT A, B C FROM OLDTABLE

Can't generate enough cash flow, I need to write a script that builds that and run it as billable hours... I mean the 2 table will never change, the amount of columns will never change. so I cant just do a regular insert & select.

To auto gen based on unknown columns and their given datatype is simple
Use the INFORMATION_SCHEMA
If you want to then use that info to dynamically insert to another table, create a procedure that will map the columns and make sure the dataypes are happy.

https://stackoverflow.com/questions/4849652/find-all-tables-containing-column-with-specified-name-ms-sql-server

SQL SERVER - Query to Find Column From All Tables of Database - SQL Authority with Pinal Dave

One question came up just a day ago while I was writing SQL SERVER - 2005 - Difference Between INTERSECT and INNER JOIN - INTERSECT vs. INNER JOIN. How

blog.sqlauthority.com

SQL DevOps tools | ApexSQL

SQL DevOps tools required to drive an automated DevOps workflow

solutioncenter.apexsql.com