moving the data from one table to another table

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

General SQL Server Forums

New to SQL Server Programming

moving the data from one table to another table

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2012-04-11 : 13:50:57

Hi All,

I have to insert the data from one table to another table , I can do the bulk insert statement to update the final table, but if one records fails then none of the records will be inserted. I want that if one record fails ignore that record and go further with the insert. can I do it without cursors.

Currently, I am doing with cursors.

also, is it possible to find out which record failed.

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-11 : 19:20:43

Bulk insert or any set-based approach would all succeed together or fail together.

You can use SSIS to do more intelligent error handling than is possible with bulk insert. Although I don't have much experience with it, that would be my preferred choice.

Another alternative would be to first import the data into a staging table. The staging table would have the same structure as the input data, except that all columns would be character type - for example, VARCHAR(1024) or VARCHAR(MAX). Then, use a script to examine the data and make any changes/corrections required and to insert only data that is valid into your final destination table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-12 : 00:29:21

for ssis you can use a simple data flow task for achieving this. You can add a conditional task to check your rules and redirect invalid records to error table of file. the valid records will be directed to main output which populates your destination table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-12 : 06:05:50

This might not work in all cases. I tried it with some sample data and it was working. Please do correct me if I am wrong.

I created a table, inserted some sample data into it and then deleted some data from the table as follows:


--create table

CREATE TABLE [dbo].[Test](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Status] [varchar](10) NULL
) ON [PRIMARY]

--insert Data in table

Declare @temp int = 1
While(@temp<125)
Begin
INSERT INTO Test Values('Checked')
Set @temp = @temp + 1
End

--Deleting Some Data From Table

Delete From Test
Where Id In (1,10,20,30,40,50,60,70,80,90,100,110,120)

After this I used the DTS Wizard and using the "Write a Query to specify the data to be transferred" option, I wrote the following query to transfer the data:


Select * From Test
Where Id IN(11,21,31,41,1,10,20,30,40,50,60,70,80,90,100,110,120,51,61,71,81,91,101,111,121)

The above query also includes the Ids that were deleted. According to what you said I was hoping that it would give an error inserting the Ids I had deleted. But, surprisingly it didn't give any error and inserted only the Ids that were currently present in the table(the deleted ones were excluded).
The important thing is that there was no error and the complete task was completed without a rollback.

I hope this might help you.
I am also new to this and always ready to learn.

Vinu Vijayan

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources