sql server db: course

Showing posts with label course. Show all posts

Saturday, February 25, 2012

Copying a database

I want to copy a database, say d:\aa.mdf and d:\aa.ldf, to
the same server but with a different name. Of course, I
can't use the Copy Database Wizard. Will the following
work?
1. Detach the database.
2. Go into Win Explorer and make copies of the files, say
d:\aa1.mdf and d:\aa1.ldf.
3. Re-attach the original database using d:\aa.mdf and
d:\aa.ldf.
4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
with a different database name.
I'm just wondering if the server or system tables will get
confused for some obscure reason. Thanks for the help."GoX" <anonymous@.discussions.microsoft.com> wrote in message
news:c76f01c48a09$d840a330$a401280a@.phx.gbl...
> I want to copy a database, say d:\aa.mdf and d:\aa.ldf, to
> the same server but with a different name. Of course, I
> can't use the Copy Database Wizard. Will the following
> work?
> 1. Detach the database.
> 2. Go into Win Explorer and make copies of the files, say
> d:\aa1.mdf and d:\aa1.ldf.
> 3. Re-attach the original database using d:\aa.mdf and
> d:\aa.ldf.
> 4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
> with a different database name.
> I'm just wondering if the server or system tables will get
> confused for some obscure reason. Thanks for the help.
As long as you use a different database name for step 4, that will work
fine.
Steve|||Should work. If the db isn't too big, I suggest doing a backup and restore. The GUI work fine for just this,
as when you do restore, you type in the desired database name, and the GUI will change physical filenames
automatically.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"GoX" <anonymous@.discussions.microsoft.com> wrote in message news:c76f01c48a09$d840a330$a401280a@.phx.gbl...
> I want to copy a database, say d:\aa.mdf and d:\aa.ldf, to
> the same server but with a different name. Of course, I
> can't use the Copy Database Wizard. Will the following
> work?
> 1. Detach the database.
> 2. Go into Win Explorer and make copies of the files, say
> d:\aa1.mdf and d:\aa1.ldf.
> 3. Re-attach the original database using d:\aa.mdf and
> d:\aa.ldf.
> 4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
> with a different database name.
> I'm just wondering if the server or system tables will get
> confused for some obscure reason. Thanks for the help.|||Sounds like you have a plan. This should work just fine.
DeeJay
>--Original Message--
>I want to copy a database, say d:\aa.mdf and d:\aa.ldf,
to
>the same server but with a different name. Of course, I
>can't use the Copy Database Wizard. Will the following
>work?
>1. Detach the database.
>2. Go into Win Explorer and make copies of the files,
say
>d:\aa1.mdf and d:\aa1.ldf.
>3. Re-attach the original database using d:\aa.mdf and
>d:\aa.ldf.
>4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
>with a different database name.
>I'm just wondering if the server or system tables will
get
>confused for some obscure reason. Thanks for the help.
>.
>|||Should have thought of that. And then I don't have to
take the database off line!
>--Original Message--
>Should work. If the db isn't too big, I suggest doing a
backup and restore. The GUI work fine for just this,
>as when you do restore, you type in the desired database
name, and the GUI will change physical filenames
>automatically.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"GoX" <anonymous@.discussions.microsoft.com> wrote in
message news:c76f01c48a09$d840a330$a401280a@.phx.gbl...
>> I want to copy a database, say d:\aa.mdf and d:\aa.ldf,
to
>> the same server but with a different name. Of course, I
>> can't use the Copy Database Wizard. Will the following
>> work?
>> 1. Detach the database.
>> 2. Go into Win Explorer and make copies of the files,
say
>> d:\aa1.mdf and d:\aa1.ldf.
>> 3. Re-attach the original database using d:\aa.mdf and
>> d:\aa.ldf.
>> 4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
>> with a different database name.
>> I'm just wondering if the server or system tables will
get
>> confused for some obscure reason. Thanks for the help.
>
>.
>|||Correct. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message news:557b01c48a1b$3f0615d0$a301280a@.phx.gbl...
> Should have thought of that. And then I don't have to
> take the database off line!
> >--Original Message--
> >Should work. If the db isn't too big, I suggest doing a
> backup and restore. The GUI work fine for just this,
> >as when you do restore, you type in the desired database
> name, and the GUI will change physical filenames
> >automatically.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"GoX" <anonymous@.discussions.microsoft.com> wrote in
> message news:c76f01c48a09$d840a330$a401280a@.phx.gbl...
> >> I want to copy a database, say d:\aa.mdf and d:\aa.ldf,
> to
> >> the same server but with a different name. Of course, I
> >> can't use the Copy Database Wizard. Will the following
> >> work?
> >>
> >> 1. Detach the database.
> >> 2. Go into Win Explorer and make copies of the files,
> say
> >> d:\aa1.mdf and d:\aa1.ldf.
> >> 3. Re-attach the original database using d:\aa.mdf and
> >> d:\aa.ldf.
> >> 4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
> >> with a different database name.
> >>
> >> I'm just wondering if the server or system tables will
> get
> >> confused for some obscure reason. Thanks for the help.
> >
> >
> >.
> >

Sunday, February 19, 2012

Copy table record as new record

Hi
I want to create a copy of an existing record in the same table, except with
a different primary key value of course.
This is pretty easy if you specify each in turn like:
INSERT INTO TableA
(
Col1
Col2
...
)
SELECT Col1, Col2, ...
FROM TableA
WHERE PK = @.RecordtoCopy
However, in my situation the database structure is evolving. Therefore, if
the fields in the TableA, above, are changed then the associated SQL script
is no longer valid.
Is there some way of doing the equivalent of:
INSERT INTO TableA
SELECT * FROM TableA
WHERE PK = @.RecordtoCopy
but leaving out the primary key field, which is an identity field.
That is, I don't want to have to specify the column names each time. I
guess I could use 'sycolumns' to construct a query or is there an easier way
?
What I've shown here is a very simplistic query. Mine are much more
complex, and I want to copy all fields from several records in tables formin
g
an hierarchical structure.
Cheers
TimHi Tim
I dont imagine a situation where you need to keep duplicate copy in the same
table.
Can you explain why you want to do that so to as provide some thought on tha
t.
Regards
R.D
"Tim M" wrote:

> Hi
> I want to create a copy of an existing record in the same table, except wi
th
> a different primary key value of course.
> This is pretty easy if you specify each in turn like:
> INSERT INTO TableA
> (
> Col1
> Col2
> ...
> )
> SELECT Col1, Col2, ...
> FROM TableA
> WHERE PK = @.RecordtoCopy
> However, in my situation the database structure is evolving. Therefore, i
f
> the fields in the TableA, above, are changed then the associated SQL scrip
t
> is no longer valid.
> Is there some way of doing the equivalent of:
> INSERT INTO TableA
> SELECT * FROM TableA
> WHERE PK = @.RecordtoCopy
> but leaving out the primary key field, which is an identity field.
> That is, I don't want to have to specify the column names each time. I
> guess I could use 'sycolumns' to construct a query or is there an easier w
ay?
> What I've shown here is a very simplistic query. Mine are much more
> complex, and I want to copy all fields from several records in tables form
ing
> an hierarchical structure.
> Cheers
> Tim
>|||This is for a cost estimating system where you want to create a new version
of an existing estimate. This would happen when you want to be on a new job
,
which is very similar to one that was previously costed and you want to use
the old one as a template for a new one.
I think I've solved it anyway. The code below is a function that creates an
insert clause as a string.
CREATE FUNCTION dbo.fn_CreateInsert (@.@.Table varchar(50), @.@.PKey
varchar(20)) RETURNS varchar(1000) AS
BEGIN
DECLARE @.InsertClause varchar(1000),
@.@.TableName varchar(255),
@.ColName varchar(255),
@.ColsCursor CURSOR
SET @.ColsCursor = CURSOR FAST_FORWARD
FOR
SELECT sysobjects.name AS TableName, syscolumns.name AS ColumnName
FROM syscolumns
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
INNER JOIN (sysobjects INNER JOIN sysusers ON sysobjects.uid =
sysusers.uid)
ON syscolumns.id = sysobjects.id
WHERE sysobjects.name = @.@.Table
OPEN @.ColsCursor
FETCH NEXT FROM @.ColsCursor
INTO @.@.TableName, @.ColName
SET @.InsertClause = ''
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.ColName NOT IN (@.@.PKey, 'DateCreated', 'DateModified')
BEGIN
IF @.InsertClause = ''
SET @.InsertClause = '(' + @.ColName
ELSE
SET @.InsertClause = @.InsertClause + ', ' + @.ColName
END
FETCH NEXT FROM @.ColsCursor
INTO @.@.TableName,@.ColName
END
CLOSE @.ColsCursor
DEALLOCATE @.ColsCursor
SET @.InsertClause = 'INSERT INTO ' + @.@.Table + ' ' + @.InsertClause + ')'
RETURN @.InsertClause
END
GO
"R.D" wrote:
> Hi Tim
> I dont imagine a situation where you need to keep duplicate copy in the sa
me
> table.
> Can you explain why you want to do that so to as provide some thought on t
hat.
> Regards
> R.D
> "Tim M" wrote:
>|||Tim, try,
-- DDL & sample data
create table dbo.t1
(
keycol int not null identity primary key,
col1 int,
col2 int,
col3 int
);
insert into dbo.t1 values(1, 2, 3);
go
-- usp_copyt1row proc
create proc usp_copyt1row
@.keyval as int
as
declare @.col_list as nvarchar(1000), @.sql as nvarchar(4000);
set @.col_list = N'';
select @.col_list = @.col_list + ',[' + column_name + N']'
from information_schema.columns
where table_schema = N'dbo'
and table_name = N't1'
and column_name <> N'keycol';
set @.col_list = right(@.col_list, len(@.col_list) - 1);
set @.sql =
N'insert into dbo.t1('
+ @.col_list + N') select '
+ @.col_list + N' from dbo.t1 where keycol = @.keyval'
exec sp_executesql
@.sql, N'@.keyval as int', @.keyval;
go
-- test
exec usp_copyt1row 1;
exec usp_copyt1row 2;
select * from dbo.t1;
-- Output
keycol col1 col2 col3
-- -- -- --
1 1 2 3
2 1 2 3
3 1 2 3
BG, SQL Server MVP
www.SolidQualityLearning.com
"Tim M" wrote:

> Hi
> I want to create a copy of an existing record in the same table, except wi
th
> a different primary key value of course.
> This is pretty easy if you specify each in turn like:
> INSERT INTO TableA
> (
> Col1
> Col2
> ...
> )
> SELECT Col1, Col2, ...
> FROM TableA
> WHERE PK = @.RecordtoCopy
> However, in my situation the database structure is evolving. Therefore, i
f
> the fields in the TableA, above, are changed then the associated SQL scrip
t
> is no longer valid.
> Is there some way of doing the equivalent of:
> INSERT INTO TableA
> SELECT * FROM TableA
> WHERE PK = @.RecordtoCopy
> but leaving out the primary key field, which is an identity field.
> That is, I don't want to have to specify the column names each time. I
> guess I could use 'sycolumns' to construct a query or is there an easier w
ay?
> What I've shown here is a very simplistic query. Mine are much more
> complex, and I want to copy all fields from several records in tables form
ing
> an hierarchical structure.
> Cheers
> Tim
>|||Thanks for that.
"Itzik Ben-Gan" wrote:
> Tim, try,
> -- DDL & sample data
> create table dbo.t1
> (
> keycol int not null identity primary key,
> col1 int,
> col2 int,
> col3 int
> );
> insert into dbo.t1 values(1, 2, 3);
> go
> -- usp_copyt1row proc
> create proc usp_copyt1row
> @.keyval as int
> as
> declare @.col_list as nvarchar(1000), @.sql as nvarchar(4000);
> set @.col_list = N'';
> select @.col_list = @.col_list + ',[' + column_name + N']'
> from information_schema.columns
> where table_schema = N'dbo'
> and table_name = N't1'
> and column_name <> N'keycol';
> set @.col_list = right(@.col_list, len(@.col_list) - 1);
> set @.sql =
> N'insert into dbo.t1('
> + @.col_list + N') select '
> + @.col_list + N' from dbo.t1 where keycol = @.keyval'
> exec sp_executesql
> @.sql, N'@.keyval as int', @.keyval;
> go
> -- test
> exec usp_copyt1row 1;
> exec usp_copyt1row 2;
> select * from dbo.t1;
> -- Output
> keycol col1 col2 col3
> -- -- -- --
> 1 1 2 3
> 2 1 2 3
> 3 1 2 3
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Tim M" wrote:
>

Saturday, February 25, 2012

Copying a database

Copying a database

Copying a database

Sunday, February 19, 2012

Copy table record as new record

sql server db

Blog Archive

About Me