0

This is what I am working with:

Select fields 1-24 dbo.tablename where multiple !=2;

However, in the original table, tablename, some fields are titled differently. This is a work in progress so pardon some of the inefficiencies. One example is the 6th column/field, which is titled xacct in tablename, but titled xaccount in result.

In short: Can one set up a command such as above and still account for differing field names all the while leaving the data and it's type unchanged?

Tim Wilcox
  • 1,275
  • 2
  • 19
  • 43
  • 1
    Possible duplicate of [Insert into ... values ( SELECT ... FROM ... )](https://stackoverflow.com/questions/25969/insert-into-values-select-from) – jmoerdyk Sep 11 '17 at 18:19
  • Would the following code be sufficient? INSERT INTO Result (xaccount) Select xacct from dbo.tablename – Tim Wilcox Sep 11 '17 at 18:22

1 Answers1

1

if you are doing an insert/select, column names are irrelevant, only order is important. If you are trying to rename the columns in a select statement, use the standard SQL:

SELECT field1 AS column1
     , field2 AS column2
     , field3 AS column3
     , multiple AS multiply
  FROM dbo.tablename
 WHERE multiple != 2;

Where 'FIELD1' is original column name, 'COLUMN1' is new name you are providing.

You don't have to specify new names for all of the columns, only those you are changing:

SELECT field1 AS tedd_e_bear
     , field2           
     , field3 AS spin_fidget
     , multiple AS multiply
  FROM dbo.tablename
 WHERE multiple != 2;

In the above example, field 2 still has the name field2.

Brian Leach
  • 2,025
  • 1
  • 11
  • 14
  • One quick followup item. I understand fully the commands given in your answer. However, if only some change would I add lines such for those that not change column name. Or would I simply add a ,something in the commands? ,something AS something – Tim Wilcox Sep 11 '17 at 18:43
  • 1
    @TimWilcox I added a second example showing you only rename what you want to rename. Again, please remember that inserts statements don't require the column names to be the same, only the order. – Brian Leach Sep 11 '17 at 18:58
  • I am using this and getting the following error: Msg 213, Level 16, State 1, Line 1 Column name or number of supplied values does not match table definition – Tim Wilcox Sep 11 '17 at 22:54
  • See https://stackoverflow.com/questions/22692399/insert-issue-msg-213-level-16-state-1-line-2 – Brian Leach Sep 12 '17 at 23:31