Showing posts with label inserts. Show all posts
Showing posts with label inserts. Show all posts

Friday, February 24, 2012

AppendStored Procedure

I got this stored procedure I'm running that inserts data from one table to another, but it keeps telling me that I'm violating the primary key rule which means one those records or 12 for all I know, already exsist in the other table. How do I add a IF ALREADY EXSIST CLAUSE TO MY STORED PROCEDURE, is that the correct word for it?? I'll look it up also. Heres the stored procedure I have so far.

ALTER PROCEDURE dbo.[2003_CorovanInsert]
AS INSERT INTO dbo.Corovan_Table
([TM #], FirstName, LastName, [SS #], TerminationDate)
SELECT [TM #], FirstName, LastName, SocialSecurityNumber, TerminationDate
FROM dbo.[2003 TERMINATIONS]
WHERE (TerminationDate BETWEEN CONVERT(DATETIME, '2003-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2003-12-31 00:00:00', 102))
GOYou are almost there. Try this (it will need a little bit of work from you, but you should get it quickly)

ALTER PROCEDURE dbo.[2003_CorovanInsert]
AS INSERT INTO dbo.Corovan_Table
([TM #], FirstName, LastName, [SS #], TerminationDate)
SELECT [TM #], FirstName, LastName, SocialSecurityNumber, TerminationDate
FROM dbo.[2003 TERMINATIONS] left join dbo.Corovan_Table on dbo.Corovan_Table.(primary key field) = dbo.[2003 TERMINATIONS].(primary key field)
WHERE (TerminationDate BETWEEN CONVERT(DATETIME, '2003-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2003-12-31 00:00:00', 102))
and dbo.corovan_table.(primary key field) is null
Go

You will have to supply the (primary key field)'s. Hope this helps.|||ALTER PROCEDURE dbo.[2003_CorovanInsert]
AS INSERT INTO dbo.Corovan_Table
([TM #], FirstName, LastName, [SS #], TerminationDate)
SELECT [TM #], FirstName, LastName, SocialSecurityNumber, TerminationDate
FROM dbo.[2003 TERMINATIONS] left join dbo.Corovan_Table on dbo.Corovan_Table.(primary key field) = dbo.[2003 TERMINATIONS].(primary key field)
WHERE (TerminationDate BETWEEN CONVERT(DATETIME, '2003-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2003-12-31 00:00:00', 102))
and not exists (select <primary key field> from dbo.corovan_table)Go|||Problem solved guys thanks so much for your help, I sure do appreciate it, was struggling with that all day. At least it was a nice learning experience for me.

Thanks guys :)|||Hi guys its me again I'm having problems with my stored procedure again it executes as long as theres no primary key rule violations. But there is a violation Of the primary key because the the stored procedure was executed yesterday, but today I have discovered that since this is based on the 2003 terms date not all the 2003 terms were dated as 2003 (user error) so now I need to edit the stored procedure, which of course I'm not doing correctly.

Help? Again Please

ALTER PROCEDURE dbo.[03Corovans]
AS INSERT INTO dbo.Corovan_Table
([TM #], FirstName, LastName, [SS #], TerminationDate)
SELECT [TM #], FirstName, LastName, SocialSecurityNumber, TerminationDate
FROM dbo.[03TermsWithoutMatchingCorovan]
and not exists (select <TM #> from dbo.Corovan_table)Go|||Oh good Grief Never mind I looked at the script again and did the necessary corrections now it works just fine. AND I FEEL LIKE AN IDIOT

SORRY GUYS|||I'm so ashamed and embarrassed.....DUH!!!!!!!!!!!!!!!!!!!!!!

Merry Christmas and
Happy Holidays everyone|||AND I FEEL LIKE AN IDIOTDoes that mean "with your hands" ?

-PatP|||That means with my hands and My SO CALLED BRAIN|||Did that answer your question Pat or did I miss the point??|||Did that answer your question Pat or did I miss the point??You are assuming that my questions have a point... Not everyone would jump on that bus!

Yes, an idiot normally feels things with their hands, so if you are "feeling like an idiot", I'd assume that you were wandering about with a goofy expression, putting your hands on everything you could.

Nevermind... I never claimed that it made any sense, I just found it humorous!

-PatP|||You know Pat I know alot of people with very High IQ's who have a simular sense of humor as yourself, so I should have known better. Next time I'll just let that bus go right by me. :)

Appending to a recordset destination

Hi,
We have a data-flow that extracts from a raw file and inserts into a datareader destination.

The data-flow requires DelayValidation=TRUE because of the raw file source (http://blogs.conchango.com/jamiethomson/archive/2005/12/01/2443.aspx)

Unfortunately it also requires DelayValidation=FALSE because of the Datareader destination (http://blogs.conchango.com/jamiethomson/archive/2005/10/20/2284.aspx)

So basically we can't do it this way.

We'd like to replace the raw file destination with a recordset destination but we have to be able to append to it.

Is there a way of appending when using a recordset destination?

Thanks in advance!
-JamieJamie,

I believe that your first statement is not correct for the raw file source (just for the raw file destination) as long as you have already created the raw file before the dataflow with the raw file source validates. So you could create a empty raw file with just the correct metadata and then set delay validation to false and what you want to do should work.

In answer to your question, I do not believe we have a way to append using a datareader dest.

Thanks,
Matt|||Matt,
That's true - but I don't really want to be moving raw files into a production envioronment. We want to deploy the packages and nothing else!

We are thinking of writing a custom destination adapter that appends to a recordset. Do you think that is possible?

I guess its a question of which is the least nasty workaround so your suggestion IS something to consider.

-Jamie

P.S. Is it worth raising a DCR asking for the ability to append to a recordset destination? This would mean it could be used in place of a raw file which could be rather useful.|||Hi Jamie,

I am certain it is possible to create a custom destination adapter to do what you want. I don't know that it wouldn't be really nasty to do so. I think the empty raw file is a little more hacky but much easier to do.

I can't see why you couldn't raise this as a DCR. I don't know whether it would make it into the next version or not but that isn't reason not to ask. Smile

Thanks,
Matt|||OK. Well I have done. :)

http://lab.msdn.microsoft.com/ProductFeedback/thankyou.aspx?FeedbackID=FDBK42005&submit=true

-Jamie

Thursday, February 16, 2012

Append Data

Hi, i need some help as i'm not good with SQL. I have 2 databases n SQL and i'm trying to write a rountine were it inserts all the new data from a live table to another table in another database.

Is there any way how to do this?

Once i have figurered that bit out i need to run the rountine every night automaically.

I would really appreciate if you have the answers for my questions

Thankyou in advanceinsert
into anotherdb.anothertable
select *
from livetable
where data = 'new'|||insert
into anotherdb.anothertable
select *
from livetable
where data = 'new'

You funny

What the DDL look like?|||We've had a topic name change - ANSI SQL - Yay! :D|||You funnyi are oney tryna be hepful :angel:

i used all the available information in the oiriginal post!!|||R937: Is the "anotherdb" from your example code a user (as in Microsoft/Sybase), a schema (as in Oracle/DB2), a database (as in MySQL/Pervasive), or something different? I guess that my point is that your code as presented will do slightly different things, depending on which database implementation is used to execute it.

-PatP|||PatP: yes :)|||Rudy's query works in DB2 even if both tables reside in different databases (not only different schemas) - DB2 for z/OS, that is. ;-)

The interesting thing is that ISO/IEC 9075:2003-2 is very, very vague about what a "database" actually is. This results from the fact that different DBMS vendors apply this term to different concepts (and DBMS vendors drive the standard).

@.Hemish: you should be a bit more specific what you mean. Do you refer to schemas/schemata or federation?|||Instead of writing a query you might be better using the backup tools available for your particular database system. For MSSQL you can set up nightly backup jobs using the scheduling tool that is available. In MySQL you can use the Mysqldump utility (from cmd line) to run a backup, which you could schedule using a cronjob (if unix) or windows scheduler (if win).|||Backup tools are not standard SQL, of course...|||Backup tools are not standard SQL, of course...Yeah, Standard SQL doesn't support backups (literally true, since backups are only supported through vendor extensions, the standard definitions don't provide any references to backups).

-PatP