Greetings all - it's been a long time...
It doesn't help that I'm pretty rusty at this sort of thing and I'm sure the answer is obvious, but I am struggling with this INSERT query this morning.
I want to populate a lookup table with all the distinct values from a column in a different table.
Here's the INSERT query:
INSERT INTO [dbo].[FMDFPRI] ([SPri] ,[descr] ,[created] ,[edited]) select p.priority as Spri ,p.priority as descr ,'20161201092101946CSS01 ' as created ,'20161201092101946CSS02 ' as edited from FMDFSRQ p where p.priority <> '' and p.priority not in (select Spri from fmdfpri) GO
I get this error message:
Violation of PRIMARY KEY constraint 'FmSPriKey'. Cannot insert duplicate key in object 'dbo.FMDFPRI'. The duplicate key value is (2 ).
Can anyone help?
Is the ID column the first one in your list of insert columns? SPri
You can rt click on the table and go to Script Table as, then use insert to, and have it write out the plain Jane code for this table's insert.
On Mon, Sep 25, 2017 at 11:22 AM, M Jarvis brewdaddy@gmail.com wrote:
Greetings all - it's been a long time...
It doesn't help that I'm pretty rusty at this sort of thing and I'm sure the answer is obvious, but I am struggling with this INSERT query this morning.
I want to populate a lookup table with all the distinct values from a column in a different table.
Here's the INSERT query:
INSERT INTO [dbo].[FMDFPRI] ([SPri] ,[descr] ,[created] ,[edited]) select p.priority as Spri ,p.priority as descr ,'20161201092101946CSS01 ' as created ,'20161201092101946CSS02 ' as edited from FMDFSRQ p where p.priority <> '' and p.priority not in (select Spri from fmdfpri) GO
I get this error message:
Violation of PRIMARY KEY constraint 'FmSPriKey'. Cannot insert duplicate key in object 'dbo.FMDFPRI'. The duplicate key value is (2 ).
Can anyone help?
-- Matt Jarvis Eugene, Oregon USA
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
On Mon, Sep 25, 2017 at 9:33 AM, Stephen Russell srussell705@gmail.com wrote:
Is the ID column the first one in your list of insert columns? SPri
You can rt click on the table and go to Script Table as, then use insert to, and have it write out the plain Jane code for this table's insert.
Yes, SPri is the PK and first column in the table being inserted in to.
The plain jane is what I used as the template to create the query, then tried modifying it to use the values from the FMDFSRQ table.
mj
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
Actually, I came up with a brute force method that seems to do the job. Thanks....
INSERT INTO [dbo].[FMDFPRI] ([SPri]) select distinct p.priority from fmdfsrq p where p.priority <> '' GO
update fmdfpri set descr = Spri , created = '20161201092101946CSS01 ' , edited = '20161201092101946CSS02 ' GO
You need a bigger hammer.
What are the rules for the PKey? It doesn't make sense that you would generate that value and not have the db create it for you. Is it an INT? Can you just do: INSERT INTO [dbo].[FMDFPRI] (created, edited) values ( '20161201092101946CSS01 ' , '20161201092101946CSS02 ' )
On Mon, Sep 25, 2017 at 12:16 PM, M Jarvis brewdaddy@gmail.com wrote:
Actually, I came up with a brute force method that seems to do the job. Thanks....
INSERT INTO [dbo].[FMDFPRI] ([SPri]) select distinct p.priority from fmdfsrq p where p.priority <> '' GO
update fmdfpri set descr = Spri , created = '20161201092101946CSS01 ' , edited = '20161201092101946CSS02 ' GO
-- Matt Jarvis Eugene, Oregon USA
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
On 2017-09-25 13:16, M Jarvis wrote:
Actually, I came up with a brute force method that seems to do the job. Thanks....
INSERT INTO [dbo].[FMDFPRI] ([SPri])
If I never have to do SQL Server T-SQL bracket crap again I'll be happy. YES, I CAN DO IT AND DO IT WELL but God I hate it. I prefer PL-SQL or MySQL syntax instead. No good reason I guess; just personal preference.
On Thu, 28 Sep 2017, at 01:47 AM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
If I never have to do SQL Server T-SQL bracket crap again I'll be happy.
You don't need brackets in T-SQL, unless the name of something is a keyword or has special characters. I never use them.
On 2017-09-28 03:20, Alan Bourke wrote:
On Thu, 28 Sep 2017, at 01:47 AM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
If I never have to do SQL Server T-SQL bracket crap again I'll be happy.
You don't need brackets in T-SQL, unless the name of something is a keyword or has special characters. I never use them.
Last gig I worked at they were religious about them pretty much all the time, regardless. Real turn off.
If you can do it why do you need brackets? Outside of describing a server instance we never use [].
On Wed, Sep 27, 2017 at 7:47 PM, < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 2017-09-25 13:16, M Jarvis wrote:
Actually, I came up with a brute force method that seems to do the job. Thanks....
INSERT INTO [dbo].[FMDFPRI] ([SPri])
If I never have to do SQL Server T-SQL bracket crap again I'll be happy. YES, I CAN DO IT AND DO IT WELL but God I hate it. I prefer PL-SQL or MySQL syntax instead. No good reason I guess; just personal preference.
[excessive quoting removed by server]
-
Alan Bourke -
M Jarvis -
mbsoftwaresolutions＠mbsoftwaresolutions.com -
Stephen Russell