如何使用 SELECT * INTO tempTable FROM CTE 查询创建临时表（查询结果创建临时表）

25-01-26 20

此处将为大家介绍关于如何使用SELECT*INTOtempTableFROMCTE查询创建临时表的详细内容，并且为您解答有关查询结果创建临时表的相关问题，此外，我们还将为您介绍关于ACCESS的真假：

此处将为大家介绍关于如何使用 SELECT * INTO tempTable FROM CTE 查询创建临时表的详细内容，并且为您解答有关查询结果创建临时表的相关问题，此外，我们还将为您介绍关于ACCESS的真假：四、"SELECT * INTO 工作表 FROM 临时表&quo、android.database.sqlite.SQLiteException：near“SELECT”：语法错误(代码1):,同时编译：SELECT * FROM Table_Name、bootstrapTable editable select empty、DELETE FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.field1=table2.field1);的有用信息。

本文目录一览：

如何使用 SELECT * INTO tempTable FROM CTE 查询创建临时表（查询结果创建临时表）
ACCESS的真假：四、"SELECT * INTO 工作表 FROM 临时表&quo
android.database.sqlite.SQLiteException：near“SELECT”：语法错误(代码1):,同时编译：SELECT * FROM Table_Name
bootstrapTable editable select empty
DELETE FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.field1=table2.field1);

如何使用 SELECT * INTO tempTable FROM CTE 查询创建临时表（查询结果创建临时表）

我有一个 MS SQL CTE 查询，我想从中创建一个临时表。我不知道该怎么做，因为它给出了一个Invalid Object name错误。

以下是整个查询以供参考

SELECT * INTO TEMPBLOCKEDDATES FROM ;with Calendar as (    select EventID, EventTitle, EventStartDate, EventEndDate, EventEnumDays,EventStartTime,EventEndTime, EventRecurring, EventStartDate as PlannedDate    ,EventType from EventCalender    where EventActive = 1 AND LanguageID =1 AND EventBlockDate = 1    union all    select EventID, EventTitle, EventStartDate, EventEndDate, EventEnumDays,EventStartTime,EventEndTime, EventRecurring, dateadd(dd, 1, PlannedDate)    ,EventType from Calendar    where EventRecurring = 1        and dateadd(dd, 1, PlannedDate) <= EventEndDate )select EventID, EventStartDate, EventEndDate, PlannedDate as [EventDates], Cast(PlannedDate As datetime) AS DT, Cast(EventStartTime As time) AS ST,Cast(EventEndTime As time) AS ET, EventTitle,EventType from Calendarwhere (PlannedDate >= GETDATE()) AND '','' + EventEnumDays + '','' like ''%,'' + cast(datepart(dw, PlannedDate) as char(1)) + '',%''    or EventEnumDays is nullorder by EventID, PlannedDateoption (maxrecursion 0)

如果我能从这个 CTE 查询中创建一个临时表，我将不胜感激

答案1

小编典典

示例 DDL

create table #Temp(    EventID int,     EventTitle Varchar(50),     EventStartDate DateTime,     EventEndDate DatetIme,     EventEnumDays int,    EventStartTime Datetime,    EventEndTime DateTime,     EventRecurring Bit,     EventType int)

;WITH CalendarAS (SELECT /*...*/)Insert Into #TempSelect EventID, EventStartDate, EventEndDate, PlannedDate as [EventDates], Cast(PlannedDate As datetime) AS DT, Cast(EventStartTime As time) AS ST,Cast(EventEndTime As time) AS ET, EventTitle,EventType from Calendarwhere (PlannedDate >= GETDATE()) AND '','' + EventEnumDays + '','' like ''%,'' + cast(datepart(dw, PlannedDate) as char(1)) + '',%''    or EventEnumDays is null

确保表在使用后被删除

If(OBJECT_ID(''tempdb..#temp'') Is Not Null)Begin    Drop Table #TempEnd

ACCESS的真假：四、"SELECT * INTO 工作表 FROM 临时表&quo

下面这个贴子中讨论了很多access中的一些猜想假设。 Access或其它数据库操作效率问题.欢迎讨论 http://topic.csdn.net/u/20090514/10/a93475bd-e67e-45c8-aa1e-87168ba36d02.html#replyachor 引用 55 楼 wwwwb 的回复:: create table是用什么方法？我一般是

下面这个贴子中讨论了很多access中的一些猜想假设。

Access或其它数据库操作效率问题.欢迎讨论

http://topic.csdn.net/u/20090514/10/a93475bd-e67e-45c8-aa1e-87168ba36d02.html#replyachor

引用 55 楼 wwwwb 的回复::
create table是用什么方法？我一般是将空的工作表另存为一个临时表，
用SELECT * INTO 工作表 FROM 临时表方法建立，这种方法比delete from table
还要慢？
这种方法的局限是如果有表间关系是不能删除的。

当看到这个猜想的时候，感觉比较怀疑。因为 select * into newtable from oldTable，ACCESS需要先从oldTable得到所有字段的定义，然后才能进行表的创建。如果猜想成立，就说明ACCESS还有一套什么机制可以直接在底层对表的结构进行访问。但select 可以是任意的查询，这样也不太可能啊。于是，依然做个简单的试验来难证一下这个猜想。

新建空 t.mdb ，然后创建一个模块，代码如下。

Option Compare Database Option Explicit Public Sub ti() Dim ssql As String Dim conn As ADODB.Connection Set conn = CurrentProject.Connection ssql = "create table table2(id integer,cname char(10))" conn.Execute ssql End Sub Public Sub tx() Dim ssql As String Dim conn As ADODB.Connection Set conn = CurrentProject.Connection Dim i As Integer On Error Resume Next For i = 1 To 9000 ssql = "drop table t" & (10000 + i) CurrentProject.Connection.Execute ssql Next i On Error GoTo 0 End Sub Public Sub t1() Dim ssql As String Dim i As Integer For i = 1 To 9000 ssql = "create table t" & (10000 + i) & " (id integer,cname char(10))" CurrentProject.Connection.Execute ssql Next i End Sub Public Sub t2() Dim ssql As String Dim i As Integer For i = 1 To 9000 ssql = "select * into t" & (10000 + i) & " from table2" CurrentProject.Connection.Execute ssql Next i End Sub Public Sub t() Call tx Debug.Print "t1 start.", Now Call t1 Debug.Print "t1 end .", Now Call tx Debug.Print "t2 start.", Now Call t2 Debug.Print "t2 end .", Now End Sub

步骤一：运行 ti()创建一个原表 table2 以供后面的 select * into newtable from oldTable 使用。
步骤二：运行 t() 结果如下。
t1 start.     5/23/2009 3:06:54 PM
t1 end .     5/23/2009 3:07:03 PM
t2 start.     5/23/2009 3:07:17 PM
t2 end .     5/23/2009 3:07:29 PM

t1() 9s , t2() 12s

试验结论：猜想不成立。

android.database.sqlite.SQLiteException：near“SELECT”：语法错误(代码1):,同时编译：SELECT * FROM Table_Name

我得到 android.database.sqlite.sqliteException：接近“SELECT”：语法错误(代码1):,同时编译：SELECT * FROM Universities错误.

我有以下课程

用户类为

public class User {
    private int univ_id;

    private String univ_name,univ_abbr;

    public User(int univ_id,String univ_name,String univ_abbr) {

        this.univ_id = univ_id;
        this.univ_name = univ_name;
        this.univ_abbr = univ_abbr;
    }

    public User(String univ_name,String univ_abbr) {

        this.univ_name = univ_name;
        this.univ_abbr = univ_abbr;
    }

    public User() {
        // Todo Auto-generated constructor stub
    }

    public int getUniv_id() {
        return univ_id;
    }

    public void setUniv_id(int univ_id) {
        this.univ_id = univ_id;
    }

    public String getUniv_name() {
        return univ_name;
    }

    public void setUniv_name(String univ_name) {
        this.univ_name = univ_name;
    }

    public String getUniv_abbr() {
        return univ_abbr;
    }

    public void setUniv_abbr(String univ_abbr) {
        this.univ_abbr = univ_abbr;
    }
}

DBStorageFirst Class as

public class DBStorageFirst extends sqliteOpenHelper {

    public static final String DATABASE_NAME = "STUPIDSID_OFFLINE_DB";

    public static final String TABLE_NAME_UNIVERSITIES = "universities";


    public static final String COLUMN_UNIV_ID = "univ_id";
    public static final String COLUMN_UNIV_NAME = "univ_name";
    public static final String COLUMN_UNIV_ABBR = "univ_abbr";

    public DBStorageFirst(Context context) {
        super(context,DATABASE_NAME,null,1);
    }

    @Override
    public void onCreate(sqliteDatabase db) {
        // Todo Auto-generated method stub
        db.execsql("create table " + TABLE_NAME_UNIVERSITIES
                + "(" + COLUMN_UNIV_ID + " integer RIMARY KEY," + COLUMN_UNIV_NAME
                + " text," + COLUMN_UNIV_ABBR + " text )");
        Log.e("Success","Universities Table Created");

    }

    @Override
    public void onUpgrade(sqliteDatabase db,int oldVersion,int newVersion) {
        // Todo Auto-generated method stub
        db.execsql("DROP TABLE IF EXISTS " + TABLE_NAME_UNIVERSITIES);

        onCreate(db);
    }

    public void addUniversities(int univ_id,String univ_abbr) {
         sqliteDatabase db = this.getWritableDatabase();
        String sql1 = "insert into " + DBStorageFirst.TABLE_NAME_UNIVERSITIES
                + " (" + DBStorageFirst.COLUMN_UNIV_ID + ","
                + DBStorageFirst.COLUMN_UNIV_NAME + ","
                + DBStorageFirst.COLUMN_UNIV_ABBR + ")" + " values(" + univ_id
                + ",''" + univ_name + " '',''" + univ_abbr + "'')";
        db.execsql(sql1);
        // database.execsql(sql2);
        Log.e("Success","Data inserted Successfully into Universities Table");
    }

    public List<User> getAllContacts() {
        List<User> contactList = new ArrayList<User>();

        String selectQuery = "SELECT * FROM "+TABLE_NAME_UNIVERSITIES;
        sqliteDatabase db1 = this.getWritableDatabase();
        Cursor cursor = db1.rawQuery(selectQuery,null);
        if (cursor.movetoFirst()) {
            do {
                User contact = new User();
                contact.setUniv_id(Integer.parseInt(cursor.getString(0)));
                contact.setUniv_name(cursor.getString(1));
                contact.setUniv_abbr(cursor.getString(2));
                contactList.add(contact);
            } while (cursor.movetoNext());
        }
        return contactList;
    }
}

和MainActivity一样

DBStorageFirst db = new DBStorageFirst(this);

// Inserting Contacts
Log.d("Insert: ","Inserting ..");
db.addUniversities(25,"ABC","UA");
db.addUniversities(26,"DEF","UB");
List<User> list = new ArrayList<User>();
list = db.getAllContacts();

for (User cn : list) {
    String log = "Id: " + cn.getUniv_id() + ",Name: "
            + cn.getUniv_name() + ",Abbr: " + cn.getUniv_abbr();
    // Writing Contacts to log
    Log.d("Name: ",log);
}

解决方法

看起来SELECT之后的空格不是普通空格(0x20),而是一些其他字符,例如非空格(0xa0).用常规空间替换它.

bootstrapTable editable select empty

总结

以上是小编为你收集整理的bootstrapTable editable select empty全部内容。

如果觉得小编网站内容还不错，欢迎将小编网站推荐给好友。

DELETE FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.field1=table2.field1);

(?is)\b(?:from|into|update)\s+(\w+)

关于如何使用 SELECT * INTO tempTable FROM CTE 查询创建临时表和查询结果创建临时表的问题就给大家分享到这里，感谢你花时间阅读本站内容，更多关于ACCESS的真假：四、"SELECT * INTO 工作表 FROM 临时表&quo、android.database.sqlite.SQLiteException：near“SELECT”：语法错误(代码1):,同时编译：SELECT * FROM Table_Name、bootstrapTable editable select empty、DELETE FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.field1=table2.field1);等相关知识的信息别忘了在本站进行查找喔。

本文标签：