August 8, 2018 at 5:24 pm
Hi all,
So I'm transferring from an access backend to SQL server. The first thing I've done is to prepare the table in SQL with primary keys and identities, etc. like so
USE [DatabaseName]
GO
/****** Object: Table [dbo].[Tbl_TableName] Script Date: 08/03/2018 11:22:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tbl_TableName](
[ID] [int] IDENTITY(9001,1) NOT NULL, --(1,1) = Increment Seed, Increment Value
[FeildName2] [nvarchar](255) NULL,
[FieldName2] [nvarchar](255) NULL,
CONSTRAINT [PK_Tbl_TableName] PRIMARY KEY CLUSTERED
(
[ID] 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
This works to create the table I need. So now I need to insert the old table data into this new table.
Important: the ID's must remain the same and match! That's super important, because these values are used in various joins and queries. The easy part to this would be that I always know what the starting ID will be, and what the ending ID will be. the problem is that during the life of the access table, various records have been deleted, so there will be missing ID numbers. Also, I can't install 3rd party software to do this job for me. It's all manual.
Any help appreciated! 🙂
August 8, 2018 at 8:51 pm
Drop Table If Exists #Test-- Restore data
Create Table #Test
(
Id Int Identity(1,1) Primary Key,
Field1 nvarchar(255),
Field2 nvarchar(255)
)
Set Identity_Insert #Test On
Insert Into #Test(Id, Field1, Field2)
Values(1, N'A', N'B'),(2, N'C', N'D'),(13, N'E', N'F'),(21, N'G', N'H')
Set Identity_Insert #Test Off
Go
-- Reset the identity value so auto-incrementing works
Dbcc CheckIdent(#Test)
Go
Insert Into #Test(Field1,Field2) Values('X','Y')
Select * From #Test
August 9, 2018 at 7:37 am
Is this a 1 time thing or does it need to be repeated in each environment? You can use the import wizard in SSMS to point to the source and target. In the mapping, you can specify to enable identity insert. This creates an ssis package that you can save and rerun. The only issue is the connections would be hardcoded in the package. Those can then be modified to be dynamic with parameters, so the solution could be run on any environment with any source/target to move through the environments.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply