none
Ajuda!!!! Select + while RRS feed

  • Pergunta

  •  

    Bom tarde  galera...

    Tenho meu banco de dados SQL Server 2005 e estou desenvonvendo em C# 2005 aplicação desktop, onde preciso fazer com um While, para que este While percorra todas as tabelas do meu banco de dados Historico, precisarei fazer a contagem total de refeição durante um determinado período, mas essas tabelas são geradas no BD todos os dias, são geradas com um inicio SQl+data do dia atual, preciso fazer contagem do total de refeição mas preciso que meu select percorra todas as tabelas, tentei fazer um select especifico para que percorre-se todas as tabelas do meu database Historico mas não consegui.

     

    SELECT TotalRefeicao = count(SUBSTRING(mensagem, 3, 3)),

    SUBSTRING(mensagem, 31, 10) as Cartão

    FROM INFORMATION_SCHEMA.TABLES CROSS JOIN

    SQL+DateTime.Today.Date.ToString("yyyyMMdd")

    WHERE INFORMATION_SCHEMA.TABLES.TABLE_NAME LIKE 'SQL%'

    AND (SUBSTRING(mensagem, 25, 6) BETWEEN '05/01/2009'

    AND DATEADD(W, 1, '06/01/2009')) GROUP BY data

     

    Fiz desta forma, mas não consegui, por que desta forma o select pega os dados somente com a tabela da data de hoje.

     

    Gostaria se algum puder dar alguma sugestão de como poderia ser feito com “While” ou de outra maneira para que conseguisse fazer o total de refeição durante um determinado período. Estou fazendo desta maneira, e infelizmente não posso mudar a criação das tabelas no BD, todos os dias vão criadas novas tabelas. 

    public DataSet PesquisaRefeicaoDt(string DataRef, string DataRef2)

    {

    DataSet LeDados = null;

    SqlCommand ti = null;

    string sSQL = null;

    clAcessoBDSQL AcessoBD = new clAcessoBDSQL();

    if (AcessoBD.ConectaBDHis())

    {

    sSQL = "SELECT TotalRefeicao = count(SUBSTRING(mensagem, 3, 3)), " +

    //SUBSTRING(mensagem,3,3) ‚ local onde o usuario passou ex:catraca e será  salvo no Historico do BD

    " SUBSTRING(mensagem, 31, 10) as Cartão " +

    " FROM INFORMATION_SCHEMA.TABLES CROSS JOIN SQL20090105 " +

    " WHERE INFORMATION_SCHEMA.TABLES.TABLE_NAME LIKE 'SQL%'" +

    " AND data BETWEEN '" +

    DataRef + "' AND DATEADD(W, 1,'" +

    DataRef2 + "') GROUP BY data ";

     

    ti = new SqlCommand();

    ti.CommandText = sSQL;

    LeDados = AcessoBD.ExecutaComandos(sSQL);

    AcessoBD.FechaBDHis();

    }

    return LeDados;

    }

     

    //Onde vou chamar a meu método PesquisaRefeicaoDt

    if ((txtDataRef.Text != "") && (txtDataRef2.Text != ""))

    {

    ClRelatorio objRelat = new ClRelatorio();

    DataSet DataRelatorio = null;

    DataRelatorio = objRelat.PesquisaRefeicaoDt(txtDataRef.Text, txtDataRef2.Text);

    this.Text = DataRelatorio.Tables[0].Rows[0].ItemArray[1].ToString();

    }

     

    Obrigada desde já

     

     

    quarta-feira, 7 de janeiro de 2009 16:47

Respostas

  • Boa Tarde,

     

    Eu sinceramente sugiro que você reveja a arquitetura da aplicação. Criar novas tabelas todos os dias com a mesma estrutura irá lhe trazer alguns problemas:

     

    Dificuldade na elaboração das consultas

    Toda vez que você precisar referir-se a tabelas precisará construir automaticamente a instrução para capturar o nome da tabela e executá-la. Isso levará a dificuldades de implementação e manutenção das consultas.

     

    Dificuldades na manutenção do backup

    Se todos os dias são criadas tabelas novas, o banco irá crescer e as tabelas antigas irão conter dados estáticos. Esses dados estáticos possivelmente não irão mudar e todos os dias você irá realizar o backup desses dados incorrendo em janelas de backup maiores, maior espaço do backup, mais fita, etc.

     

    Dificuldades de implementação e manutenção das dependências

    Para cada tabela criada será necessário criar novos relacionamentos com outras tabelas todos os dias. Isso pode levar a situações cada mais trabalhosas (senão impossíveis) de manter as integridades referenciais

     

    Dificuldades de administração

    Para cada tabela poderá necessário recriar as permissões de acesso incorrendo em mais trabalho. Se você trabalhar com roles como db_datareader ou db_owner poderá resolver esse problema, mas pode estar abrindo brechas de segurança.

     

    [ ]s,

     

    Gustavo

    http://gustavomaiaaguiar.spaces.live.com

    quarta-feira, 7 de janeiro de 2009 17:58
  • Olá Mary Sistemas,

     

    É importantíssimo que sistemas grandes comecem corretamente, pois, corrigir um problema arquitetural desses para algo muito grande pode ser muito difícil senão proibitivo.

     

    Entendo perfeitamente a preocupação se há milhares de registros todos os dias, mas não acho que justifique a implementação dessa forma. Sugira ao seu chefe que caso ele possua o SQL Server 2005 Enterprise que ele opte pelos recursos de particionamento. Supondo que você pesquisará por dia, particionar com base na data lhe concederia todos os benefícios de tabelas separadas sem as notórias desvantagens.

     

    [ ]s,

     

    Gustavo

    http://gustavomaiaaguiar.spaces.live.com

    quarta-feira, 7 de janeiro de 2009 18:24
  • Olá Mary,

     

    Acho que isso aqui ajuda:

     

    Code Snippet

    select * from sys.objects

    where

    type = 'U'

    and is_ms_shipped = 0

    and create_date between '20081201' and '20081231'

     

     

    Daí você especifica qual período você quer pegar as tabelas criadas.

     

    Daí basta fazer um inner join ou subconsulta. Com esta consulta, você consegue pegar o nome das tabelas criadas num determinado período.

     

    Abraços

     

     

     

    quarta-feira, 7 de janeiro de 2009 18:43
  •  

    Olá Mary,

     

    A idéia seria consultar atravéz do sql que postei e só assim aplicar o que você deseja:

     

    Code Snippet

    select t.*

    from 

    suaTabela t left join

    (

    SELECT o.name

    FROM sys.objects o

    WHERE

    o.type = 'U'

    AND o.is_ms_shipped = 0

    AND o.create_date BETWEEN '20090105' AND '20090109'

    ) objeto

    on objeto.name = t.nome collate SQL_Latin1_General_CP1_CI_AS

     

     

    Com o comando acima, você consegue recuperar todas as tabelas criadas num período. Daí, basta trocar o t.* pelo comando

     

    "COUNT(SUBSTRING(mensagem, 10, 3))

    AS TotalRefeicao, SUBSTRING(mensagem,10, 3) as TipoMensagem"

     

    Ou outro qualquer

     

    Abraços

    sexta-feira, 9 de janeiro de 2009 19:12

Todas as Respostas

  • Olá pelo que entendi novas tabelas são criadas no BD todos os dias. Porque?

     

    Abraços

     

    quarta-feira, 7 de janeiro de 2009 17:42
  • Boa Tarde,

     

    Eu sinceramente sugiro que você reveja a arquitetura da aplicação. Criar novas tabelas todos os dias com a mesma estrutura irá lhe trazer alguns problemas:

     

    Dificuldade na elaboração das consultas

    Toda vez que você precisar referir-se a tabelas precisará construir automaticamente a instrução para capturar o nome da tabela e executá-la. Isso levará a dificuldades de implementação e manutenção das consultas.

     

    Dificuldades na manutenção do backup

    Se todos os dias são criadas tabelas novas, o banco irá crescer e as tabelas antigas irão conter dados estáticos. Esses dados estáticos possivelmente não irão mudar e todos os dias você irá realizar o backup desses dados incorrendo em janelas de backup maiores, maior espaço do backup, mais fita, etc.

     

    Dificuldades de implementação e manutenção das dependências

    Para cada tabela criada será necessário criar novos relacionamentos com outras tabelas todos os dias. Isso pode levar a situações cada mais trabalhosas (senão impossíveis) de manter as integridades referenciais

     

    Dificuldades de administração

    Para cada tabela poderá necessário recriar as permissões de acesso incorrendo em mais trabalho. Se você trabalhar com roles como db_datareader ou db_owner poderá resolver esse problema, mas pode estar abrindo brechas de segurança.

     

    [ ]s,

     

    Gustavo

    http://gustavomaiaaguiar.spaces.live.com

    quarta-feira, 7 de janeiro de 2009 17:58
  • Mary,

     

    Qual é a necessidade de ter tantas tables sendo criadas diariamente?

     

    quarta-feira, 7 de janeiro de 2009 17:59
  • Com certeza Gustavo,

     

    Até porque isso meio que desfaz a idéia de Banco de Dados. Seria mais um banco de dados do banco de dados.

     

    Abraços

    quarta-feira, 7 de janeiro de 2009 18:02
  • Boa Tarde,

     

    Só faltou um exemplo. Talvez você queira encapsular em uma SP posteriormente.

     

    Code Snippet

    CREATE TABLE TB20090101 (C1 INT, C2 INT, C3 INT, C4 INT)

    CREATE TABLE TB20090102 (C1 INT, C2 INT, C3 INT, C4 INT)

    CREATE TABLE TB20090103 (C1 INT, C2 INT, C3 INT, C4 INT)

    CREATE TABLE TB20090104 (C1 INT, C2 INT, C3 INT, C4 INT)

    CREATE TABLE TB20090105 (C1 INT, C2 INT, C3 INT, C4 INT)

    CREATE TABLE TB20090106 (C1 INT, C2 INT, C3 INT, C4 INT)

    CREATE TABLE TB20090107 (C1 INT, C2 INT, C3 INT, C4 INT)

     

    DECLARE @listaCampos VARCHAR(200), @cmdSQL VARCHAR(MAX), @inicio CHAR(10), @fim CHAR(10)

    SET @listaCampos = 'SELECT C1, C2, C3, C4 FROM '

    SET @inicio = '20090101'

    SET @fim = '20090107'

    SET @cmdSQL = ''

     

    -- Monta a instrução de execução dinamicamente

    DECLARE cPesquisa CURSOR FAST_FORWARD FOR

    SELECT @listaCampos + SCHEMA_NAME(SCHEMA_ID) + '.' + NAME FROM SYS.TABLES

    WHERE RIGHT(NAME,8) BETWEEN @inicio AND @fim

    ORDER BY SCHEMA_NAME(SCHEMA_ID)

     

    OPEN cPesquisa

     

    DECLARE @val VARCHAR(300)

    FETCH NEXT FROM cPesquisa INTO @val

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @cmdSQL = @cmdSQL + @val + ' UNION ALL' + CHAR(10)

    FETCH NEXT FROM cPesquisa INTO @val

    END

    SET @cmdSQL = LEFT(@cmdSQL,LEN(@cmdSQL) - 11)

     

    EXEC(@cmdSQL)

     

    CLOSE cPesquisa

    DEALLOCATE cPesquisa

     

    Ainda que funcione, não recomendo.

     

    [ ]s,

     

    Gustavo

    http://gustavomaiaaguiar.spaces.live.com

    quarta-feira, 7 de janeiro de 2009 18:16
  • Oi Demétrio,

     

    Justamente. Para essas situações seria melhor trabalhar uma coluna para identificar o dia do que criar tantas tabelas.

     

    [ ]s,

     

    Gustavo

    http://gustavomaiaaguiar.spaces.live.com

    quarta-feira, 7 de janeiro de 2009 18:17
  • Obrigada, Gustavo

     

    Sim, infelizmente vou precisar criar as tabelas todos os dias, foi a recomendação do meu chefe, falei da minha dificuldade para criar os select, já que serão geradas tabelas todas os dias.

    Não podemos mudar para uma única tabela, pois quando vamos fazer um select para pesquisar algum registro, ficara muito lento. Precisamos criar as tabelas todos dias pois milhares de registros que serão salvos no BD...

    Não tenho mais idéia de como fazer tudo isso...

     

    Se puder me ainda

     

    quarta-feira, 7 de janeiro de 2009 18:19
  • Mary,

     

    Então a necessidade de se criar várias tables todos os dias é devido ao volume de informações?

    quarta-feira, 7 de janeiro de 2009 18:22
  • Obrigada a todos...

     

    Sim todos os dias são salvos milhares de registros...
    quarta-feira, 7 de janeiro de 2009 18:24
  • Olá Mary Sistemas,

     

    É importantíssimo que sistemas grandes comecem corretamente, pois, corrigir um problema arquitetural desses para algo muito grande pode ser muito difícil senão proibitivo.

     

    Entendo perfeitamente a preocupação se há milhares de registros todos os dias, mas não acho que justifique a implementação dessa forma. Sugira ao seu chefe que caso ele possua o SQL Server 2005 Enterprise que ele opte pelos recursos de particionamento. Supondo que você pesquisará por dia, particionar com base na data lhe concederia todos os benefícios de tabelas separadas sem as notórias desvantagens.

     

    [ ]s,

     

    Gustavo

    http://gustavomaiaaguiar.spaces.live.com

    quarta-feira, 7 de janeiro de 2009 18:24
  • Concordo com o Gustavo,

     

    Mary que tipos de dados serão gerados? Serão milhares mesmo por dia ?

     

    Abraços

     

    quarta-feira, 7 de janeiro de 2009 18:28
  • Olá Gustavo  o que ocorre é o seguinte:

    fazemos controle de segurança que gerencia centrais remotas e nelas gerenciamos os eventos atraves de blocos como essse abaixo:

     

    esta é a remota que controla a entrada/saida de funcionarios na empresa

     

    <\\00102030250001021552502010080AC53AAB60/>                                        

     

     

    001 – 3 digitos da central

    13  - mensagem (0203 cor e fonte, 025 tipo da mensagem )

    06 – hora, min, seg

    06  - data dia, mês, ano

    10 – numero do cartao

     

    como tb possui blocos diferentes que sao eventos de alarmes, catraca, portas etc...

    todos estes eventos sao armazanados por dia em tabelas como ja mencionei acima

    a questão que vcs me aletaram eu concordo plenamente ... mas como ja disse ele nao quer modificar a estrutura eu tenho que trabalhar desta forma.

     

    como que eu iria fazer uma consulta pra buscar as informações necessarias percorrendo todas as tabelas geradas.

     

    muito Obrigada a todos

     

    Mary

     

    quarta-feira, 7 de janeiro de 2009 18:34
  • Olá Mary,

     

    Acho que isso aqui ajuda:

     

    Code Snippet

    select * from sys.objects

    where

    type = 'U'

    and is_ms_shipped = 0

    and create_date between '20081201' and '20081231'

     

     

    Daí você especifica qual período você quer pegar as tabelas criadas.

     

    Daí basta fazer um inner join ou subconsulta. Com esta consulta, você consegue pegar o nome das tabelas criadas num determinado período.

     

    Abraços

     

     

     

    quarta-feira, 7 de janeiro de 2009 18:43
  • Pois é mary...

    estou com uma duvida semelhante

    é o mesmo formato do  bloco só que eu tenho que selecionar e calcular o numero de visitantes que acessaram a empresa atraves deste bloco.

     

    pegar o numero do cartao, e verificar por data

    quantos visitantes por dia

    por semana , mes e o total

     

    já tentei utilizar um select com o between mas se eu mando por exemplo pesquisar assim 07/01/2009 and 07/01/2009

    ele nao encontra nenhum registro

     

    se alguem puder me ajudar

     

    Obrigada

    Tâninha

     

    quarta-feira, 7 de janeiro de 2009 19:20
  • Ola Demétrio,

     

    Fiz deste select abaixo, mas não esta fazendo o calculo corretante, ele soma sempre uma quantidade a mais, estou tentando verificar o que esta errado...

    Mas estou com um grande problema, consigo fazer a soma entre duas datas, mas somente se o nome da tabela estiver correto, se tabela estiver com um nome onde não exista por exemplo: SUBSTRING(mensagem,10, 3) = '025' não faz nenhum calculo. Não sei como faço para pesquisar qualquer coisa no meu database Historico não precisado toda vez mudificar o nome da tabela, pesquisando entre duas datas...

     

    SELECT COUNT(SUBSTRING(mensagem, 10, 3))

    AS TotalRefeicao, SUBSTRING(mensagem,10, 3) as TipoMensagem

    FROM sys.objects CROSS JOIN SQL20090108

    WHERE (sys.objects.type = 'U') AND (sys.objects.is_ms_shipped = 0)

    AND (sys.objects.create_date BETWEEN '20090105' AND '20090109')

    AND SUBSTRING(mensagem,10, 3) = '025'

    GROUP BY SUBSTRING(mensagem,10, 3)

     

    Obrigada pela ajuda

    sexta-feira, 9 de janeiro de 2009 18:55
  •  

    Olá Mary,

     

    A idéia seria consultar atravéz do sql que postei e só assim aplicar o que você deseja:

     

    Code Snippet

    select t.*

    from 

    suaTabela t left join

    (

    SELECT o.name

    FROM sys.objects o

    WHERE

    o.type = 'U'

    AND o.is_ms_shipped = 0

    AND o.create_date BETWEEN '20090105' AND '20090109'

    ) objeto

    on objeto.name = t.nome collate SQL_Latin1_General_CP1_CI_AS

     

     

    Com o comando acima, você consegue recuperar todas as tabelas criadas num período. Daí, basta trocar o t.* pelo comando

     

    "COUNT(SUBSTRING(mensagem, 10, 3))

    AS TotalRefeicao, SUBSTRING(mensagem,10, 3) as TipoMensagem"

     

    Ou outro qualquer

     

    Abraços

    sexta-feira, 9 de janeiro de 2009 19:12
  • BOM DIA, DEMÉTRIO

     

    Fiz o meu select abaixo, mas com este select eu não consigo pegar os dados se eu não indicar o nome da tabela, não consigui fazer o meu select passar nas tabelas entre '20090105'  entre '20090112'

    Ele pega os dados do dia '20090112' coloquei o nome da minha tabela com a data de hoje, mas eu gostaria pegar todos os dados '20090105' entre '20090112'

     

    select COUNT(SUBSTRING(mensagem, 10, 3))AS TotalRefeicao,

    SUBSTRING(mensagem,10, 3) as TipoMensagem

    from SQL20090112 left join(SELECT o.name FROM sys.objects o

    WHERE o.type = 'U'AND o.is_ms_shipped = 0 AND

    o.create_date BETWEEN '20090105' AND '20090112')

    objeto on objeto.name = SUBSTRING(mensagem,10, 3)collate SQL_Latin1_General_CP1_CI_AS

    AND SUBSTRING(mensagem,10, 3) = '025'

    GROUP BY SUBSTRING(mensagem,10, 3)

     

    Obrigada desde já

     

     

    segunda-feira, 12 de janeiro de 2009 12:16
  •  

    Olá Mary,

     

    Porque você está filtrando o nome das tabelas?

     

    Code Snippet

    "

    on objeto.name = SUBSTRING(mensagem,10, 3)collate SQL_Latin1_General_CP1_CI_AS

    AND SUBSTRING(mensagem,10, 3) = '025'

    "

     

     

    Abraços

    segunda-feira, 12 de janeiro de 2009 14:47