May 30, 2012 at 11:23 pm
Hi,
Not sure how to do this and obviously have very little SQL skills.
We have a table with 3 columns.
Column 1 is 'groups_pk1' (int)
Column 2 is 'application' (varchar(64))
Column 3 is 'pk' and generates a value on its own
1970blogs 51284
1970collaboration51285
1970discussion_board51286
1970course_email51287
1970file_exchange51288
1970journal 51289
Above is an example of what's in them.
Now what we want to do is have a query that inserts a new row with the application containing 'tasks' only if it doesn't already exist, it would need to automatically get column 1. So it would look like:
1970 tasks
The third column doesn't appear to matter and automatically gets that number.
Any help would be great.
Thanks
Ben
May 31, 2012 at 1:26 am
Something like this?
--Creating Table
Create Table Ex
(Column1 int,
Column2 varchar(64),
Column3 int Identity(51284, 1) Primary Key)
--Inserting Sample Data
Insert into Ex
Select '1970', 'blogs'
Union ALL
Select '1970', 'collaboration'
Union ALL
Select '1970', 'discussion_board'
Union ALL
Select '1970', 'course_email'
Union ALL
Select '1970', 'file_exchange'
Union ALL
Select '1970', 'journal'
--Query For you Requirement
If Not Exists(Select * From Ex Where Column2 = 'Task')
Begin
Insert Into Ex Values(1970, 'Task')
End
May 31, 2012 at 1:35 am
Hi
You can do this using NOT EXISTS
DECLARE @TABLE AS TABLE
(Groups INT, Application VARCHAR (50), PK INT)
INSERT INTO @TABLE
(Groups, Application, PK)
SELECT 1970, 'blogs', 51284 UNION ALL
SELECT 1970, 'collaboration', 51285 UNION ALL
SELECT 1970, 'discussion_board', 51286 UNION ALL
SELECT 1970, 'course_email', 51287 UNION ALL
SELECT 1970, 'file_exchange', 51288 UNION ALL
SELECT 1970, 'journal', 51289
SELECT *
FROM @TABLE
INSERT INTO @TABLE
(Groups, Application, PK)
SELECT 1970, 'blogs', 51284 UNION ALL
SELECT 1970, 'Tasks', 51284
WHERE
NOT EXISTS
(SELECT Application FROM @TABLE)
SELECT *
FROM @TABLE
Hope it helps
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 31, 2012 at 4:49 am
Thanks for that, apologies if one of the previous posts answers my question, the value in column 1 needs to be automatically generated.
If Not Exists(Select * From group_application Where application = 'tasks')
Begin
Insert Into group_application Values(groups_pk1, 'tasks')
End
It needs to check that tasks doesn't exist for a number and if it doesn't insert a row with that number and the application 'tasks'.
Hope that makes sense.
1234 blog
1234 tasks
4321 blog - tasks doesn't exist so create 4321 and tasks
4321 tasks
Thanks
Ben
May 31, 2012 at 4:56 am
, the value in column 1 needs to be automatically generated.
What do you mean by automatically generated? Do you mean it just increases the value everytime a row is inserted if so look at this link:
http://www.w3schools.com/sql/sql_autoincrement.asp
Or does the auto generated ID have some business logic behind it?
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 31, 2012 at 5:09 am
Hi,
I've edited my last post to try make more sense regarding setting column 1 value. Below is just one of many that look the same, it needs to check that 544 has no 'tasks' and if it doesn't then insert a row with 544 and column 2 'tasks', there's heaps of rows which is why it needs to somehow get the column 1 value.
544Bb-wiki
544blogs
544collaboration
544course_email
544discussion_board
544file_exchange
544journal
Ben
May 31, 2012 at 5:49 am
If there are heaps of rows and different values in Column1 then you can use a procedure as follows:
--Creating Table
Create Table Ex
(Column1 int,
Column2 varchar(64),
Column3 int Identity(51284, 1) Primary Key)
--Inserting Sample Data
Insert into Ex
Select 1970, 'blogs'
Union ALL
Select 1970, 'collaboration'
Union ALL
Select 1970, 'discussion_board'
Union ALL
Select 1970, 'course_email'
Union ALL
Select 1970, 'file_exchange'
Union ALL
Select 1970, 'journal'
Union ALL
Select 544,'Bb-wiki'
Union ALL
Select 544,'blogs'
Union ALL
Select 544,'collaboration'
Union ALL
Select 544,'course_email'
Union ALL
Select 544,'discussion_board'
Union ALL
Select 544,'file_exchange'
Union ALL
Select 544,'journal'
--Creating Procedure
Create Procedure dbo.Ex_Proc
@Column1 int
As
Begin
If Not Exists(Select * From Ex Where Column1 = @Column1 AND Column2 = 'Tasks')
Begin
Insert Into Ex Values(@Column1, 'Tasks')
End
Else
Begin
Return
End
End
--Executing Procedure
Execute dbo.Ex_Proc @Column1 = 544
Does that help?
May 31, 2012 at 6:12 am
you could try a left join with an insert:
-- create tables
CREATE TABLE #Groups
(groups_pk1 INT)
CREATE TABLE #Ex
(column1 INT NOT NULL,
column2 VARCHAR(64) NOT NULL,
column3 INT IDENTITY(1, 1) NOT NULL)
-- populate preexisting sample data
INSERT INTO #Groups
SELECT 1234 UNION ALL
SELECT 4321
INSERT INTO #Ex
SELECT 1234, 'blog' UNION ALL
SELECT 1234, 'tasks' UNION ALL
SELECT 4321, 'blog'
-- your query
INSERT INTO #Ex
SELECT
#Groups.groups_pk1,
'tasks'
FROM
#Groups LEFT OUTER JOIN
#Ex ON #Groups.groups_pk1 = #Ex.column1 and 'tasks' = #Ex.column2
WHERE
#Ex.column1 IS NULL
-- clean up
DROP TABLE #Groups
DROP TABLE #Ex
May 31, 2012 at 6:15 am
Yeah that helps, how do you execute it on all instead of specifing 544?
Thanks
Ben
May 31, 2012 at 6:28 am
do you want to do it based on the values in column1 or based on the values in your groups table?
May 31, 2012 at 10:24 pm
bduff (5/31/2012)
Yeah that helps, how do you execute it on all instead of specifing 544?Thanks
Ben
This will work for you in all cases without specifying any value for Column1. You can insert the distinct Column1 values into a temp table with a default Column2 value 'tasks'. Then you can do an except on your main table and insert those column1 values which do not have 'tasks' in Column2.
--Creating Table
Create Table Ex
(Column1 int,
Column2 varchar(64),
Column3 int Identity(51284, 1) Primary Key)
--Inserting Sample Data
Insert into Ex
Select 1970, 'blogs'
Union ALL
Select 1970, 'collaboration'
Union ALL
Select 1970, 'discussion_board'
Union ALL
Select 1970, 'course_email'
Union ALL
Select 1970, 'file_exchange'
Union ALL
Select 1970, 'journal'
Union ALL
Select 544,'Bb-wiki'
Union ALL
Select 544,'blogs'
Union ALL
Select 544,'collaboration'
Union ALL
Select 544,'course_email'
Union ALL
Select 544,'discussion_board'
Union ALL
Select 544,'file_exchange'
Union ALL
Select 544,'journal'
--Insert Into Table using EXCEPT
Declare @temptable Table(Column1 int, Column2 varchar(64) )
Insert Into @temptable
Select Distinct Column1, 'Tasks' From Ex
Insert Into Ex
Select Column1, Column2 From @temptable
Except
Select Column1, Column2 From Ex Where Column2 = 'Tasks'
June 4, 2012 at 10:59 pm
I believe this will also do it for you, likely with only a single pass on the source table (other solutions probably do 2 passes):
Create Table #Ex
(Column1 int, Column2 varchar(64), Column3 int Identity(51284, 1) Primary Key)
Insert into #Ex
Select 1970, 'blogs' Union ALL Select 1970, 'collaboration' Union ALL
Select 1970, 'discussion_board' Union ALL Select 1970, 'course_email' Union ALL
Select 1970, 'file_exchange' Union ALL Select 1970, 'journal' Union ALL
Select 544,'Bb-wiki' Union ALL Select 544,'blogs'Union ALL Select 544,'collaboration' Union ALL
Select 544,'course_email'Union ALL Select 544,'discussion_board' Union ALL
Select 544,'file_exchange' Union ALL Select 544,'journal'
INSERT INTO #Ex
SELECT Column1, 'Tasks'
FROM #Ex
GROUP BY Column1
HAVING MAX(CASE Column2 WHEN 'Tasks' THEN Column2 ELSE NULL END) IS NULL
SELECT * FROM #Ex WHERE Column2 = 'Tasks'
DROP TABLE #Ex
Thanks to Vinu for the setup data.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 5, 2012 at 12:37 am
Now here's an interesting result. I took a slightly refactored version of Vinu's EXCEPT query and compared across a large test harness to mine.
Create Table #Ex
(Column1 int, Column2 varchar(64), Column3 int Identity(51284, 1) Primary Key)
;WITH SetupData (Column1, Column2) AS (
Select 1, 'blogs' Union ALL Select 1, 'collaboration' Union ALL
Select 1, 'discussion_board' Union ALL Select 1, 'course_email' Union ALL
Select 1, 'file_exchange' Union ALL Select 1, 'journal' Union ALL
Select 2,'Bb-wiki' Union ALL Select 2,'blogs'Union ALL Select 2,'collaboration' Union ALL
Select 2,'course_email'Union ALL Select 2,'discussion_board' Union ALL
Select 2,'file_exchange' Union ALL Select 2,'journal' UNION ALL
Select 3,'file_exchange' Union ALL Select 3,'Tasks'),
Tally (n) AS (
SELECT TOP 50000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2)
Insert into #Ex
SELECT n+Column1 * 1000000, Column2
FROM SetupData
CROSS APPLY Tally
SET STATISTICS TIME ON
SET STATISTICS IO ON
--INSERT INTO #Ex
SELECT Column1, 'Tasks'
FROM #Ex
GROUP BY Column1
HAVING MAX(CASE Column2 WHEN 'Tasks' THEN Column2 ELSE NULL END) IS NULL
--INSERT INTO #Ex
Select DISTINCT Column1, 'Tasks' From #Ex
Except
Select Column1, Column2 From #Ex Where Column2 = 'Tasks'
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
--SELECT * FROM #Ex WHERE Column2 = 'Tasks'
DROP TABLE #Ex
The new EXCEPT avoids the table variable and by taking advantage of the fact that EXCEPT only returns DISTINCT values. With the INSERTs commented out, here are the timing results (abbreviated for clarity).
(100000 row(s) affected)
SQL Server Execution Times:
CPU time = 1043 ms, elapsed time = 1312 ms.
(100000 row(s) affected)
SQL Server Execution Times:
CPU time = 829 ms, elapsed time = 1138 ms.
In other words, this version of the EXCEPT is about 20% faster than the GROUP BY option! A bit of a surprise to me anyway.
Edit: I had meant to remove DISTINCT from the EXCEPT query but found that it runs faster with it included.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 5, 2012 at 3:31 am
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply