What's wrong with this statement?

[blakemckenna]

I'm trying to select data from a variable into a variable defined as table. I'm not sure what's wrong with this statement. Please see attached image.

Thanks,

[techgnome]

In doesn't work like that... It's expecting a list of things. A table isn't a list. But what you can do is this:

and (A.MainID In (select mainID from @tbl))

-tg

[blakemckenna]

Ok,

I've changed things around a bit. What I'm trying to do now is SPLIT the @MainID variable. This string variable contains 6 digit numbers in the format of 123456, 234567, 456789, etc. The STRING_SPLIT function does exist, however, I know that I'm using it wrong. It's just for illustration for what I'm wanting to accomplish.

Thanks,

[szlamany]

I only see one closing parenthesis after the STRING_SPLIT - see if that fixes it.

If not try to put the results of the STRING_SPLIT into a variable - and use that simple variable in the VALUES() list. I kind of remember that VALUES() lists have to remain somewhat simple...

Or just flip it to be: Insert into @Tbl Select String_Split(...)

[techgnome]

Did you check the documentation on string_split? https://docs.microsoft.com/en-us/sql...t-transact-sql
Specifically the note where it states:

The STRING_SPLIT function is available only under compatibility level 130. If your database compatibility level is lower than 130, SQL Server will not be able to find and execute STRING_SPLIT function. You can change a compatibility level of database using the following command:
ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130 +
Note that compatibility level 120 might be default even in new Azure SQL Databases.

That's only going to work if you can change the compatibility mode of the database, which may or may not be a good thing to do.

-tg