Read the data from a table and insert into anethor

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 2008 Forums

Transact-SQL (2008)

Read the data from a table and insert into anethor

Author

Topic

kranthiblogs
Starting Member

5 Posts

Posted - 2011-04-16 : 05:40:29

hi ..
i have a requirement..
1.table1 contains multiple coluns and consists many rows of data
2.we need to insert into table2 ( few columns data from table1)

lets say. table1 contains 1000 rows of data..
while looping throw table1 and inserts into table2.. if any error encounters... we need to skip that record and go ahead with other rows of data...

error prone record from table1 need to logged into some other table

write a Stored procedure for this.. we can use cursors or plain looping features

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-16 : 06:38:31

So now you want us to do the job for you?
What have you done so far?

No, you're never too old to Yak'n'Roll if you're too young to die.

kranthiblogs
Starting Member

5 Posts

Posted - 2011-04-16 : 08:33:51

USE [Kranthi]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spt_UpdateSite] AS
SET NOCOUNT ON

DECLARE
@DownLoadID int,
@CMF varchar(50) ,
@Control varchar(50),
@Owner varchar(50),
@Plate varchar(20),
@Sticker varchar(50),
@Amount money,
@Year DateTime,
@Make varchar(50),
@NoOfRows int
SET @DownLoadID = 0
--select NoOfRows = COUNT(1) from dbo.tblDownLoad

DECLARE Deals_cursor CURSOR FOR
SELECT DownLoadID, CMF,
Control,
Owner,
Plate,
Sticker,
[Amount(pennies)],
Case When [year] = '' Then null Else [year] End [year],
make
FROM tblDownLoad

open Deals_cursor
FETCH NEXT FROM Deals_cursor
INTO @DownLoadID, @CMF ,
@Control ,
@Owner ,
@Plate ,
@Sticker ,
@Amount ,
@Year ,
@Make

continueLoop:

begin try
PRINT 'try entered '
begin tran
while @@fetch_status = 0
begin
print 'hello ---' +@CMF

INSERT INTO tblDeals(CMF,
Control,
Owner,
Plate,
Sticker,
TotAmount,
YearModel,Make
)
Values(@CMF,
@Control,
@Owner,
@Plate,
@Sticker,
@Amount,
Convert(int,Year(@Year)),@Make
)

FETCH NEXT FROM Deals_cursor
INTO @DownLoadID, @CMF ,
@Control ,
@Owner ,
@Plate ,
@Sticker ,
@Amount ,
@Year ,
@Make
end

commit tran

end try

begin catch
PRINT 'Catch entered ' + ERROR_MESSAGE()
--ERROR_MESSAGE()
insert into dbo.tblErrorLog values (@DownLoadID, @@ERROR)
FETCH NEXT FROM Deals_cursor
INTO @DownLoadID, @CMF ,
@Control ,
@Owner ,
@Plate ,
@Sticker ,
@Amount ,
@Year ,
@Make
goto continueLoop
end catch

close Deals_cursor
deallocate Deals_cursor

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-16 : 09:27:20

Why a cursor? What's wrong with a straightforward INSERT ... SELECT?

--
Gail Shaw
SQL Server MVP

kranthiblogs
Starting Member

5 Posts

Posted - 2011-04-16 : 13:24:19

i just simplified my question..

that insert contains - 3 separate inserts into 3 tables..

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-16 : 18:23:44

My question remains. Why do you want a cursor or loop. Very bad approach.

--
Gail Shaw
SQL Server MVP

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-17 : 10:40:38

Here is need of

DECLARE @c1 int
DECLARE @c2 varchar(300)
DECLARE @id int

DECLARE @INScount int
DECLARE @insert int

SELECT @INScount = count(*) from TABTEST1
set @insert =0

while @insert != @INSCount
begin

Select top 1 @id=id,@c2=col1111 from Tabtest1

Begin Transaction T1
insert into Tabtest2(col1111) values(@c2)

if @@ERROR = 0
Begin
commit transaction t1
print 'commit'
delete from table1 where id = @id
End
else
Begin
rollback transaction T1
print 'rollback'
End
set @insert = @insert + 1
End

that is a sample code

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources