SQL Server 2019: Issues with SELECT INTO on Partitioned Tables Causing Unexpected NULLs
I'm dealing with I'm trying to configure I am encountering an issue when using the `SELECT INTO` statement to create a new table from an existing partitioned table in SQL Server 2019....... The original table has several partitions based on a date column, and I'm trying to create a new table that only contains records from a specific partition. Here’s the SQL query I’m running: ```sql SELECT * INTO NewTable FROM OriginalTable WHERE PartitionDate = '2023-01-01'; ``` However, some of the columns in `NewTable` end up with NULL values, even though I can confirm they have valid data in the relevant partition of `OriginalTable`. I’ve double-checked the data types and constraints, and they seem to match. I've also tried explicitly specifying the columns instead of using `*`: ```sql SELECT Column1, Column2, Column3 INTO NewTable FROM OriginalTable WHERE PartitionDate = '2023-01-01'; ``` I'm still facing issues with `Column3` being NULL in the resulting `NewTable`. I suspect this might be related to the way SQL Server handles partitioned tables with the `SELECT INTO` command. Additionally, I’ve verified that no triggers or constraints are affecting the data during the insertion, as I ran the same query on a non-partitioned test table without any issues. When I try running a similar query with `INSERT INTO` instead of `SELECT INTO`, the NULLs do not appear. Here's an example of that: ```sql INSERT INTO NewTestTable (Column1, Column2, Column3) SELECT Column1, Column2, Column3 FROM OriginalTable WHERE PartitionDate = '2023-01-01'; ``` In that case, all values are copied correctly. Is there a known limitation or a specific configuration I need to check regarding using `SELECT INTO` with partitioned tables? Any insights or workarounds would be greatly appreciated! This is happening in both development and production on Ubuntu 20.04. I'm developing on Ubuntu 20.04 with Sql. I'm on macOS using the latest version of Sql. I'm coming from a different tech stack and learning Sql. Any ideas what could be causing this?