Solved

Instert statment question

Posted on 2013-11-05
8
188 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 250 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 65

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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

856 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