Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL SELECT INTO

24 August,2010 by Jack Vamvas

A regular question is how to transfer data from one table to another efficiently.

I'm including an example of one method. But as with all approaches - ensure you have relevant SQL Server BACKUP and RESTORE  for easy rollback.

Keep in mind - the structure of the new table is defined by the SELECT statements expression attributes . One of the consequences is that you will need to transfer indices separately

 

--create new table and insert dataset

select * INTO Links_temp from Links where links_id NOT IN ('2E28BF11CFBC','B0566C4F2758')

--check count dataset  and whatever sanity checks you require

select count(*) from Links_temp

--check new resultset

select * from Links_temp

--check and compare column definition

SELECT column_name,

data_type ,

CHARacter_maximum_length

FROM information_schema.columns

WHERE table_name = 'Links_temp'

 

--rename existing Linkstable to Links240810

sp_rename '[Links]','[Links240810]'

--rename Links_temp to Links

sp_rename 'Links_temp','Links'

 

--oops!indexes didn't transfer - which they don't on a SELECT INTO

 

CREATE UNIQUE CLUSTERED INDEX [I_Links10] ON [dbo].[Links]

(

[links_id] ASC,

[name] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]






CREATE NONCLUSTERED INDEX [I_Links11] ON [dbo].[Links]

(

[head_id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]





GO

CREATE NONCLUSTERED INDEX [I_Links12] ON [dbo].[Links]

(

[version_series_id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]





ALTER TABLE [dbo].[Links] ADD PRIMARY KEY NONCLUSTERED

(

[object_id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  =

ON) ON [PRIMARY]
Author: Jack Vamvas (http://www.sqlserver-dba.com)

Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer