Insert into and set

Question

Insert into and set

_taz_

Ten Centuries

Points: 1225
More actions
August 5, 2010 at 9:08 am

#225128

i'm trying to insert combined values from one table into another. will I have to create a temp table first, combine and then insert?
need to combine column from Clients ClientName and column ClientID into table ClientAlias column ClientAlias while separating with a space and ().
so clientname = John Doe clientid=1234 would look like John Doe (1234)
I thought about Insert into and Set, but i have no idea how to do a set with a select.
this is what I was thinking but I know it's not correct
Insert into ClientAlias(ClientAlias,ClientID,defaultalias,enrolled)
Select Clients.ClientID,Clients.Name
From Clients
where Clients.DateStart='1/1/2008'
Set ClientAlias=Clients.Name & ' (' & Clients.ClientID & ')'
,ClientID=Clients.ClientID
,enrolled=0
,defaultalias=-1

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Just make sure the SELECT portion of this statement gives you the correct results, then combine with the INSERT and let it roll.

INSERT INTO ClientAlias(ClientAlias,ClientID,defaultalias,enrolled)

SELECT Clients.Name & ' (' & Clients.ClientID & ')' as ClientAlias,

Clients.ClientID,

-1 as defaultalias,

0 as enrolled

FROMClients

WHEREClients.DateStart='1/1/2008'

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

_taz_ Ten Centuries Points: 1225 More actions · Answer 2

no i get "The data types nvarchar and varchar are incompatible in the '&' operator."

clientname is nvarchar(50) and clientid is int

_taz_ Ten Centuries Points: 1225 More actions · Answer 3

nvrmnd used cast(clientid as nvarchar(10))

of course doing it that way I found I have a lot of empty spaces after some names, gotta remove all white space from the right side of clientname first.

_taz_ Ten Centuries Points: 1225 More actions · Answer 4

hmmm... the select statement works now, but when I add it to the insert I get

"Conversion failed when converting the nvarchar value 'Jim Beam' to data type int."

don't really understand, I'm not trying to convert that to int??

John Rowan SSC Guru Points: 56440 More actions · Answer 5

My bad.....replace the & with +......

INSERT INTO ClientAlias(ClientAlias,ClientID,defaultalias,enrolled)

SELECT Clients.Name + ' (' + Clients.ClientID + ')' as ClientAlias,

Clients.ClientID,

-1 as defaultalias,

0 as enrolled

FROM Clients

WHERE Clients.DateStart='1/1/2008'

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

_taz_ Ten Centuries Points: 1225 More actions · Answer 6

already did that. still don't know why it thinks I'm converting nvarchar to int though

John Rowan SSC Guru Points: 56440 More actions · Answer 7

Ah, your ClientID column needs cast.

INSERT INTO ClientAlias(ClientAlias,ClientID,defaultalias,enrolled)

SELECT Clients.Name + ' (' + CAST(Clients.ClientID as varchar) + ')' as ClientAlias,

Clients.ClientID,

-1 as defaultalias,

0 as enrolled

FROM Clients

WHERE Clients.DateStart='1/1/2008'

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

_taz_ Ten Centuries Points: 1225 More actions · Answer 8

nevermind, i'm an idiot had clientname going into clientid field. (should have just copied and pasted...)