Copy Data from one table to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Copy Data from one table to another

Author

Topic

badpig521
Starting Member

14 Posts

Posted - 2008-06-02 : 12:02:07

I need to move data from a large table (350GB/395,000 rows) to another table within the DB, but onto another disk. The table has an image data type so I have to create a new table onto the new disk. My original table looks like this:

USE [PD51_Data]
GO
/****** Object: Table [dbo].[SCANNEDDOCUMENTS] Script Date: 05/27/2008 11:26:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SCANNEDDOCUMENTS](
[DocID] [int] IDENTITY(1,1) NOT NULL,
[CaseID] [int] NOT NULL,
[DocName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Doc] [image] NOT NULL,
[DocLocation] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DocNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TopicID] [int] NULL,
[ScannedDocumentsCheckSum] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
PRIMARY KEY CLUSTERED
(
[DocID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SCANNEDDOCUMENTS] WITH NOCHECK ADD CONSTRAINT [ISCANNEDDOCUMENTS2] FOREIGN KEY([TopicID])
REFERENCES [dbo].[TOPICS] ([TopicID])
GO
ALTER TABLE [dbo].[SCANNEDDOCUMENTS] CHECK CONSTRAINT [ISCANNEDDOCUMENTS]

My new table looks like this:
USE [PD51_Data]
GO
/****** Object: Table [dbo].[SCANNEDDOCUMENTS2] Script Date: 05/27/2008 11:27:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SCANNEDDOCUMENTS2](
[DocID] [int] IDENTITY(1,1) NOT NULL,
[CaseID] [int] NOT NULL,
[DocName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Doc] [image] NOT NULL,
[DocLocation] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DocNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TopicID] [int] NULL,
[ScannedDocumentsCheckSum] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [SECONDARY] TEXTIMAGE_ON [SECONDARY]

GO
SET ANSI_PADDING OFF

My plan was to use import/export wizard to move the data over to the new table, while set to Bulk Log recovery, drop the original table, then create the indexes/constraints on the new one.
With this much data, I'm wondering what else I should do.
Anyone have a better idea? What am I missing?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-06-02 : 12:11:37

note that text and image types are deprecated in 2005. use varchar(max) and varbinary(max) instead.

elsasoft.org

badpig521
Starting Member

14 Posts

Posted - 2008-06-02 : 12:37:37

quote:
Originally posted by jezemine

note that text and image types are deprecated in 2005. use varchar(max) and varbinary(max) instead.

elsasoft.org

Use it where?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 12:41:11

quote:
Originally posted by badpig521

quote:
Originally posted by jezemine

note that text and image types are deprecated in 2005. use varchar(max) and varbinary(max) instead.

elsasoft.org

Use it where?

instead of your text,ntext & image fields use them

badpig521
Starting Member

14 Posts

Posted - 2008-06-02 : 12:43:24

quote:
Originally posted by visakh16

quote:
Originally posted by badpig521

quote:
Originally posted by jezemine

note that text and image types are deprecated in 2005. use varchar(max) and varbinary(max) instead.

elsasoft.org

Use it where?

instead of your text,ntext & image fields use them

Oh you mean in the new table?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-06-02 : 12:53:07

yes, i meant in the new table, and in any new development on 2005 you happen to do.

elsasoft.org

badpig521
Starting Member

14 Posts

Posted - 2008-06-02 : 13:02:40

quote:
Originally posted by jezemine

yes, i meant in the new table, and in any new development on 2005 you happen to do.

elsasoft.org

I understand. The DB was originally created and still set @ Compatibility level 80 (2000)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 15:19:55

quote:
Originally posted by badpig521

quote:
Originally posted by jezemine

yes, i meant in the new table, and in any new development on 2005 you happen to do.

elsasoft.org

I understand. The DB was originally created and still set @ Compatibility level 80 (2000)

You need to change compatibility level to 90 before using 2005 specific features. Use sp_dbcmptlevel to change compatability level of your db.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources