DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to SELECT without column names?

Ocassionally you may get a data feed with changing column names into the same staging table. If you don't want to use select *, you can use the following Microsoft SQL Server dynamic SQL sample for preparing your own dynamic SQL query.

USE NorthWind

GO

 

DECLARE  @SchemaName  AS SYSNAME,

         @TableName   AS SYSNAME

 

DECLARE  @Col2  AS SYSNAME,

         @Col6  AS SYSNAME,

         @Col7  AS SYSNAME

 

DECLARE  @SQLstring  AS NVARCHAR(512)

 

SET @SchemaName = 'dbo'

 

SET @TableName = 'Products'

 

SELECT @Col2 = sc.name

FROM   sysobjects AS so

       INNER JOIN syscolumns AS sc

         ON so.id = sc.id

WHERE  so.name = @TableName

       AND sc.colid = 2

 

SELECT @Col6 = sc.name

FROM   sysobjects AS so

       INNER JOIN syscolumns AS sc

         ON so.id = sc.id

WHERE  so.name = @TableName

       AND sc.colid = 6

 

SELECT @Col7 = sc.name

FROM   sysobjects AS so

       INNER JOIN syscolumns AS sc

         ON so.id = sc.id

WHERE  so.name = @TableName

       AND sc.colid = 7

 

SELECT @SQLstring = 'select ' + @Col2 + ',' + @Col6 + ',' + @Col7 +

' from ' + @SchemaName + '.' + @TableName

PRINT @SQLstring

/*

select ProductName,UnitPrice,UnitsInStock from dbo.Products

*/

EXEC sp_executesql   @SQLstring

GO

/*

ProductName                   UnitPrice   UnitsInStock

....

Aniseed Syrup                 10.00       13

Chef Anton's Cajun Seasoning  22.00       53

Chef Anton's Gumbo Mix        21.35       0

Grandma's Boysenberry Spread  25.00       120

....

*/

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE