CODEWITHSUNDEEP
sql-server
Developer
Developer

Reputation: 428

Way to select all columns from table and near each one the same column name with prefix?

Let's say I have a table with the following columns:

"col1", "col2", "col3", "new_col1", "new_col2", "new_col3" ...

I want to display the columns in the following way:

select col1, new_col1, col2, new_col2, col3, new_col3 ...

Is there a select command/keyboard shortcut that I can do that without copy each column in the select command

Thank you

Upvotes: 0

Views: 175

Answers (2)

Jan Madeyski
Jan Madeyski

Reputation: 359

I have a custom procedure mapped to Ctrl+8 shortcut.

It's something like that:

CREATE or ALTER PROC dbo.utl_get_select(
  @table sysname,
  @alias varchar(10) = 'v'
)
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @query varchar(max) = 'SELECT';

  SELECT @alias = ISNULL(@alias, 'v')

  SELECT @query = @query + ' ' + @alias + '.' + QUOTENAME(c.name)+','
  FROM sys.objects t 
  JOIN sys.columns c ON c.object_id = t.object_id
  WHERE t.name = @table
  ORDER BY c.column_id ASC;

  SELECT @query = LEFT(@query, LEN(@query)-1) + ' ';
  SELECT @query + 'FROM dbo.' + @Table + ' ' + @alias;
END;

We could also use STRING_AGG in SQL Server 2017 or newer.

You can also compare columns names to achieve some customised order.

And the result for EXEC dbo.utl_get_select 'users'

SELECT v.[id], v.[login], v.[email], v.[name], v.[last_name] FROM dbo.users v

Upvotes: 1

Emka
Emka

Reputation: 350

Here is a script for Insert and Select

DECLARE @String nvarchar(max)='',@Column nvarchar(100),@Insert nvarchar(max), @Select nvarchar(max),@TBL_NAME nvarchar(150)

SET @TBL_NAME = 'TestTable'

DECLARE CURS CURSOR LOCAL FAST_FORWARD FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBL_NAME

OPEN CURS
FETCH NEXT FROM CURS INTO @Column

WHILE @@FETCH_STATUS=0
BEGIN
    SET @String = @String +'['+@Column+'],'+'['+@Column+'_NEW],'
    FETCH NEXT FROM CURS INTO @Column
END
CLOSE CURS
DEALLOCATE CURS

SET @String = SUBSTRING(@String,1,LEN(@String)-1)
SET @Insert = 'INSERT INTO ['+@TBL_NAME+'] ('+@String+')'
SET @Select = 'SELECT '+@String

Select @Insert,@Select

Upvotes: 0

Related Questions