July 27, 2017 at 7:28 am
I have a small table in Dev (contains 38 rows) and I ran an update statement but forgot the where clause. Now I am trying to export the table from prod to dev and running into some issues. What's the best way to handle this issue?
DDL of a table of tableCREATE TABLE DataSource](
[DataSourceKey] [int] IDENTITY(1,1) NOT NULL,
[DataSourceCode] [varchar](10) NOT NULL,
[DataSourceDesc] [varchar](50) NOT NULL,
CONSTRAINT [PK_DataSource] PRIMARY KEY CLUSTERED
(
[DataSourceKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
When I run the package, I run into PK issue. so I tried truncating the table but it is referenced by a FK. What's the best way to resolve this issue?
SET ANSI_PADDING OFF
GO
July 27, 2017 at 7:37 am
newdba2017 - Thursday, July 27, 2017 7:28 AMI have a small table in Dev (contains 38 rows) and I ran an update statement but forgot the where clause. Now I am trying to export the table from prod to dev and running into some issues. What's the best way to handle this issue?DDL of a table of table
CREATE TABLE DataSource](
[DataSourceKey] [int] IDENTITY(1,1) NOT NULL,
[DataSourceCode] [varchar](10) NOT NULL,
[DataSourceDesc] [varchar](50) NOT NULL,
CONSTRAINT [PK_DataSource] PRIMARY KEY CLUSTERED
(
[DataSourceKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GO
When I run the package, I run into PK issue. so I tried truncating the table but it is referenced by a FK. What's the best way to resolve this issue?
SET ANSI_PADDING OFF
GO
Did you update Code and Description columns? Are the Keys the same in prod and dev?
With SSIS, you can use a SQL Command in your destination instead of the table. That way you update row by row to get the values from production without deleting them.
July 27, 2017 at 7:45 am
Luis Cazares - Thursday, July 27, 2017 7:37 AMnewdba2017 - Thursday, July 27, 2017 7:28 AMI have a small table in Dev (contains 38 rows) and I ran an update statement but forgot the where clause. Now I am trying to export the table from prod to dev and running into some issues. What's the best way to handle this issue?DDL of a table of table
CREATE TABLE DataSource](
[DataSourceKey] [int] IDENTITY(1,1) NOT NULL,
[DataSourceCode] [varchar](10) NOT NULL,
[DataSourceDesc] [varchar](50) NOT NULL,
CONSTRAINT [PK_DataSource] PRIMARY KEY CLUSTERED
(
[DataSourceKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GO
When I run the package, I run into PK issue. so I tried truncating the table but it is referenced by a FK. What's the best way to resolve this issue?
SET ANSI_PADDING OFF
GODid you update Code and Description columns? Are the Keys the same in prod and dev?
With SSIS, you can use a SQL Command in your destination instead of the table. That way you update row by row to get the values from production without deleting them.
I only updated DataSourceDesc column. Keys in Prod and Dev are same.
July 27, 2017 at 7:54 am
.. When I run the package, I run into PK issue. so I tried truncating the table but it is referenced by a FK. What's the best way to resolve this issue? ..
If you're using the Import wizzard to create your one-off SSIS process (recommended for this scenario you're describing), then edit column mappings and enable the "identity insert" option. What this will do is set the identity column in dev with the same values that are coming from the source table in prod. Otherwise, your ID column will generate new sequential values for each inserted row, which is probably not what you want, because it will break foreign key relationships with other existing tables in development.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 27, 2017 at 8:00 am
Since this was a small table, I just used Excel to build the script to update all the columns so it's all good. Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply