March 20, 2017 at 4:21 pm
Hey guys,
need your briliant minds, sadly i dont have one 🙁 and sorry for the confused title.
ok i have a select statement like:
select OrderID, customer name, blah blah
from dbo.orders
however i am using this select statement in BIML, but dont let that throw anyone off, i am trying to say:
select *
from dbo.orders
where orders in
(
select name
from sys.tables
where name like 'orders'
)
therefore it will complete the select statement, however if it wasnt an order table, then skip the select statement, i am really sorry if i am not explaining it right, but that is what i can think on top of my head how i can accomplish my task that BIML requires.
what the main purpose is to dynamically do select statements for only certain tables in a database, if it meets my criteria, then do the select statement, if not, skip it.... is this possible?
thanks in advance
March 20, 2017 at 5:19 pm
Siten0308 - Monday, March 20, 2017 4:21 PMHey guys,need your briliant minds, sadly i dont have one 🙁 and sorry for the confused title.
ok i have a select statement like:
select OrderID, customer name, blah blah
from dbo.ordershowever i am using this select statement in BIML, but dont let that throw anyone off, i am trying to say:
select *
from dbo.orders
where orders in
(
select name
from sys.tables
where name like 'orders'
)therefore it will complete the select statement, however if it wasnt an order table, then skip the select statement, i am really sorry if i am not explaining it right, but that is what i can think on top of my head how i can accomplish my task that BIML requires.
what the main purpose is to dynamically do select statements for only certain tables in a database, if it meets my criteria, then do the select statement, if not, skip it.... is this possible?
thanks in advance
Dynamically, based on what?
Do you want a loop which iterates round all tables containing the word 'Orders' and selects all rows from them?
Like 'Orders' is the same as = 'Orders', because you have not included any wildcards.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 20, 2017 at 5:38 pm
I think what you want is something like:
IF EXISTS(SELECT * FROM sys.tables WHERE name = 'Orders')
SELECT *
FROM Orders;
March 20, 2017 at 5:49 pm
Siten0308 - Monday, March 20, 2017 4:21 PMHey guys,need your briliant minds, sadly i dont have one 🙁 and sorry for the confused title.
ok i have a select statement like:
select OrderID, customer name, blah blah
from dbo.ordershowever i am using this select statement in BIML, but dont let that throw anyone off, i am trying to say:
select *
from dbo.orders
where orders in
(
select name
from sys.tables
where name like 'orders'
)therefore it will complete the select statement, however if it wasnt an order table, then skip the select statement, i am really sorry if i am not explaining it right, but that is what i can think on top of my head how i can accomplish my task that BIML requires.
what the main purpose is to dynamically do select statements for only certain tables in a database, if it meets my criteria, then do the select statement, if not, skip it.... is this possible?
thanks in advance
First let me tell you that I don't know BIML.
That said, why would you want to do this? If you already know the names of the tables, you can query them. If you don't know the names of the tables, why would you want to query them?
March 21, 2017 at 4:33 pm
If you want to select records from all tables that exist where the table name is like ‘orders’ you would need to iterate through a list of table names that meet the criteria, pass in the table name as a variable, then run a select statement using the variable as a table name. To do this you would need to use a loop.
declare @name as varchar(128)
DECLARE cur CURSOR
FOR
select name from sys.tables
where name like '%order%'
OPEN cur FETCH NEXT FROM cur INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
--I have used a top 5 condition to limit results
exec('select top 5 * from ' + @name)
FETCH NEXT FROM cur INTO @name
END
CLOSE cur
DEALLOCATE cur
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply