CODEWITHSUNDEEP
sqlsql-serverssms
Jfabs
Jfabs

Reputation: 563

Using a function within a Select statement for conversion

So let's say I have these two tables with five columns, four of which match up perfectly but the last of which have different data types, one being char, the other being uniqueidentifier. But I have this function, let's call it dbo.x that accepts the uniqueidentifier and hunts down the corresponding name to it elsewhere in the database.

So what I need to do is develop this script that will copy the data from table1 to table2 and convert the last column using the function.

After doing a bit of research I tried this (which clearly hasn't worked):

INSERT INTO table1 (col1, col2, col3, col4, col5)
SELECT col1, col2, col3, col4, col5 AS dbo.x(table2.col5)
FROM table2

Can anybody give me a little advice on where to go from here?

Upvotes: 2

Views: 116

Answers (2)

Young Bob
Young Bob

Reputation: 743

How about this

INSERT INTO table1 (col1, col2, col3, col4, col5)
SELECT col1, col2, col3, col4, dbo.x(col5)
FROM table2

Upvotes: 3

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

You have switched the expression and alias - the syntax should be as follows:

INSERT INTO table1 (col1, col2, col3, col4, col5)
SELECT col1, col2, col3, col4, dbo.x(table2.col5) AS col5
FROM table2

In general, when you use an expression in the select statement and would like to give the resultant column a name, you write the expression first, and then its alias, e.g.

SELECT <expression> AS <column-name>
FROM ...

Upvotes: 3

Related Questions