copying table data from 1 dbase to another

HI just had a question on this. I was able to copy the table using the
script, create in query analizer but am not quite sure how to copy the data.
I tried the (script object to window as Select) for the source data, and then
switched to the destination dbase and table and selected script object to new
window insert. For the insert I get the error though, Incorrect syntax near
'<'.,line 3, also not quite sure if this is the correct method to use.
this is the insert code created automatically that does not compile correctly.
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
VALUES(<Arrive_Depart_ID,int,>,
<Arrive_Depart_VC,varchar(50),>)
Paul G
Software engineer.
If the table is already on the OTHER database try
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from origtableinlocaldatabase
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),>)
> --
> Paul G
> Software engineer.
|||You can use DTS to move tables and data between servers.
Since you already have the table created you could use DTS to move the data
OR
you could BCP / BULK COPY the data out of one server and BCP or BULK COPY it
in to the other server
OR
you could use a linked server or openrowset to select (and insert) the data
something like this (on the server that you are trying to populate)
INSERT INTO TheDatabase.TheOwner.TheTable (ColumnName,
AnotherColumnName...)
SELECT A.ColumnName, A.AnotherColumnName...
FROM LinkedServerName.TheDatabase.TheOwner.TheTable A
/*this next step is not needed if the destination table is truly empty*/
WHERE NOT EXISTS (SELECT * FROM TheDatabase.TheOwner.TheTable B WHERE
A.ThePrimaryKeyColumn = B.ThePrimaryKayColumn)
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),>)
> --
> Paul G
> Software engineer.
|||Several methods would work, in your example you are missing the SELECT
statement (see BOL INSERT statement for examples). If the column names are
the same on source and target, your SQL should look something like:
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
SELECT * FROM SourceTable
Steve
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),>)
|||Hi thanks for the response, tried this,-DMLinter is the destination dbase
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
but get the error,
Cannot insert explicit value for identity column in table
'DML$Arrive_Depart_T' when IDENTITY_INSERT is set to OFF.
just wondering if you know how to set identity_insert to on?
"Wayne Snyder" wrote:

> If the table is already on the OTHER database try
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> Select [Arrive_Depart_ID], [Arrive_Depart_VC] from origtableinlocaldatabase
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> data.
> then
> new
> near
> correctly.
>
>
|||The following seemed to work, both dbases are on the same server.
SET IDENTITY_INSERT [DMLinter].[dbo].DML$Arrive_Depart_T ON
--IDENTITY_INSERT = ON
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
Have not played around with DTS yet but seems pretty useful.
thanks.
"Keith Kratochvil" wrote:

> You can use DTS to move tables and data between servers.
> Since you already have the table created you could use DTS to move the data
> OR
> you could BCP / BULK COPY the data out of one server and BCP or BULK COPY it
> in to the other server
> OR
> you could use a linked server or openrowset to select (and insert) the data
> something like this (on the server that you are trying to populate)
> INSERT INTO TheDatabase.TheOwner.TheTable (ColumnName,
> AnotherColumnName...)
> SELECT A.ColumnName, A.AnotherColumnName...
> FROM LinkedServerName.TheDatabase.TheOwner.TheTable A
> /*this next step is not needed if the destination table is truly empty*/
> WHERE NOT EXISTS (SELECT * FROM TheDatabase.TheOwner.TheTable B WHERE
> A.ThePrimaryKeyColumn = B.ThePrimaryKayColumn)
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> data.
> then
> new
> near
> correctly.
>
|||This below seems to work, had to set the Identity_insert to on.
SET IDENTITY_INSERT [DMLinter].[dbo].DML$Arrive_Depart_T ON
--IDENTITY_INSERT = ON
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
"Steve Thompson" wrote:

> Several methods would work, in your example you are missing the SELECT
> statement (see BOL INSERT statement for examples). If the column names are
> the same on source and target, your SQL should look something like:
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> SELECT * FROM SourceTable
> Steve
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> data.
> then
> new
> near
> correctly.
>
>