Copy the records from a table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Copy the records from a table

Author

Topic

Kalpa
Starting Member

9 Posts

Posted - 2008-07-29 : 04:59:06

Hi

I need to write a query that will copy the entire records from a table e.g(BatchProcessDaily)and insert these values to another table e.g(BatchProcessHistoryTable)and simultaneously it will clear the data from the previous table.So can u please provide the way how to implement it with an example.

Kalpa
Starting Member

9 Posts

Posted - 2008-07-29 : 05:24:33

Can u please give a sample querry???

kpkirilov
Starting Member

10 Posts

Posted - 2008-07-29 : 05:26:23

Use DELETE AFTER TRIGGER. In the body of trigger have access to temporary table DELETED (deleted records).

In addition, you can use DELETE FROM ... OUTPUT INTO. But there are quite limited-to avoid use them this time with the local temporarily table.

Kalpa
Starting Member

9 Posts

Posted - 2008-07-29 : 05:35:53

Thanks for the above information but i need the query that will copy the entire records from a table e.g(BatchProcessDaily)and insert these values to another table e.g(BatchProcessHistoryTable)
Thanking you

chetanb3
Yak Posting Veteran

52 Posts

Posted - 2008-07-29 : 05:43:39

try this
INSERT INTO BatchProcessHistoryTable
SELECT * FROM BatchProcessDaily

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-29 : 05:48:25

Hi,
I think this will help u..

if object_id('BatchProcessHistoryTable') is not null
drop table BatchProcessHistoryTable
go
SELECT * into BatchProcessHistoryTable FROM BatchProcessDaily

--and chk BatchProcessHistoryTable by
select * from BatchProcessHistoryTable

Kalpa
Starting Member

9 Posts

Posted - 2008-07-29 : 05:55:59

When i am executing this Query it is throwing the error message
"Cannot insert explicit value for identity column in table 'BatchProcessHistory' when IDENTITY_INSERT is set to OFF."
In Both the tables all the columns are same.What might be the problem?

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-29 : 06:00:43

Is my query returns the error message..?

Kalpa
Starting Member

9 Posts

Posted - 2008-07-29 : 06:13:36

no....not ur query..

But when i am trying "Insert into BatchProcessHistory Select * From BatchProcessDaily "this query.Because first i need to insert the records from BatchProcessDaily to BatchProcessHistory.then after i have to remove only the values.

kpkirilov
Starting Member

10 Posts

Posted - 2008-07-29 : 06:16:13

Hi Kalpa

Use DELETE INSTEAD OF TRIGGER on the table BatchProcessDaily.
Trigger code is as follows:

INSERT INTO BatchProcessHistoryTable () FROM
SELECT * FROM Deleted;

DELETE FROM BatchProcessDaily
WHERE BatchProcessDaily.Id IN (SELECT Id FROM Deleted)

Run clause DELETE FROM BatchProcessDaily and will add deleted from the records in BatchProcessDaily into BatchProcessHistoryTable.

ut is good to use AFTER TRIGGER.
Trigger code is as follows:

INSERT INTO BatchProcessHistoryTable () FROM
SELECT * FROM Deleted;

Kalpa
Starting Member

9 Posts

Posted - 2008-07-29 : 06:17:00

i donot have to drop the table only i need to delete the records.

chetanb3
Yak Posting Veteran

52 Posts

Posted - 2008-07-29 : 09:18:57

set IDENTITY_INSERT ON

INSERT INTO BatchProcessHistoryTable
SELECT * FROM BatchProcessDaily

set IDENTITY_INSERT OFF
in that case your table may have dulicate Identity values.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-29 : 09:55:01

quote:
Originally posted by kpkirilov

Use DELETE AFTER TRIGGER. In the body of trigger have access to temporary table DELETED (deleted records).

In addition, you can use DELETE FROM ... OUTPUT INTO. But there are quite limited-to avoid use them this time with the local temporarily table.

is this a joke?

kpkirilov
Starting Member

10 Posts

Posted - 2008-07-29 : 10:25:12

quote:
Originally posted by rohitkumar

