Solved

Instert statment question

Posted on 2013-11-05
8
185 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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.…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

759 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

24 Experts available now in Live!

Get 1:1 Help Now