July 10, 2008 at 1:52 pm
I am trying to backup a table to a different database on the same server. Using "Select Into" works great but does not get everything. I looked at the sys views to see if I could find something like the text in the syscomments table. But no luck. This has to be done inside a stored procedure. Any help would be appreciated.
Thanks
Dave
July 10, 2008 at 11:48 pm
Hi
The SELECT INTO statement creates a new table and populates it with the result set of the SELECT statement. SELECT INTO can be used to combine data from several tables or views into one table.
The structure of the new table is defined by the attributes of the expressions in the select list.
July 10, 2008 at 11:57 pm
Since the format of new_table is determined by evaluating the expressions in the select list, I am not sure Indexes will be maintained in new_table.
You need to create the indexes for new_table.
July 11, 2008 at 12:19 am
Select into just creates a new table matching the number and types of the columns in the select statement.
It does not create constraints, indexes, defaults or anything else other than the table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2008 at 12:25 am
You'll have to use DDL to create the table, constraints, etc...
Most of it is easily generated by right clicking the source table - Script table as - Create to...
Then use INSERT INTO to load the destination table.
something like this:
...
USE [destDatabase]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[destTable]') AND type in (N'U'))
DROP TABLE [dbo].[destTable]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[destTable](
[ID] [int] NOT NULL,
[Name] [varchar](100) NULL,
[SomeCode] [char](2) NULL,
[AnotherCode] [char](3) NULL,
[State] [char](2) NULL,
[County] [varchar](100) NULL,
[DOBRequired] [bit] NOT NULL CONSTRAINT [DF_destTable_DOBRequired] DEFAULT ((0)),
[Billable] [bit] NOT NULL CONSTRAINT [DF_destTable_Billable] DEFAULT ((0)),
[SomeFee] [money] NULL CONSTRAINT [DF_destTable_SomeFee] DEFAULT ((0))
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_destTable_ID] ON [dbo].[destTable]
(
[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]
CREATE NONCLUSTERED INDEX [IX_destTable_AnotherCode] ON [dbo].[destTable]
(
[AnotherCode] 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 [IX_destTable_SomeCode] ON [dbo].[destTable]
(
[SomeCode] 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]
...
Insert INTO destTable
Select * from srcDatabase.dbo.srcTable
USE [sourceDatabase]
...
--continue with your sp code
If you don't like that in the middle of your beautiful code, you can put it in a separate sp like spCreate_Dest_Table. Then exec that instead of your INSERT INTO statement.
Hope that helps.
July 11, 2008 at 9:00 am
The problem is the new table is going to dynamic. I should have explained better in the OP.
I am making a stored procedure that will backup procedures, views, functions and tables. Just pass in the database name, name of the object and type and it will be copied to a backup database. This way if one of the developers needs to make a change, they backup the table first, then make the update. If something goes wrong, I can easily restore it. The procedures, views and functions were easy. I just pulled the create statement out of the syscomments table and copied it to a new table in my backup database. The problem is I cant find where to get the same create script in a sys table. So I was using the Select Into. But it does not copy everything about the table, just the structure and the data. Hope that makes more sense.
Thanks for the above post. Any help is greatly appreciated.
Dave
July 14, 2008 at 12:30 am
Have you looked at database snapshots? It seems to me that creating a snapshot may solve your problem.
They do have an IO load impact though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2008 at 12:42 am
or what about a simple database backup. If your developers are going to make a lot of changes or database size is huge this may not work out.
"Keep Trying"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply