Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Adding Rows to an existing table sql 2005 Expand / Collapse
Author
Message
Posted Friday, April 26, 2013 2:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, September 20, 2014 10:38 AM
Points: 17, Visits: 34
hi
i have a table from navision PLSo that is being updated every hour now i have created a table on my server PLSy now i want PLSo to update PLSy after 30 min it has got that info .. i don't mind doing it by my self if i cannot schedule it..Please help with the statement.. PLSo has a field that is never duplicated so i used it to say PLSo give everything(new rows that are captured on you) that PLSy do not have

select * into [PLSy] from [srv5].[DB].[dbo].[PLSo]--Navision
where [timestamp] not in (select [timestamp] from [PLSy])

I tried using this statement it does not work since the table is there so for the past week i have been deleting and re creating this table

please help
Post #1446838
Posted Friday, April 26, 2013 4:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:51 AM
Points: 2,693, Visits: 4,755
You will have to use an INSERT..SELECT option

INSERT	INTO [PLSy]( <<Column1>>, <<Column2>>, ....<<Columnn>> )
SELECT <<Column1>>, <<Column2>>, ....<<Columnn>>
FROM [srv5].[DB].[dbo].[PLSo] -- Navision
WHERE [timestamp] NOT IN ( SELECT [timestamp] FROM [PLSy])

Another better way would be to use NOT EXISTS instead of NOT IN

INSERT	INTO [PLSy]( <<Column1>>, <<Column2>>, ....<<Columnn>> )
SELECT <<Column1>>, <<Column2>>, ....<<Columnn>>
FROM [srv5].[DB].[dbo].[PLSo] AS Nav-- Navision
WHERE NOT EXISTS( SELECT * FROM [PLSy] AS PL WHERE PL.[timestamp] = Nav.[timestamp] )




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1446890
Posted Monday, April 29, 2013 12:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, September 20, 2014 10:38 AM
Points: 17, Visits: 34
hi

thank you i will try it and get back to u
Post #1447376
Posted Monday, April 29, 2013 7:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, September 20, 2014 10:38 AM
Points: 17, Visits: 34
Thank you it worked!!!!
Post #1447524
Posted Monday, April 29, 2013 7:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:51 AM
Points: 2,693, Visits: 4,755
kgeeel240 (4/29/2013)
Thank you it worked!!!!


Glad it helped



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1447540
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse