Solved

Instert statment question

Posted on 2013-11-05
8
186 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now