To insert a value from a table into a warning statment

  • 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

  • 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

  • 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

  • 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 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

  • 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" )

  • 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

  • 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

  • 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 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

  • 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

  • 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

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

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