To insert a value from a table into a warning statment

Question

Post reply

To insert a value from a table into a warning statment

SQLTestUser

Hall of Fame

Points: 3997
More actions
September 16, 2010 at 12:30 pm

#227998

To insert a value from a table into a warning statment

my script currently is as

Update table A

Set RptStatement = ' watever you comapnyName @2010'

where ( rptStatementId = 1 and rptStatementNO = 4)

So i have to create a new script for every new company i have to add and dispaly the report , now instead of doing this i want to create a script where the "watever you comapnyName " is derived from a table company name where the company Name has already been defined

Is there a way to do this can i include a select statement inside the Rpt Statement or how can i accomplish this

Please let me know if i have enot explained this properly

thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 1

avi-631555 (9/16/2010)

To insert a value from a table into a warning statment

my script currently is as

Update table A

Set RptStatement = ' watever you comapnyName @2010'

where ( rptStatementId = 1 and rptStatementNO = 4)

So i have to create a new script for every new company i have to add and dispaly the report , now instead of doing this i want to create a script where the "watever you comapnyName " is derived from a table company name where the company Name has already been defined

Is there a way to do this can i include a select statement inside the Rpt Statement or how can i accomplish this

Please let me know if i have enot explained this properly

thanks

You haven't given us any schema or sample data to understand the problem completely, but to begin with, data from columns can be combined with character data as shown in the code below. There are a number of ways to get the column data: You can join to the table that contains the company names (presumably joining on a company number or ID). Or you can write a subquery to get

the company.



declare @company table (companyID int identity(1,1) primary key, companyName varchar(30))

insert into @company

select 'My Company' union all

select 'Your Company'

select companyName+' @2010'

from @company

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 2

did you try a joined solution like ...



Update A

    Set RptStatement = ' watever you ' + C.comapnyName + ' @' + cast (year(getdate()) as char(4))

from TableA A

, Companies C

where ( A.rptStatementId = 1 and A.rptStatementNO = 4)

-- just to restrict the scope

and A.RptStatement <> ' watever you ' + C.comapnyName + ' @' + cast (year(getdate()) as char(4))

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 3

First of all thanks for the scripr and i ran it this way as shown below but the company name and confidential are diaplyed together can i insert a break in between like an html break to seperate the two or is there a different way to do this

Update Astatements

Set AStatement = C.companyName + ' Confidential '+ ' @' + cast (year(getdate()) as char(4)) + C.companyName + ',Corporation. '

from AStatements W

, Company C

where ( W.AName = 'AWarn' and W.ANo = 1)

-- just to restrict the scope

and W. AStatement <> C.companyName + 'Confidential '+ ' @' + cast (year(getdate()) as char(4)) + C.companyName + ' ,Corporation. '

When i do this it shows up as

CompanyName Confidential @2010companyName,Corporation.

Also what does char(4) server here

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 4

The CHAR(4) is a datatype, as opposed to INT or VARCHAR or DATETIME, etc. It indicates that the 4 digits of the years are being stored as a character string 4 characters long.

Try using char(13) for a new line.

Also, SET statements just assign values to single (scalar) variables. For multiple rows you should be using SELECT.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 5

SQLTestUser

Hall of Fame

Points: 3997

September 16, 2010 at 2:40 pm

#1222614

I think i know how to perform the spaces now i tried it on an insert into can we perfrom this on an insert Into statement

So for a statement like

Insert into ATable

value ( Employees of a "CompanyName from Company table" will get the same email " from "CompanyName from Company table" )

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 6

SQLTestUser

Hall of Fame

Points: 3997

September 16, 2010 at 3:30 pm

#1222664

I think that would be impossible ( what i asked for ) only scalar variables are allowed in an insert and no subqueries as i have to enter the query into a statement unless anyone has a different approach

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 7

only scalar variables are allowed in an insert

Pardon me, are we talking about something other than T-SQL?

I ask because in T-SQL there is no such limitation. You can absolutely use a SELECT with an INSERT in T-SQL.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 8

SQLTestUser

Hall of Fame

Points: 3997

September 16, 2010 at 4:27 pm

#1222681

Well i am not getting it then if u can explain ti that would be awesome this is what i am trying to accomplish

Insert Into values ( 1,2, 'A warning statement for the user " Select A from B table" ' )

Please let me know if i didnot explain properly

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 9

The VALUES clause is one source of data to be inserted, but a SELECT clause can also be a source of data for an INSERT.

See the example below.



declare @source table (ID int, data varchar(30))

declare @target table (ID int identity(1,1), data varchar(30))

-- using VALUES clause to insert data

insert into @source

values	(4, 'oranges' ),

	    (2, 'apples'),

	    (6, 'melons');

select '@source' as 



,ID,data from @source

-- using SELECT clause to insert data, adding text

insert into @target

select data+' Hi Mom'

from @source

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 10

Thanks again for the clarification this is what i tried

IF EXISTS(SELECT * from A where where AID ='4')

Begin

Print 'The EmpID alreay exist'

END

ELSE

Begin

Insert Into Emp (AID,BName,SName,IsActive)

Values (4,'this','that' ,1)

Print 'Added the Name to the Emp Table '

End

"This" and "that" in the insert statement shoould be selected from EmpNAmes table where it is already defined by a previous script. Now this has to be dont without making a stored procedure. is this a way i can perform this without dcalring any variables ??

thanks again

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 11

You sure can.

I'm not sure why we're stuck on using a values clause to do this, but the expressions in a value clause may include sub-queries so long as they only return one row containing one value.

See the example below.



declare @target table  (name varchar(10), data1 varchar(50), data2 varchar(50))

insert into @target

values ('Bob', (select top 1 name from sys.objects order by name ), (select top 1 name from sys.columns order by name))

select * from @target

I now understand what you were trying to say about scalar variables. Understand that is not really an INSERT limitation. Its easy to use INSERT INTO/SELECT to insert multiple rows at a time. However, the expressions in the VALUES clause must be scalar.

Here are other ways to write this without using a VALUES clause.



declare @target table  (name varchar(10), data1 varchar(50), data2 varchar(50))

insert into @target

values ('Bob', (select top 1 name from sys.objects order by name), (select top 1 name from sys.columns order by name))

select * from @target

insert into @target

select 'Bob',(select top 1 name from sys.objects order by name), (select top 1 name from sys.columns order by name)

select * from @target

insert into @target

select top 1 'Bob', name, name2

from sys.objects

cross apply (select top 1 name from sys.columns order by name) ca (name2)

order by name 

select * from @target

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills