inserting rows in 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

General SQL Server Forums

New to SQL Server Programming

inserting rows in table

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-10-15 : 04:23:33

I have a 2 column's called usergroupid and contactid in a table.

I would like all contactid's usergroupid with 1 to have usergroupid also as 2 and all usergroupid with 2 to also have usergroupid as 1)

contactid is not unique.
eg.the current table might look like this:
ContactToUserGroup table
contactid usergroupid
1 1
2 1
3 2
4 2
1 3

to:
1 1
1 2
2 1
2 2
3 2
3 1
4 2
4 1
1 3

Is this how to do it(then switch the 2 numbers)?


INSERT INTO dbo.ContactToUserGroup (
            ContactId,
            UserGroupId
      )
      SELECT ContactToUserGroup.contactid, 1
      FROM ContactToUserGroup 
      WHERE UserGroupId = 2

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-15 : 05:12:40

You can do your SELECT without the INSERT part to see what happens.

SELECT ContactToUserGroup.contactid, 1
FROM ContactToUserGroup
WHERE UserGroupId = 2
union all
SELECT ContactToUserGroup.contactid, 2
FROM ContactToUserGroup
WHERE UserGroupId = 1

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-15 : 05:14:39

No.
Do it without the union all.
The first insert would affect the second select.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-15 : 05:16:53

Build a testtable by yourself and insert the given example data and then you can try it out by yourself.
If then there is a problem come back and ask.

Is this ok for you?

No, you're never too old to Yak'n'Roll if you're too young to die.

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-10-15 : 05:35:12

quote:
Originally posted by webfred

Build a testtable by yourself and insert the given example data and then you can try it out by yourself.
If then there is a problem come back and ask.

Is this ok for you?

No, you're never too old to Yak'n'Roll if you're too young to die.

OK will test it and get back to you if i have problems

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-10-15 : 05:53:22

quote:
Originally posted by webfred

Build a testtable by yourself and insert the given example data and then you can try it out by yourself.
If then there is a problem come back and ask.

Is this ok for you?

No, you're never too old to Yak'n'Roll if you're too young to die.

You are right, the first inserts 2 rows which is correct but the second inserts 4 because it duplicates the 2 rows in the first insert. How would i manage that?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-15 : 06:02:11

Please post your create, insert and whatever statements so we can copy and paste to try it out.

No, you're never too old to Yak'n'Roll if you're too young to die.

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-10-15 : 06:14:48

quote:
Originally posted by webfred

Please post your create, insert and whatever statements so we can copy and paste to try it out.

No, you're never too old to Yak'n'Roll if you're too young to die.

Ok here is the code to Create table, insert initial values then the 2 insert statements. The first will work but the second doesn't.



CREATE TABLE dbo.ContactGroupLink
      (contactLinkId INT IDENTITY(1,1),
      contactId INT,
      groupId INT)
      
      INSERT INTO dbo.ContactGroupLink (	  	
	  	contactId,
	  	groupId
	  ) VALUES ( 1,1)
	  INSERT INTO dbo.ContactGroupLink (	  	
	  	contactId,
	  	groupId
	  ) VALUES ( 2,1)
	  INSERT INTO dbo.ContactGroupLink (	  	
	  	contactId,
	  	groupId
	  ) VALUES ( 3,2)
	  INSERT INTO dbo.ContactGroupLink (	  	
	  	contactId,
	  	groupId
	  ) VALUES ( 4,2)
	  INSERT INTO dbo.ContactGroupLink (	  	
	  	contactId,
	  	groupId
	  ) VALUES ( 1,3)
      
      INSERT INTO dbo.ContactGroupLink (
            ContactId,
            GroupId
      )
      SELECT dbo.ContactGroupLink.contactid, 2
      FROM ContactGroupLink 
      WHERE GroupId = 1
      
      INSERT INTO dbo.ContactGroupLink (
            ContactId,
            GroupId
      )
      SELECT dbo.ContactGroupLink.contactid, 1
      FROM ContactGroupLink 
      WHERE GroupId = 2

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-15 : 07:30:01

I think this is it, please check it

CREATE TABLE dbo.ContactGroupLink
      (contactLinkId INT IDENTITY(1,1),
      contactId INT,
      groupId INT)
      
      INSERT INTO dbo.ContactGroupLink (	  	
	  	contactId,
	  	groupId
	  ) VALUES ( 1,1)
	  INSERT INTO dbo.ContactGroupLink (	  	
	  	contactId,
	  	groupId
	  ) VALUES ( 2,1)
	  INSERT INTO dbo.ContactGroupLink (	  	
	  	contactId,
	  	groupId
	  ) VALUES ( 3,2)
	  INSERT INTO dbo.ContactGroupLink (	  	
	  	contactId,
	  	groupId
	  ) VALUES ( 4,2)
	  INSERT INTO dbo.ContactGroupLink (	  	
	  	contactId,
	  	groupId
	  ) VALUES ( 1,3)
      

select * from ContactGroupLink


      INSERT INTO dbo.ContactGroupLink (
            ContactId,
            GroupId
      )
      SELECT t1.contactid, 2
      FROM ContactGroupLink t1
      WHERE t1.GroupId = 1
      and not exists(select * from ContactGroupLink t2
                     where t2.contactid = t1.contactid
                       and t2.GroupId = 2)
      
select * from ContactGroupLink

      INSERT INTO dbo.ContactGroupLink (
            ContactId,
            GroupId
      )
      SELECT t1.contactid, 1
      FROM ContactGroupLink t1
      WHERE GroupId = 2
            and not exists(select * from ContactGroupLink t2
                     where t2.contactid = t1.contactid
                       and t2.GroupId = 1)

select * from ContactGroupLink

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-15 : 07:32:14

And btw. this is the best way to ask a question.
Now you have given table structure, sample data and wanted output(in first post).
So we can copy, paste and try to find a solution without doing that all for you

No, you're never too old to Yak'n'Roll if you're too young to die.

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-10-15 : 09:12:35

quote:
Originally posted by webfred

And btw. this is the best way to ask a question.
Now you have given table structure, sample data and wanted output(in first post).
So we can copy, paste and try to find a solution without doing that all for you

No, you're never too old to Yak'n'Roll if you're too young to die.

Great, thanks for the solution and point taken. Next time I can't solve a problem I will create the table etc

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-15 : 09:14:56

Fine, you're welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources