migration script - 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)

migration script

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-12-01 : 05:24:41

hi iam having two tables A and B
COLUMNS A are b,c,
COLUMNS B are a,b,c

b and c are available in both tbales.

now i need to insert what ever the record are there in A into Corresponding B column tables.
and column a from B table should start with value 1501 and so on.

how to give a stored procedure in doing so.

vikky
Yak Posting Veteran

54 Posts

Posted - 2009-12-01 : 05:52:43

hi,

CREATE PROC PROCEDURENAME
(
@start_val INT = NULL
)
AS
BEGIN
SET NOCOUNT OFF

SELECT @start_val = 1501 WHERE @start_val IS NULL

DECLARE @a TABLE ( b INT, c INT)
INSERT INTO @a
SELECT 1,4 union all
SELECT 3,5 union all
SELECT 7,6 union all
SELECT 9,7 union all
SELECT 1,6

DECLARE @b TABLE ( a INT, b INT, c INT)

INSERT INTO @b(a,b,c)
SELECT DISTINCT @start_val+number,b,c
FROM ( SELECT ROW_NUMBER()OVER (ORDER BY b,c)AS row,b,c FROM @a) AS a
INNER JOIN master..spt_values AS n ON n.type = 'p' and a.row = n.number+1

SELECT * FROM @b

END
SET NOCOUNT ON

-------------------

Execution :
Exec PROCEDURENAME null ( or ) any value u need.

Thanks,
vikky.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-01 : 05:58:35

If you use 2005 or greater then....

insert into b
select 1500+row_number() over (order by b,c),b,c from a

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-12-01 : 06:31:59

i have written like this but values are not inserting in the another table.please check

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Migration_PATIENTDETAILS]') AND type = 'P')
drop Procedure [dbo].[Migration_PATIENTDETAILS]
GO

CREATE PROCEDURE [dbo].[Migration_PATIENTDETAILS]
(
@start_val INT = NULL
)
AS
BEGIN
SET NOCOUNT OFF

SELECT @start_val = 1501 WHERE @start_val IS NULL

DECLARE @EMRFEMALEPATIENTDETAILS TABLE ( PATIENT_ID INT, ENCOUNTER_ID INT)
INSERT INTO @EMRFEMALEPATIENTDETAILS
SELECT 1,4 union all
SELECT 3,5 union all
SELECT 7,6 union all
SELECT 9,7 union all
SELECT 1,6

DECLARE @EMRFRExamData TABLE ( FR_EXAM_DATA_ID INT, PATIENT_ID INT, ENCOUNTER_ID INT)

INSERT INTO @EMRFRExamData (FR_EXAM_DATA_ID,PATIENT_ID,ENCOUNTER_ID)
SELECT DISTINCT @start_val+number,PATIENT_ID,ENCOUNTER_ID
FROM ( SELECT ROW_NUMBER()OVER (ORDER BY PATIENT_ID,ENCOUNTER_ID)AS row, PATIENT_ID,ENCOUNTER_ID FROM @EMRFEMALEPATIENTDETAILS) AS EMRFEMALEPATIENTDETAILS
INNER JOIN master..spt_values
AS n ON n.type = 'p' and EMRFEMALEPATIENTDETAILS.row = n.number+1

SELECT * FROM @EMRFRExamData
END
GO
SET NOCOUNT ON

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources