Copying data from one table to another

Copying data from one table to another

Post by Sayye » Wed, 16 Oct 2002 05:14:28



I need to know which stored procedure or other SQl command
to use to copy existing data from one table to another in
the same SQL database.  The table to copy from will have
all the same fields as the new table.  Also the identitys
need to remain fromt the old table.

Thanks for your help

Sayyed

 
 
 

Copying data from one table to another

Post by Keith Kratochvi » Wed, 16 Oct 2002 05:24:03


You can use something like this:

SET IDENTITY_INSERT ON
GO
INSERT INTO AnotherTable (column1, column2.....)
SELECT column1, column2....
FROM OneTable
GO
SET IDENTITY_INSERT OFF
GO

--
Keith, SQL Server MVP


Quote:> I need to know which stored procedure or other SQl command
> to use to copy existing data from one table to another in
> the same SQL database.  The table to copy from will have
> all the same fields as the new table.  Also the identitys
> need to remain fromt the old table.

> Thanks for your help

> Sayyed


 
 
 

Copying data from one table to another

Post by Sayye » Wed, 16 Oct 2002 06:17:53


Is there anyway to do it without listing all the fields?
What about this BULK INSERT.  Or is that for importing
data from an external table?

>-----Original Message-----
>You can use something like this:

>SET IDENTITY_INSERT ON
>GO
>INSERT INTO AnotherTable (column1, column2.....)
>SELECT column1, column2....
>FROM OneTable
>GO
>SET IDENTITY_INSERT OFF
>GO

>--
>Keith, SQL Server MVP



>> I need to know which stored procedure or other SQl
command
>> to use to copy existing data from one table to another
in
>> the same SQL database.  The table to copy from will have
>> all the same fields as the new table.  Also the
identitys
>> need to remain fromt the old table.

>> Thanks for your help

>> Sayyed

>.

 
 
 

Copying data from one table to another

Post by Keith Kratochvi » Wed, 16 Oct 2002 06:24:11


You could try bulk insert or bcp....but insert into is probably the easiest
(most straightforward) method.

--
Keith, SQL Server MVP


> Is there anyway to do it without listing all the fields?
> What about this BULK INSERT.  Or is that for importing
> data from an external table?

> >-----Original Message-----
> >You can use something like this:

> >SET IDENTITY_INSERT ON
> >GO
> >INSERT INTO AnotherTable (column1, column2.....)
> >SELECT column1, column2....
> >FROM OneTable
> >GO
> >SET IDENTITY_INSERT OFF
> >GO

> >--
> >Keith, SQL Server MVP



> >> I need to know which stored procedure or other SQl
> command
> >> to use to copy existing data from one table to another
> in
> >> the same SQL database.  The table to copy from will have
> >> all the same fields as the new table.  Also the
> identitys
> >> need to remain fromt the old table.

> >> Thanks for your help

> >> Sayyed

> >.

 
 
 

1. Best Way To Copy Data From One Table to another in the same database

Greetings,

I have several ideas of how I would like to do this, but I thought I would
throw this one out here for the masses to comment on.

I have a production database that has a table used for logging activity
information.  This table is a highly utilized table by a run-time
application.  My goal is to off load the data out of this table into a
offline table that can be used for reporting and such.

The goal I am trying to acheive is this:
1. I want this offline table to be in the same physical database as the
production.
2. I want an efficient way to select rows from the production table then
insert those rows into the offline table and delete the copied rows out of
the production table.

I am thinking that using DTS and creating a scheduled package is the best
way to accomplish this.

Has anyone had a similar problem to solve, if so how did you find it best
handled.

Thank you in advance for any comments or help.

Bruce Wallwin
Sr. Software Developer
Rockwell Automation

2. Base "ORDER BY" in your select statement on a parameter

3. Stored Procedures - copy data from one table to another

4. N. Virginia - Oracle DBA

5. Copy data from one table to another

6. Archiving Cube Structure, but not data

7. copy data from one table to another without logging

8. Web SQL Management

9. Copy data from one table to another

10. Copying Data from one Table to Another...

11. copy data from one table to another(SQL Server) from two different machines using visual basic