Solved

Instert statment question

Posted on 2013-11-05
8
189 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
Technology Partners: 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

Independent Software Vendors: 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!

Question has a verified solution.

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

Suggested Solutions

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

679 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