MySQL Variable Error

Question

Here is the code that doesn't work:

DECLARE @myTable TABLE (colName nvarchar(500), tableName nvarchar(500))

insert into @myTable

SELECT c.COLUMN_NAME AS colName, c.TABLE_NAME AS tableName, TABLE_SCHEMA tableSchema 
FROM information_schema.COLUMNS as c 
WHERE c.COLUMN_NAME like '%password%'

select * from @myTable

My error is:

[Error] Script lines: 1-7 -------------------------- You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @myTable TABLE (colName nvarchar(500), tableName nvarchar(500))

insert ' at line 1

Anyone have any ideas?

You can't declare temporary table using '@' in MySQL it should be `CREATE TEMPORARY TABLE ..` — Omesh, Oct 22 '12 at 11:02
Look at http://stackoverflow.com/questions/1524858/create-table-variable-in-mysql — Olaf Dietsche, Oct 22 '12 at 11:04

score 1 · Accepted Answer · answered Oct 22 '12 at 11:12

Mysql is a bit different:

create table myTable (colName nvarchar(500), tableName nvarchar(500));

insert into myTable (colName, tableName)
    SELECT COLUMN_NAME, TABLE_NAME
    FROM information_schema.COLUMNS
    WHERE COLUMN_NAME like '%password%';

select * from myTable;

score 0 · Answer 2 · answered Oct 22 '12 at 11:10

0

you can try this

  insert into myTable  

 SELECT c.COLUMN_NAME AS colName, c.TABLE_NAME AS tableName 
 FROM information_schema.COLUMNS as c 
 WHERE c.COLUMN_NAME like '%password%'

answered Oct 22 '12 at 11:10

SRIRAM

1,888
2
17
17

MySQL Variable Error

2 Answers2