Naru Lopo
Reputation: 19
How Can I create a procedure that autogenerates a table and automatically fill it with values
alter procedure inselt
@MES VARCHAR(30), @IDA INT
AS BEGIN
DECLARE @NRODIA INT
DECLARE @NOMBRE_TABLA VARCHAR (50),@ENTRADA VARCHAR(20),@SOPA VARCHAR(20),@PLATO VARCHAR(20)
DECLARE @PLATOP varchar (50)
DECLARE @PLATOS varchar (20)
SET @PLATOP='PLATOP'
set @PLATOS='PLATOS'
DECLARE @A VARCHAR(1)
SET @A=CHAR(39)
DECLARE @tipo varchar(20)
SET @tipo='tipo'
SET @NOMBRE_TABLA=@MES
declare @entrada1 varchar(20)
set @ENTRADA1='entrada'
declare @sopa1 varchar(20)
set @sopa1='sopa'
declare @plato1 varchar(20)
set @plato1='plato'
declare @nd int
DECLARE @xEntrada VARCHAR(20), @xSopa varchar(20), @xPlato varchar(20)
set @nd=1
IF OBJECT_ID(@NOMBRE_TABLA) IS NULL
BEGIN
EXECUTE ('CREATE TABLE ' + @NOMBRE_TABLA+'
(nrodia integer,entrada varchar(20), sopa varchar(20), plato varchar(20),unique(entrada,sopa,plato))')
while @nd<31
begin
EXECUTE(
'Select TOP 1' + @xEntrada + '=' +@PlatoP+ 'from' +@Platos+ 'where' +@tipo+ '=' +@A+@entrada1+@A+ 'ORDER BY NEWID()
Select TOP 1' + @xSopa + '=' +@PlatoP+ 'from' +@Platos+ 'where' +@tipo+ '=' +@A+@sopa1+@A+ 'ORDER BY NEWID()
Select TOP 1' + @xPlato + '=' +@PlatoP+ 'from' +@Platos+ 'where' +@tipo+ '=' +@A+@plato1+@A+ 'ORDER BY NEWID()
if (not exists(select 1 FROM'+@NOMBRE_TABLA+ 'WHERE' +@entrada1+ '=' +@xEntrada+ 'AND' +@Sopa1+ '=' +@xSopa+ 'AND'
+@PLATO1+ '=' +@xPlato+ '))' + 'BEGIN INSERT INTO' +@NOMBRE_TABLA+ 'values (' +@nd+ ',' +@xEntrada+ ',' +@xSopa+ ','
+@xPlato+ ')' + 'SET' +@nd+ '=' +@nd+ '+1' + 'END END;')
end
END
END
This is my try to: 1)Auto create a table just entering "Exec inselt 'a varchar', 'a int of 4 digits'" 2)After that the created table must be filled with values from another table The values come from this table :
CREATE TABLE PLATOS(Idplatos integer PRIMARY KEY, Tipo varchar(20), PlatoP varchar(20));
---insert---
INSERT INTO Platos VALUES('1','entrada','ceviche');
INSERT INTO Platos VALUES('2','entrada','arrozleche');
INSERT INTO Platos VALUES('3','sopa','caldo');
INSERT INTO Platos VALUES('4','sopa','mote');
INSERT INTO Platos VALUES('5','plato','lomo');
INSERT INTO Platos VALUES('6','plato','rusa');
, the code without a procedure it's:
declare @nd int
set @nd=1
DECLARE @xEntrada VARCHAR(20), @xSopa varchar(20), @xPlato varchar(20)
while @nd<=31
begin
Select TOP 1 @xEntrada = PlatoP from Platos where tipo = 'entrada' oRDER BY NEWID()
Select TOP 1 @xSopa = PlatoP from Platos where tipo = 'sopa' Order by NewId()
Select TOP 1 @xPlato = PlatoP from Platos where tipo = 'plato' oRDER by NewId()
IF (NOT EXISTS(SELECT 1 FROM abril2017 WHERE Entrada = @xEntrada AND Sopa = @xSopa AND Plato = @xPlato))
BEGIN
Insert into abril2017 values (@nd,@xEntrada,@xSopa,@xPlato)
SET @nd=@nd+1
END
END;
Converting that code to:
EXECUTE(
'Select TOP 1' + @xEntrada + '=' +@PlatoP+ 'from' +@Platos+ 'where' +@tipo+ '=' +@A+@entrada1+@A+ 'ORDER BY NEWID()
Select TOP 1' + @xSopa + '=' +@PlatoP+ 'from' +@Platos+ 'where' +@tipo+ '=' +@A+@sopa1+@A+ 'ORDER BY NEWID()
Select TOP 1' + @xPlato + '=' +@PlatoP+ 'from' +@Platos+ 'where' +@tipo+ '=' +@A+@plato1+@A+ 'ORDER BY NEWID()
if (not exists(select 1 FROM'+@NOMBRE_TABLA+ 'WHERE' +@entrada1+ '=' +@xEntrada+ 'AND' +@Sopa1+ '=' +@xSopa+ 'AND'
+@PLATO1+ '=' +@xPlato+ '))' + 'BEGIN INSERT INTO' +@NOMBRE_TABLA+ 'values (' +@nd+ ',' +@xEntrada+ ',' +@xSopa+ ','
+@xPlato+ ')' + 'SET' +@nd+ '=' +@nd+ '+1' + 'END END;')
(the complete try of the code it's the first one) How Can I make this possible? Right now I'm only able to autogenerate a table but without the int of 4 digits, the insertion of the values aren't possible because sql-server says this
> Msg 102, Level 15, State 1, Line 123 Incorrect syntax near '='. Msg
> 102, Level 15, State 1, Line 124 Incorrect syntax near '='. Msg 102,
> Level 15, State 1, Line 125 Incorrect syntax near '='. Msg 102, Level
> 15, State 1, Line 126 Incorrect syntax near '='. Msg 102, Level 15,
> State 1, Line 126 Incorrect syntax near ','. Msg 102, Level 15, State
> 1, Line 123 Incorrect syntax near '='. Msg 102, Level 15, State 1,
> Line 124 Incorrect syntax near '='. Msg 102, Level 15, State 1, Line
> 125 Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 125
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 126
> Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 123
> Incorrect syntax near '='. Msg 102, Level 15, State 1, Line 124
> Incorrect syntax near '='.
And a lot more but they all say almost the same, almost 115 times I think.
Upvotes: 0
Views: 70
Answers (1)
Brian Leach
Reputation: 2101
Am I missing something? How about
select * into newtable from platos;
Or if your table creation is ok, and you have manually inserted your first rows, use an insert select:
insert into newtable (col1, col2, col3, col4)
select cola, colb, colc, cold from oldtable;
Upvotes: 1
Related Questions
- SQL Server : creating a table from procedure with selected data
- Create a stored procedure to insert new data into a table
- Fill temporary table with procedure result
- How to create automatically a table populate script from current data in SQL Server?
- How to automatically create rows and pass values to other tables
- How to automatically generate records into a table
- Automate procedures SQL
- How to insert data from auto generated and already created data
- recursively fill a sql table based on data in the table
- SQL Server - Autoincrementing on a generated table