October 21, 2014 at 12:50 am
Hi.
There is one seprate archive database available with name of DB_ARCHIVE.
In source database tables like Header and details, that same tables are available at DB_ARCHIVE database.
In my case source tables date column available in header table but details table date column not available. so how can move the data each seprate table(Header and detail) in DB_ARCHIVE database. Pls provide suitable soultions.
INSERT INTO DB_Archive.dbo.Prescription_H
SELECT * FROM Prescription_H
WHERE Modify_Dt >= CONVERT(DATETIME, '01-01-2010', 103)and Modify_Dt <= CONVERT(DATETIME, '01-01-2011', 103) ORDER BY Modify_Dt ASC
--(206802 row(s) affected)
source tables definations
----------------------------
/****** Object: Table [dbo].[Prescription_H] Script Date: 10/21/2014 11:59:53 ******/
CREATE TABLE [dbo].[Prescription_H](
[Presc_No] [numeric](20, 0) NOT NULL,
[Presc_DateTime] [datetime] NOT NULL,
[RegnNo] [float] NOT NULL,
[Presc_By] [varchar](10) NOT NULL,
[Modify_By] [varchar](10) NOT NULL,
[Modify_Dt] [datetime] NOT NULL,
[Comment] [varchar](250) NULL,
CONSTRAINT [PK_Prescription_H] PRIMARY KEY CLUSTERED
(
[Presc_No] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Prescription_D] Script Date: 10/21/2014 11:59:53 ******/
CREATE TABLE [dbo].[Prescription_D](
[Presc_No] [numeric](20, 0) NOT NULL,
[Sr_No] [numeric](2, 0) NOT NULL,
[Drug] [varchar](100) NOT NULL,
[Formulation] [varchar](50) NULL,
[Dosage] [varchar](50) NULL,
[Strength] [varchar](50) NULL,
[Qty] [numeric](5, 0) NULL,
[Period] [varchar](50) NULL,
[Route] [varchar](50) NULL,
[Regular] [numeric](1, 0) NULL,
CONSTRAINT [PK_Prescription_D] PRIMARY KEY CLUSTERED
(
[Presc_No] ASC,
[Sr_No] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
Thanks
October 21, 2014 at 1:37 am
Join the two tables to filter on the date:
INSERT INTO DB_Archive.dbo.Prescription_D
SELECT Prescription_D.*
FROM Prescription_D
INNER JOIN Prescription_H
ON Prescription_D.Presc_No = Prescription_H.Presc_No
WHERE Prescription_H.Modify_Dt >= CONVERT(DATETIME, '01-01-2010', 103)and Prescription_H.Modify_Dt <= CONVERT(DATETIME, '01-01-2011', 103)
INSERT INTO DB_Archive.dbo.Prescription_H
SELECT *
FROM Prescription_H
WHERE Modify_Dt >= CONVERT(DATETIME, '01-01-2010', 103)and Modify_Dt <= CONVERT(DATETIME, '01-01-2011', 103)
-- Gianluca Sartori
October 21, 2014 at 3:08 am
Wow, Thank your script working fine and I moved since last 5 years data into archive database.
But I can't delete the records at source table as below command.
DELETE FROM Prescription_D
INNER JOIN
ON Prescription_D.Presc_No = Prescription_H.Presc_No
WHERE Prescription_H.Modify_Dt >= CONVERT(DATETIME, '01-01-2012', 103)and Prescription_H.Modify_Dt <= CONVERT(DATETIME, '01-01-2013', 103)
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'INNER'.
Pls. help.
October 21, 2014 at 3:23 am
DELETE D
FROM Prescription_D AS D
INNER JOIN Prescription_H AS H
ON D.Presc_No = H.Presc_No
WHERE H.Modify_Dt >= CONVERT(DATETIME, '01-01-2012', 103)and H.Modify_Dt <= CONVERT(DATETIME, '01-01-2013', 103)
-- Gianluca Sartori
October 21, 2014 at 3:28 am
Thank you Gianluca Sartori, Delete script working fine...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply