Danny
10/27/2021, 5:57 PM--create-only
so I can edit it to copy the data to the new column, but I'm getting an error when trying to execute the file during the actual migration itselfDanny
10/27/2021, 5:59 PM/*
Warnings:
- You are about to drop the column `testName` on the `Account` table. All the data in the column will be lost.
- Added the required column `personName` to the `Account` table without a default value. This is not possible if the table is not empty.
*/
BEGIN TRY
BEGIN TRAN;
-- Remove testName column
ALTER TABLE [dbo].[Account] DROP COLUMN [testName];
-- Add personName column
ALTER TABLE [dbo].[Account] ADD [personName] NVARCHAR(1000) NOT NULL;
COMMIT TRAN;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN;
END;
THROW
END CATCH
Danny
10/27/2021, 5:59 PM/*
Warnings:
- You are about to drop the column `testName` on the `Account` table. All the data in the column will be lost.
- Added the required column `personName` to the `Account` table without a default value. This is not possible if the table is not empty.
*/
BEGIN TRY
BEGIN TRAN;
-- Add personName column
ALTER TABLE [dbo].[Account] ADD [personName] NVARCHAR(1000) NOT NULL;
-- Move data from testName to personName
UPDATE [dbo].[Account] SET [personName] = [testName];
-- Remove testName column
ALTER TABLE [dbo].[Account] DROP COLUMN [testName];
COMMIT TRAN;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN;
END;
THROW
END CATCH
Danny
10/27/2021, 6:00 PMError: P3006
Migration `20211027171057_account_testname_to_personname` failed to apply cleanly to the shadow database.
Error:
Invalid column name 'personName'.
0: sql_migration_connector::validate_migrations
at migration-engine/connectors/sql-migration-connector/src/lib.rs:342
1: migration_core::api::DevDiagnostic
at migration-engine/core/src/api.rs:108
Danny
10/27/2021, 6:16 PMGO
to split the script into batches (since the new column is not yet accessible in the current batch of statements). But I don't have access to GO
in the .sql
migrations ( Incorrect syntax near 'GO'
) since:
The GO command is frequently used inside T-SQL code to use batches. It is not a T-SQL statement, but it is a command recognized by native SQL Server tools like the SSMS, the SQLCMD, and OSQL (SQLCMD is the command-line to handle SQL Server and osql is an old version of SQLCMD that may be removed someday).So I'm thinking I would need to do something else here? Do I need to do two migrations similar to the expand/contract pattern described in the Prisma docs? 1. First migration: Add the column, make it optional/nullable 2. Second migration: Copy data over from old column to new, drop the old column
Danny
10/27/2021, 6:39 PMRyan
10/28/2021, 5:07 AMalter table
command.
1. Create a migration renaming the field in schema.prisma
using prisma migrate dev --create-only
2. Replace the contents of the .sql
file with this: alter table tale_name rename column column_name to new_column_name
3. Run prisma migrate dev
Danny
11/01/2021, 7:30 PMsp_rename
stored proc right? https://docs.microsoft.com/en-us/sql/relational-databases/tables/rename-columns-database-engine?view=sql-server-ver15