Using a synonym in a make-table query ("select into")

Question

I always thought a synonym in T-SQL was just a convenient abbreviation. Yet when I do the following ...

create synonym BACKUP_TABLE for T_SHORT_NAMES_BACKUP go

select * into BACKUP_TABLE from T_SHORT_NAMES

... I get the error that there is already an object called BACKUP_TABLE. Am I doing something wrong?

score 2 · Answer 1 · answered Sep 18 '14 at 19:44

Synonyms are pointers to other SQL tables. They are extremely useful depending on what you are wanting to do. You can point them to a table in another database, or a table on another server (through a linked server). We leverage them a lot in our ETLs

The process I use to generate mine:

Query to build synonyms dynamically:

SELECT 
    'CREATE SYNONYM [dbo].[' +TABLE_NAME+ '] FOR [' + 'Put database name here or remove' + '].[dbo].['+TABLE_NAME+']'    
FROM 
    INFORMATION_SCHEMA.TABLES
WHERE 
    TABLE_TYPE = 'BASE TABLE'

From there, you just SELECT * FROM TABLE_NAME

Now, to circle back to your question. You create a synonym for BACKUP_TABLE that points to T_SHORT_NAMES_BACKUP.

Try: SELECT * FROM BACKUP_TABLE

To find out more about your synonyms: SELECT name, base_object_name FROM sys.synonyms

timo.rieber · Answer 2 · 2014-09-18T19:55:10.117

2

As select ... into ... always creates a new table object with the given name there must not exist a object with the same name already.

Simply use your select ... into ... standalone, there is no need to add a synonym.

But if you want to add additional rows to your T_SHORT_NAMES_BACKUP use insert into ... select ... instead:

insert into
    T_SHORT_NAMES_BACKUP

select
    *

from
    T_SHORT_NAMES

edited Sep 18 '14 at 19:55

answered Sep 18 '14 at 19:47

timo.rieber

3,727
3
32
47

Okay, this answers my question: You CAN'T use a synonym in a make-table query. But I thought SSMS could distinguich between a synonym-object and a table-object. In any case, I do need a synonym. There are several queries in the script, each using the name "T_SHORT_NAME_Backup", a name which will change each week. I don't want to change all the instances of this name. – Dom Sep 18 '14 at 19:57
Ok, then simply change the order of your statements. First create your backup into a new table with a new object name using `select ... into ...` and afterwards create the synonym for this table object. – timo.rieber Sep 18 '14 at 20:07
Good answer. And I guess that's the best I can hope for. I still have two lines to change each week instead of one, but you can't have it all! – Dom Sep 18 '14 at 20:12

Using a synonym in a make-table query ("select into")

2 Answers2