Copying Data to a New 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 2008 Forums

Transact-SQL (2008)

Copying Data to a New Table

Author

Topic

Nicole0904
Starting Member

4 Posts

Posted - 2012-04-27 : 11:31:18

I have the code below. What I'm trying to accomplish is read data from a table and copy it into a new table. If the data is already in the new table I want to update. If the data is not there I want to insert.

My table [pak-web] has 28 rows. This code writes 24 to the new table. Any help would be greatly appreciated.

begin
set nocount on

declare @Part varchar(30)
declare @datasheet nvarchar(255)

declare crsr_facprice cursor for
select
ltrim(rtrim(partnumber)),
[pdfurl]
from ordsys.dbo.[pak-web]

open crsr_facprice

fetch crsr_facprice into @Part, @datasheet
set @seq=0
while @@FETCH_STATUS = 0
begin

update facpricelist
set part = @part, datasheet=@datasheet
Where ltrim(rtrim(Part)) = @Part

if @@ROWCOUNT=0
insert into facpricelist(mfg, part, datasheet,webdatadate)
VALUES ('PAK',@part, @datasheet, getdate())

fetch crsr_facprice into @Part, @datasheet

end
close crsr_facprice
deallocate crsr_facprice
end

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-27 : 11:51:03

you could do it set based with the wonderful command MERGE

something like this


MERGE facpricelist AS target
USING (
		SELECT ltrim(rtrim(partnumber)) Part,
               [pdfurl]
          FROM ordsys.dbo.[pak-web]
       ) 
   AS source (Part, datasheet)
   ON (target.Part = source.Part)
 WHEN MATCHED THEN 
      UPDATE SET part = source.part,
                 datasheet=source.datasheet
WHEN NOT MATCHED THEN    
     INSERT (mfg, Part, datasheet, webdatadate)
     VALUES ('PAK', source.Part, source.datasheet, getdate())
     OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;

caveat how big of a data do you want to move? this part

SELECT ltrim(rtrim(partnumber)) Part,
               [pdfurl]
          FROM ordsys.dbo.[pak-web]

could bite you
by the way you are doing a lot of whack stuff in there with ltrim rtrim you will have to implement in this MERGE. Why not put a default constraint on webdatadate instead of doing getdate()

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Nicole0904
Starting Member

4 Posts

Posted - 2012-04-27 : 12:17:01

There are very few lines (20-30)

This is SQL 2005 so merge doesn't appear to work

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-27 : 13:17:38

try this


;WITH ctepakweb(Part, datasheet)
AS
(
select ltrim(rtrim(partnumber)) Part,
       [pdfurl] datasheet
       
from ordsys.dbo.[pak-web]
)
update tgt
   set tgt.datasheet= src.datasheet   
 FROM facpricelist tgt
 inner join ctepakweb src
  on ltrim(rtrim(tgt.Part)) = src.Part

insert into facpricelist(mfg, part, datasheet,webdatadate)
select 'PAK' mfg,
       ltrim(rtrim(partnumber)),
       pdfurl,
       getdate()
  from ordsys.dbo.[pak-web] o
  left join facpricelist f
   on ltrim(rtrim(f.Part)) = ltrim(rtrim(o.partnumber))
  and ltrim(rtrim(f.Part)) IS NULL

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-28 : 07:33:00

The method given by yosiasz should work in Sql Server 2005.

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

Nicole0904
Starting Member

4 Posts

Posted - 2012-04-30 : 12:25:46

The only issue is that if the items already exist they are being re-created in the target database.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-30 : 12:53:12

Nicole, since you are very vague in your posts and you are piecemealing your questions it will be very difficult to help you. can you specify what makes a record unique in the target "database"

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Nicole0904
Starting Member

4 Posts

Posted - 2012-05-01 : 08:49:40

Part number makes a record unique. If you look at my original post this is what I was working with. The issue with that is when all records are new it only is writing 24 of the 28 records. It appears to work fine for updates.

I have two tables. pak-web contains data to be entered into facpricelist. Facpricelist may already contain the part number. If it does I want to update. If it doesn't I want to insert.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources