Mysql to SQL Sever

Asked

Viewed 32 times

1

What would the following code look like MySQL in SQL Server 2008:

desc `TABLE 2`;

alter table `TABLE 2` add column `COLL 44` varchar(20) DEFAULT NULL;
alter table `TABLE 2` add column `COLL 45` varchar(20) DEFAULT NULL;
alter table `TABLE 2` add column `COLL 46` varchar(20) DEFAULT NULL;
alter table `TABLE 2` add column `COLL 47` varchar(20) DEFAULT NULL;
alter table `TABLE 2` add column `COLL 48` varchar(20) DEFAULT NULL;
alter table `TABLE 2` add column `COLL 49` varchar(20) DEFAULT NULL;
alter table `TABLE 2` add column `COLL 50` varchar(20) DEFAULT NULL;
alter table `TABLE 2` add column `COLL 51` varchar(20) DEFAULT NULL;

select * from `TABLE 2`;
update `TABLE 2` AS T2
INNER JOIN `TABLE 1` AS T1 ON T1.`COL 1` = T2.`COL 1` -- N3 FILIAL
                                 AND T1.`COL 2` = T2.`COL 2` -- N3_CBASE
                                 AND T1.`COL 3` = T2.`COL 3` -- N3_ITEM
                                 AND T1.`COL 11` = T2.`COL 8` -- N3_CCDEPRE
SET
        T2.`COL 44` = T1.`COL4`, -- N3_TIPO
        T2.`COL 45` = T1.`COL8`, -- N3_CUSTOBEM
        T2.`COL 46` = T1.`COL18`, -- N3_VORIGI
        T2.`COL 47` = T1.`COL19`, -- N3_TXDEPR1
        T2.`COL 48` = T1.`COL33`, -- N3_TVRDACM1
        T2.`COL 49` = T1.`COL59`, -- N3_FIMDEPR
        T2.`COL 50` = T1.`COL79`, -- N3_CLV
        T2.`COL 58` = T1.`COL16`; -- N3_DINDEP


SELECT * 
FROM `TABLE 2` AS T2            
WHERE 
                T2.`COL 44` IS NOT NULL OR  
                T2.`COL 45` IS NOT NULL OR  
                T2.`COL 46` IS NOT NULL OR  
                T2.`COL 47` IS NOT NULL OR  
                T2.`COL 48` IS NOT NULL OR  
                T2.`COL 49` IS NOT NULL OR  
                T2.`COL 50` IS NOT NULL OR  
                T2.`COL 51` IS NOT NULL
mysql sql

  • Do you have a table with space in the name? That’s very wrong

  • @Sorack yes, I went up a CSV and it created automatic table name as TABLE 2, so the ran right on mysql, problem I need to run on SQL Server.

1 answer

0


In SQL Server it would be something like this:

EXEC sp_columns [TABLE 2]

ALTER TABLE [TABLE 2] ADD [COLL 44] VARCHAR(20) NULL
ALTER TABLE [TABLE 2] ADD [COLL 45] VARCHAR(20) NULL
ALTER TABLE [TABLE 2] ADD [COLL 46] VARCHAR(20) NULL
ALTER TABLE [TABLE 2] ADD [COLL 47] VARCHAR(20) NULL
ALTER TABLE [TABLE 2] ADD [COLL 48] VARCHAR(20) NULL
ALTER TABLE [TABLE 2] ADD [COLL 49] VARCHAR(20) NULL
ALTER TABLE [TABLE 2] ADD [COLL 50] VARCHAR(20) NULL
ALTER TABLE [TABLE 2] ADD [COLL 51] VARCHAR(20) NULL

SELECT  * 
FROM    [TABLE 2]

UPDATE      T2
SET         T2.[COL 44] = T1.[COL4],    -- N3_TIPO
            T2.[COL 45] = T1.[COL8],    -- N3_CUSTOBEM
            T2.[COL 46] = T1.[COL18],   -- N3_VORIGI
            T2.[COL 47] = T1.[COL19],   -- N3_TXDEPR1
            T2.[COL 48] = T1.[COL33],   -- N3_TVRDACM1
            T2.[COL 49] = T1.[COL59],   -- N3_FIMDEPR
            T2.[COL 50] = T1.[COL79],   -- N3_CLV
            T2.[COL 58] = T1.[COL16]    -- N3_DINDEP
FROM        [TABLE 2] T2
INNER JOIN  [TABLE 1] T1 ON T1.[COL 1]  = T2.[COL 1] -- N3 FILIAL
                        AND T1.[COL 2]  = T2.[COL 2] -- N3_CBASE
                        AND T1.[COL 3]  = T2.[COL 3] -- N3_ITEM
                        AND T1.[COL 11] = T2.[COL 8] -- N3_CCDEPRE

SELECT  * 
FROM    [TABLE 2]
WHERE   [COL 44] IS NOT NULL OR 
        [COL 45] IS NOT NULL OR  
        [COL 46] IS NOT NULL OR  
        [COL 47] IS NOT NULL OR  
        [COL 48] IS NOT NULL OR  
        [COL 49] IS NOT NULL OR  
        [COL 50] IS NOT NULL OR  
        [COL 51] IS NOT NULL

The "secret" is the use of straight brackets ([]) to replace the plea or accent Mysql uses to allow spaces in table names and/or columns.

  • PERFECT, worked properly!

  • Take advantage and give an UP on the answer :)

Browser other questions tagged mysql sql

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