Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Instert statment question

Posted on 2013-11-05
8
Medium Priority
?
193 Views
Last Modified: 2013-11-20
I have two tables below.  I need to create a SQL insert script that will insert into table2.  I need to insert Sue and Sam with uids of '89' and values of 'false' while the id needs to be pulled from table1.  I also need to insert Bob and Jim with uids of '89' and values of 'false'.  Leaving Tom alone since he is already setup correctly in Table 2.  

table1
 1
table2
 2
What table2 needs to look like after the insert.
3
0
Comment
Question by:abarefoot
  • 3
  • 2
8 Comments
 
LVL 6

Assisted Solution

by:Argenti
Argenti earned 1000 total points
ID: 39625924
Hi there,

SQL Server doesn't have a Boolean column type (like Access has) so we'll consider 'value' as tinyint, having 0 and 1 as False and True values.

Table creation:
CREATE TABLE table1 (
	ID nvarchar(20) NOT NULL
)
GO
CREATE TABLE table2(
	ID nvarchar(20) NOT NULL,
	[uid] int NOT NULL,
	[value] tinyint NOT NULL
)

Open in new window


Initialization
insert into table1(ID) values('Bob');
insert into table1(ID) values('Tom');
insert into table1(ID) values('Jim');
insert into table1(ID) values('Sam');
insert into table1(ID) values('Sue');

insert into table2(ID, [uid], [value]) values('Bob', 14, 1);
insert into table2(ID, [uid], [value]) values('Tom', 89, 0);
insert into table2(ID, [uid], [value]) values('Jim', 55, 1);

Open in new window


And the statement you asked for:
insert into table2(ID, uid, value)
select ID, 89, 0 from table2 where (uid <> 89)
union
select ID, 89, 0 from table1 where ID not in (select ID from table2);

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39627184
Argenti is correct.

<Minor point>SQL Server doesn't have a Boolean column type (like Access has)
SQL has a bit datatype that holds 0 and 1.   A common source of confusion is that Access represents True as -1 and False as 0, whereas SQL Server represents True as 1 and False as 0.
0
 
LVL 1

Author Comment

by:abarefoot
ID: 39663958
I've requested that this question be deleted for the following reason:

Don't need this anymore.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:abarefoot
ID: 39663868
I have since figured this out.
0
 
LVL 1

Accepted Solution

by:
abarefoot earned 0 total points
ID: 39663903
I used a trigger on the users table.  So  when a new user is created it will add the info I need to the preference table.  Below is the trigger I'm using.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[t_users_addpreference_i] ON [dbo].[users]
FOR INSERT
AS
IF ( @@rowcount = 0 )
RETURN

SET NOCOUNT ON

INSERT INTO user_preference
( preference_uid
, value
, date_created
, created_by
, date_last_modified
, last_maintained_by
, user_id
)
SELECT '89'
, 'false'
, GETDATE()
, 'sytem'
, GETDATE()
, 'system'
, inserted.id
FROM inserted
LEFT OUTER JOIN user_preference ON inserted.id = user_preference.user_id AND user_preference.preference_uid = 89
WHERE user_preference.user_id IS NULL
0
 
LVL 6

Expert Comment

by:Argenti
ID: 39664277
Thanks
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question