Exact Table Copy

Simple scenario, just curious of the best approach. I have a table on a
Development SQL box. I need to create an exact replica of this table on
another server, including the data. I can obviously generate a SQL Script
for the creation of the table, but it has an identity field, and already
contains records. I basically want an *exact* replica of this table on the
other server. I'm curious what the best approach is.
Thanks so muchYou can do a BULK INSERT or bcp and preserve the value of the identity. See
the BOL for details.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"James" <neg@.tory.com> wrote in message
news:Ok3Ol9P4HHA.4476@.TK2MSFTNGP06.phx.gbl...
Simple scenario, just curious of the best approach. I have a table on a
Development SQL box. I need to create an exact replica of this table on
another server, including the data. I can obviously generate a SQL Script
for the creation of the table, but it has an identity field, and already
contains records. I basically want an *exact* replica of this table on the
other server. I'm curious what the best approach is.
Thanks so much|||James,
You can use IDENTITY_INSERT property on the target server to script in the
identities from the source machine. Turn it back off afterward.
RLF
"James" <neg@.tory.com> wrote in message
news:Ok3Ol9P4HHA.4476@.TK2MSFTNGP06.phx.gbl...
> Simple scenario, just curious of the best approach. I have a table on a
> Development SQL box. I need to create an exact replica of this table on
> another server, including the data. I can obviously generate a SQL Script
> for the creation of the table, but it has an identity field, and already
> contains records. I basically want an *exact* replica of this table on
> the other server. I'm curious what the best approach is.
> Thanks so much
>|||"James" <neg@.tory.com> wrote in message
news:Ok3Ol9P4HHA.4476@.TK2MSFTNGP06.phx.gbl...
> Simple scenario, just curious of the best approach. I have a table on a
> Development SQL box. I need to create an exact replica of this table on
> another server, including the data. I can obviously generate a SQL Script
> for the creation of the table, but it has an identity field, and already
> contains records. I basically want an *exact* replica of this table on
> the other server. I'm curious what the best approach is.
> Thanks so much
>
Do you mean that you want to copy the data as well? If so then you just need
to script the data with the IDENTITY values and using the IDENTITY_INSERT ON
option.
If you DON'T script the data, then the answer depends what difference it
makes to you that the current identity value for the original table might be
different to the new one. IDENTITY sequences can have gaps. So even if the
source and target tables were empty you can't be absolutely certain that the
next value to be inserted will be the same in both tables. If you want to
make future IDENTITY values in the tables start from *approximately* the
same place then you could try running the script generated by the following
query:
SELECT 'DBCC CHECKIDENT ('''
+QUOTENAME(SCHEMA_NAME(o.schema_id))+'.'+
+QUOTENAME(OBJECT_NAME(o.object_id))+''', RESEED, '
+CAST(i.last_value AS VARCHAR(10))+');'
FROM sys.identity_columns AS i
JOIN sys.objects AS o
ON i.object_id = o.object_id
WHERE i.last_value IS NOT NULL;
Result:
DBCC CHECKIDENT ('[Person].[Address]', RESEED, 32521);
DBCC CHECKIDENT ('[Production].[ProductReview]', RESEED, 4);
DBCC CHECKIDENT ('[Production].[TransactionHistory]', RESEED, 213442);
DBCC CHECKIDENT ('[Person].[AddressType]', RESEED, 6);
DBCC CHECKIDENT ('[Production].[ProductSubcategory]', RESEED, 37);
DBCC CHECKIDENT ('[dbo].[AWBuildVersion]', RESEED, 1);
DBCC CHECKIDENT ('[Production].[BillOfMaterials]', RESEED, 3482);
(SQL Server 2005 only)
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:_YqdnTwHltEcbljbRVnyvQA@.giganews.com...
> Do you mean that you want to copy the data as well?
Obviously if I had read your question properly I would have known the answer
to that. :-) Tom and Russell have given you two good answers.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks all for the replies. BCP seems like a bit of a pain for this, are
there strong reasons for using it? I'm curious why it's bad practice to use
a script to create an exact replica of the structure and then do a standard
SELECT into the destination table. I.E. I have a table on Northwind and
want to move it to a database named James. Simple table for example sake,
with ID as an idenity column and First/Last Name field. I assume there's
something fundamentally wrong with this approach?
Thanks again!!
USE [James]
GO
/****** Object: Table [dbo].[Tbl_Names] Script Date: 08/17/2007 15:43:42
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tbl_Names](
[ID] [int] IDENTITY(1,1) NOT NULL,
[First_Name] [nvarchar](100) NOT NULL,
[Last_Name] [nvarchar](100) NOT NULL,
CONSTRAINT [PK_Tbl_Names] 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
SET IDENTITY_INSERT [dbo].[Tbl_Names] ON
GO
INSERT INTO [dbo].[Tbl_Names](ID, First_Name, Last_Name) (SELECT ID,
First_Name, Last_Name FROM [Northwind].[dbo].[Tbl_Names])
GO
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:br6dnXVmEt2XaVjbnZ2dnUVZ8surnZ2d@.giganews.com...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:_YqdnTwHltEcbljbRVnyvQA@.giganews.com...
>> Do you mean that you want to copy the data as well?
> Obviously if I had read your question properly I would have known the
> answer to that. :-) Tom and Russell have given you two good answers.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||James,
Your original post said that you were duplicating the table onto another
server. That is how bcp came into the mix.
What you did below will work fine if you are on the _same_ server. You can
still get it to work across servers if you set up a linked server and make
sure that rights are correct for the link.
INSERT INTO [dbo].[Tbl_Names](ID, First_Name, Last_Name)
(SELECT ID, First_Name, Last_Name
FROM [LinkedServerName][Northwind].[dbo].[Tbl_Names])
RLF
"James" <neg@.tory.com> wrote in message
news:%23Z0gsgQ4HHA.3940@.TK2MSFTNGP05.phx.gbl...
> Thanks all for the replies. BCP seems like a bit of a pain for this, are
> there strong reasons for using it? I'm curious why it's bad practice to
> use a script to create an exact replica of the structure and then do a
> standard SELECT into the destination table. I.E. I have a table on
> Northwind and want to move it to a database named James. Simple table for
> example sake, with ID as an idenity column and First/Last Name field. I
> assume there's something fundamentally wrong with this approach?
> Thanks again!!
> USE [James]
> GO
> /****** Object: Table [dbo].[Tbl_Names] Script Date: 08/17/2007
> 15:43:42 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[Tbl_Names](
> [ID] [int] IDENTITY(1,1) NOT NULL,
> [First_Name] [nvarchar](100) NOT NULL,
> [Last_Name] [nvarchar](100) NOT NULL,
> CONSTRAINT [PK_Tbl_Names] 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
> SET IDENTITY_INSERT [dbo].[Tbl_Names] ON
> GO
> INSERT INTO [dbo].[Tbl_Names](ID, First_Name, Last_Name) (SELECT ID,
> First_Name, Last_Name FROM [Northwind].[dbo].[Tbl_Names])
> GO
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:br6dnXVmEt2XaVjbnZ2dnUVZ8surnZ2d@.giganews.com...
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:_YqdnTwHltEcbljbRVnyvQA@.giganews.com...
>> Do you mean that you want to copy the data as well?
>> Obviously if I had read your question properly I would have known the
>> answer to that. :-) Tom and Russell have given you two good answers.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>
>|||Ahh, I see what you're saying. Thanks again for clarifying. I'll give it a
run and re-post if I have any difficulty.
Thanks!
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23SXVt5Q4HHA.3916@.TK2MSFTNGP02.phx.gbl...
> James,
> Your original post said that you were duplicating the table onto another
> server. That is how bcp came into the mix.
> What you did below will work fine if you are on the _same_ server. You
> can still get it to work across servers if you set up a linked server and
> make sure that rights are correct for the link.
> INSERT INTO [dbo].[Tbl_Names](ID, First_Name, Last_Name)
> (SELECT ID, First_Name, Last_Name
> FROM [LinkedServerName][Northwind].[dbo].[Tbl_Names])
> RLF
>
> "James" <neg@.tory.com> wrote in message
> news:%23Z0gsgQ4HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Thanks all for the replies. BCP seems like a bit of a pain for this, are
>> there strong reasons for using it? I'm curious why it's bad practice to
>> use a script to create an exact replica of the structure and then do a
>> standard SELECT into the destination table. I.E. I have a table on
>> Northwind and want to move it to a database named James. Simple table
>> for example sake, with ID as an idenity column and First/Last Name field.
>> I assume there's something fundamentally wrong with this approach?
>> Thanks again!!
>> USE [James]
>> GO
>> /****** Object: Table [dbo].[Tbl_Names] Script Date: 08/17/2007
>> 15:43:42 ******/
>> SET ANSI_NULLS ON
>> GO
>> SET QUOTED_IDENTIFIER ON
>> GO
>> CREATE TABLE [dbo].[Tbl_Names](
>> [ID] [int] IDENTITY(1,1) NOT NULL,
>> [First_Name] [nvarchar](100) NOT NULL,
>> [Last_Name] [nvarchar](100) NOT NULL,
>> CONSTRAINT [PK_Tbl_Names] 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
>> SET IDENTITY_INSERT [dbo].[Tbl_Names] ON
>> GO
>> INSERT INTO [dbo].[Tbl_Names](ID, First_Name, Last_Name) (SELECT ID,
>> First_Name, Last_Name FROM [Northwind].[dbo].[Tbl_Names])
>> GO
>>
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:br6dnXVmEt2XaVjbnZ2dnUVZ8surnZ2d@.giganews.com...
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:_YqdnTwHltEcbljbRVnyvQA@.giganews.com...
>> Do you mean that you want to copy the data as well?
>> Obviously if I had read your question properly I would have known the
>> answer to that. :-) Tom and Russell have given you two good answers.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>
>>
>|||I think I understand logically what I need to do, but syntax is throwing me
a bit. The first part works, but how do I shift context to execute in the
frame of reference of the other server? USE only works for database level
as far as I can tell. Do I need to do this as a two step process? That
would be a big pain. I'm almost there, though.
declare @.bcpCommand varchar(200)
begin
set @.bcpCommand = 'bcp <sourcedb>.dbo.<sourcetable> out
c:\records.txt -T -c -S <devbox>'
exec master..xp_cmdshell @.bcpCommand
END
<how do I tell it go to shift gears here?>
DECLARE @.bulk_cmd varchar(1000)
SET @.bulk_cmd = 'BULK INSERT <cantputservernamehere>
FROM ''C:\records.txt''
WITH (ROWTERMINATOR = '''+CHAR(13)+CHAR(10)+''')'
EXEC(@.bulk_cmd)|||When you bcp out, you should specify a UNC name for the destination - i.e.
the other box. Then, you can use the local drive on the destination box.
Alternatively, you can bcp out locally and then bcp in via a UNC name.
You'll need SQL Server to be running under a domain account with read/write
privileges on the share.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"James" <neg@.tory.com> wrote in message
news:ePGzPbR4HHA.4676@.TK2MSFTNGP05.phx.gbl...
I think I understand logically what I need to do, but syntax is throwing me
a bit. The first part works, but how do I shift context to execute in the
frame of reference of the other server? USE only works for database level
as far as I can tell. Do I need to do this as a two step process? That
would be a big pain. I'm almost there, though.
declare @.bcpCommand varchar(200)
begin
set @.bcpCommand = 'bcp <sourcedb>.dbo.<sourcetable> out
c:\records.txt -T -c -S <devbox>'
exec master..xp_cmdshell @.bcpCommand
END
<how do I tell it go to shift gears here?>
DECLARE @.bulk_cmd varchar(1000)
SET @.bulk_cmd = 'BULK INSERT <cantputservernamehere>
FROM ''C:\records.txt''
WITH (ROWTERMINATOR = '''+CHAR(13)+CHAR(10)+''')'
EXEC(@.bulk_cmd)|||Thanks for your reply Tom. I did run into the path/permissions problem
previously, so I can export to a file on the target machine where
permissions aren't an issue, but in order to do the Bulk Insert I'm looking
at a two step process? What I mean is, there doesn't seem to be a way to do
all of this from within one script.
Regarding the wizard, while I'd love a wizard that just made all of the
magic happen, a cursory examination of this tool seems it's lacking for the
purposes I described, unless I'm missing something.
There don't seem to be ways to:
1.) Maintain the primary key.
2.) Maintain identity columns for tables created from scratch.
3.) Relative to my use-case, do much of anything except copy the data.
"Ellen" <DBAEllen@.community.nospam> wrote in message
news:6373B89E-9751-49F3-8284-EEB3D4AC3C88@.microsoft.com...
> You can just use the import task to import the table from one
> server/database
> to the other. When you have the table chosen edit the mappings and check
> to
> allow the identity insert.
> This is usuall quick and easy.
> ~Ellen
> --
> Thank you for sharing your knowledge
>
> "Tom Moreau" wrote:
>> When you bcp out, you should specify a UNC name for the destination -
>> i.e.
>> the other box. Then, you can use the local drive on the destination box.
>> Alternatively, you can bcp out locally and then bcp in via a UNC name.
>> You'll need SQL Server to be running under a domain account with
>> read/write
>> privileges on the share.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "James" <neg@.tory.com> wrote in message
>> news:ePGzPbR4HHA.4676@.TK2MSFTNGP05.phx.gbl...
>> I think I understand logically what I need to do, but syntax is throwing
>> me
>> a bit. The first part works, but how do I shift context to execute in
>> the
>> frame of reference of the other server? USE only works for database
>> level
>> as far as I can tell. Do I need to do this as a two step process? That
>> would be a big pain. I'm almost there, though.
>> declare @.bcpCommand varchar(200)
>> begin
>> set @.bcpCommand = 'bcp <sourcedb>.dbo.<sourcetable> out
>> c:\records.txt -T -c -S <devbox>'
>> exec master..xp_cmdshell @.bcpCommand
>> END
>> <how do I tell it go to shift gears here?>
>> DECLARE @.bulk_cmd varchar(1000)
>> SET @.bulk_cmd = 'BULK INSERT <cantputservernamehere>
>> FROM ''C:\records.txt''
>> WITH (ROWTERMINATOR = '''+CHAR(13)+CHAR(10)+''')'
>> EXEC(@.bulk_cmd)
>>|||Well, an SSIS package can help. You would create the table in one step and
then pump the data in another. There would be no intermediate file to
produce. You can still preserve the identity values from the source table.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"James" <neg@.tory.com> wrote in message
news:eWd76T14HHA.748@.TK2MSFTNGP04.phx.gbl...
Thanks for your reply Tom. I did run into the path/permissions problem
previously, so I can export to a file on the target machine where
permissions aren't an issue, but in order to do the Bulk Insert I'm looking
at a two step process? What I mean is, there doesn't seem to be a way to do
all of this from within one script.
Regarding the wizard, while I'd love a wizard that just made all of the
magic happen, a cursory examination of this tool seems it's lacking for the
purposes I described, unless I'm missing something.
There don't seem to be ways to:
1.) Maintain the primary key.
2.) Maintain identity columns for tables created from scratch.
3.) Relative to my use-case, do much of anything except copy the data.
"Ellen" <DBAEllen@.community.nospam> wrote in message
news:6373B89E-9751-49F3-8284-EEB3D4AC3C88@.microsoft.com...
> You can just use the import task to import the table from one
> server/database
> to the other. When you have the table chosen edit the mappings and check
> to
> allow the identity insert.
> This is usuall quick and easy.
> ~Ellen
> --
> Thank you for sharing your knowledge
>
> "Tom Moreau" wrote:
>> When you bcp out, you should specify a UNC name for the destination -
>> i.e.
>> the other box. Then, you can use the local drive on the destination box.
>> Alternatively, you can bcp out locally and then bcp in via a UNC name.
>> You'll need SQL Server to be running under a domain account with
>> read/write
>> privileges on the share.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "James" <neg@.tory.com> wrote in message
>> news:ePGzPbR4HHA.4676@.TK2MSFTNGP05.phx.gbl...
>> I think I understand logically what I need to do, but syntax is throwing
>> me
>> a bit. The first part works, but how do I shift context to execute in
>> the
>> frame of reference of the other server? USE only works for database
>> level
>> as far as I can tell. Do I need to do this as a two step process? That
>> would be a big pain. I'm almost there, though.
>> declare @.bcpCommand varchar(200)
>> begin
>> set @.bcpCommand = 'bcp <sourcedb>.dbo.<sourcetable> out
>> c:\records.txt -T -c -S <devbox>'
>> exec master..xp_cmdshell @.bcpCommand
>> END
>> <how do I tell it go to shift gears here?>
>> DECLARE @.bulk_cmd varchar(1000)
>> SET @.bulk_cmd = 'BULK INSERT <cantputservernamehere>
>> FROM ''C:\records.txt''
>> WITH (ROWTERMINATOR = '''+CHAR(13)+CHAR(10)+''')'
>> EXEC(@.bulk_cmd)
>>