0

I want to use dbo.split and insert data into temp table. Please correct the following query.

SELECT  dbo.Split('002,003,004', ',') into #Temp



 select * from #Temp

Expected Output:
Items
002
003
004

Error message thrown:

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Split", or the name is ambiguous

SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
Ajay Prajapati
  • 63
  • 1
  • 10
  • What is the expected outcome versus the actual outcome? Did you get any error messages? – SchmitzIT May 29 '17 at 07:16
  • I do not really see something wrong. What is not working? – Arion May 29 '17 at 07:17
  • I am getting this error: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Split", or the name is ambiguous. – Ajay Prajapati May 29 '17 at 07:17
  • Split() is not a default SQL Server function. Is that the issue here? SQL Server 2016 introduced a STRING_SPLIT function, though (https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql) – SchmitzIT May 29 '17 at 07:22
  • @AjayPrajapati,"dbo.Split" Is not available in your database.Check user defined function list it shows or not – Mansoor May 29 '17 at 07:23
  • dbo.Split function is there in my database. I have used this in other procedures also. – Ajay Prajapati May 29 '17 at 07:36
  • @AjayPrajapati - It isn't according to the error message. Are you in the proper database context? Otherwise, try adding the database in front of the table name. i.e. `SELECT myDB.dbo.Split('002,003,004', ',') into #Temp` – SchmitzIT May 29 '17 at 07:43
  • This thread might be helpful: [Turning a multi-value parameter into a temp table](https://stackoverflow.com/questions/40534211) – snyderj May 14 '20 at 22:08

1 Answers1

2

Split is not a function is SQL Server. there is a function 'STRING_SPLIT', but only available in database if compatibility level is 130.
https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

you may create your own Split function. check following links

Split function equivalent in T-SQL?

http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/19/how-to-split-a-string-by-delimited-char-in-sql-server/

Edit

@AjayPrajapati if you already have this function defined in your database, then may be you are using it wrong. please check if Split is table valued function. if it is, then may be you are using it wrong.

SELECT dbo.Split('002,003,004', ',') into #Temp

according to your query, you are selecting a column (it is expecting value from scalar valued function), but if your function is returning table, then you need to select column from your function.

try this

SELECT * into #Temp from dbo.Split('002,003,004', ',')

h.munawar
  • 56
  • 4