quote:
Originally posted by kpkirilov

Use DELETE AFTER TRIGGER. In the body of trigger have access to temporary table DELETED (deleted records).

In addition, you can use DELETE FROM ... OUTPUT INTO. But there are quite limited-to avoid use them this time with the local temporarily table.

is this a joke?

<OUTPUT_CLAUSE> ::=
{
[ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ ( column_list ) ] ]
[ OUTPUT <dml_select_list> ]
}
<dml_select_list> ::=
{ <column_name> | scalar_expression } [ [AS] column_alias_identifier ]
[ ,...n ]

<column_name> ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }

output_table

Specifies a table that the returned rows are inserted into instead of being returned to the caller. output_table may be a temporary table.

If column_list is not specified, the table must have the same number of columns as the OUTPUT result set. The exceptions are identity and computed columns. These must be skipped. If column_list is specified, any omitted columns must either allow null values or have default values assigned to them.

output_table cannot:

Have enabled triggers defined on it.

Participate on either side of a foreign key constraint.

Have CHECK constraints or enabled rules.

USE AdventureWorks
GO
DECLARE @MyTableVar table (
ProductID int NOT NULL,
ProductName nvarchar(50)NOT NULL,
ProductModelID int NOT NULL,
PhotoID int NOT NULL);

DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
p.Name,
p.ProductModelID,
DELETED.ProductPhotoID
INTO @MyTableVar
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p
ON ph.ProductID = p.ProductID
WHERE p.ProductModelID BETWEEN 120 and 130;

--Display the results of the table variable.
SELECT ProductID, ProductName, ProductModelID, PhotoID
FROM @MyTableVar
ORDER BY ProductModelID;
GO

I prefer temporary tables (SELECT * INTO # TempTable FROM Deleted) instead tabular variables. Have advantages (the code is more iniversalen) and are fast because it can create an index on them.

Example:
SELECT DocHdId, DocDate, DocKndId, CreateDate, CmpId, BrnId INTO #Inserted
FROM Inserted WHERE DocHdId IS NULL;
CREATE UNIQUE CLUSTERED INDEX PK_#Inserted_DocHdId ON #Inserted(DocHdId);

DELETE FROM ... OUTPUT ... INTO #Inserted;

INSERT INTO TableName (....)
SELECT .. FROM #Inserted

From here work only with # Inserted; # Inserted table meets the requirements for MS
output_table in OUTPUT INTO.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-29 : 10:28:24

but then if he/she has to copy the entire table why cant just a select * into... statement and deletion on first table within a transaction work? whats the need to use triggers?

kpkirilov
Starting Member

10 Posts

Posted - 2008-07-29 : 10:29:10

quote:
Originally posted by rohitkumar

quote:
Originally posted by kpkirilov

Use DELETE AFTER TRIGGER. In the body of trigger have access to temporary table DELETED (deleted records).

In addition, you can use DELETE FROM ... OUTPUT INTO. But there are quite limited-to avoid use them this time with the local temporarily table.

is this a joke?

Hi rohitkumar.

I have a problem related to INSERT .. OUTPUT INTO(Topic:INSERT ... OUTPUT INTO in INSTEAD OF TRIGGER). Can we consider it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-29 : 10:29:50

quote:
Originally posted by Kalpa

Hi

I need to write a query that will copy the entire records from a table e.g(BatchProcessDaily)and insert these values to another table e.g(BatchProcessHistoryTable)and simultaneously it will clear the data from the previous table.So can u please provide the way how to implement it with an example.

DECLARE @ARCHIVED_IDS table
(
PKCol int
)
INSERT INTO BatchProcessHistoryTable (all fields except id field)
OUTPUT INSERTED.PKCol INTO @ARCHIVED_IDS
SELECT reqdfields from BatchProcessDaily

DELETE t FROM BatchProcessDaily t
INNER JOIN @ARCHIVED_IDS a
ON a.PKCol=t.PKCol

PKCol is the primary key of BatchProcessDaily table. you should have the same datatyped field in @ARCHIVED_IDS table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources