Write data to a based table and two other tables

Asked

Viewed 182 times

0

Hello! I am new in sql Transact and would like and have the following situation. I have a table that contains the list of columns in a second table. That’s right! In table B the names of the columns are the same stored in table A. Then use the SELECT information in table B and store in a third table. It would be something like:

@Coluna = SELECT nomecoluna FROM tabelaA;
WHILE (!@Coluna)
BEGIN
INSERT INTO TABLE tabelaC (data, tag) 
SELECT data, @Coluna from tabelaB;
END

Note: The column tag in the table will receive the column name as value (@column)

I hope someone will cheer up to help me! Abs!

sql

  • Check if this can be useful to you:https://answall.com/questions/52800/like-passData

  • Thank you for the answer, but this example ai is only for inserting one table into another. Unfortunately I need something much more complex!

2 answers

0

I think it works:

declare @query nvarchar(max), @nome varchar(50)
declare @temp Table (nomecoluna nvarchar(50))

--SALVE OS REGISTROS COM OS NOMES DAS COLUNAS EM @TABLE
insert into @temp
select * from TabelaA

--ENQUANTO EXISTIR REGISTRO EM @TABLE...
while exists (select nomecoluna from @temp)

begin
--...CRIA A QUERY PARA PEGAR DE UMA TABELA E INSERIR NA OUTRA
set @nome = (select top 1 nomecoluna from @temp);

set @query = 'insert into TabelaC (tag) select '+@nome+' from TabelaB';
--EXECUTE A QUERY
exec (@query)
--E APAGUE O REGISTRO DE @TEMP
delete from @temp where nomecoluna = @nome

end

  • Hello Leon. From what I understand you are reading the names of the columns of the Table and storing in a temporary table. Then read from this table and write to the Table. No meu caso são mesmo 3 tabelas: 
TabelaA
 - nomecol
TabelaB
 -datahora
 -nomecol1
 -nomecol2
 -nomecol3
(*)Os nomes das colunas nessa tabela tem correspondentes na TabelaA
TabaleC
 -datahora
 -nomecoluna (takes string'colName 1' or'colName 2', or'colName 3' -value (takes the value stored in colName, colName, etc)

  • I am selecting from the Table and inserting in the Table.

  • A yes. I get it. I’m going to test it here. VLW!

  • Hello Leon. I did the test here and what is being tagged in the Table is the value and not the column name.

  • Insert into Tabelac (tag) values (''+@name+'') . This will insert the column name.

  • That sounds about right. The problem now is how to put quotes inside quotes. I am using Management Studio and gives error: (5 Row(s) affected) Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'tag1'. (1 Row(s) affected) Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'tag2'. I’ve tried several combinations of single and double quotes and nothing... :(

  • I don’t think I can put "into" the Insert in the script pq the Insert is based on select.

  • 1

    It worked! It was 3 simple quotes! Vlw!

Show 3 more comments

0

A suggestion would be to use the select into statement. It will automatically create the table after the into You would need to rename existing tables.

SELECT colunaA, colunaB, coluna C 
INTO TabelaB
FROM TabelaA
WHERE ...

SELECT colunaA, colunaB, coluna C 
INTO TabelaC
FROM TabelaB
WHERE ...

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.