我想按名称顺序在SQL中的表中插入数据 [英] I want to insert the data in table in SQL with order by name

问题描述

我正在使用此代码按名称按表顺序插入数据但它不起作用。它做的是它首先在表中插入所有名称之后再次从列的末尾开始它不是明智的数据名称排序。请帮助



我尝试过：

i am using this code to insert the data in table order by name but it does not work .what it do is it first insert all the name in the table after that again it start with the end of the column it does not sort the data name wise.please help

What I have tried:

insert   eta_user.dbo.RTU_AVAIL_DIALY  (RTU_NAME )
select substation_name   from [ereps].[dbo].[FRTU_AVAILABILITY_CONFIG]  order by
substation_name

推荐答案

如果我创建表并尝试使用代码：

If I create your tables and try your code:

USE [Testing]
GO

/****** Object:  Table [dbo].[FRTU_AVAILABILITY_CONFIG]    Script Date: 13/05/2019 11:32:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[FRTU_AVAILABILITY_CONFIG](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[SubstationName] [nvarchar](max) NOT NULL,
	[Other] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

USE [Testing]
GO

/****** Object:  Table [dbo].[RTU_AVAIL_DIALY]    Script Date: 13/05/2019 11:32:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[RTU_AVAIL_DIALY](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[RTUName] [nvarchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SELECT TOP 1000 [ID]
      ,[SubstationName]
      ,[Other]
  FROM [Testing].[dbo].[FRTU_AVAILABILITY_CONFIG]

ID	SubstationName	Other
1	DDD	6
2	EEE	5
3	AAA	9
4	BBB	8
5	CCC	7

SELECT TOP 1000 [ID]
      ,[RTUName]
  FROM [Testing].[dbo].[RTU_AVAIL_DIALY]

ID	RTUName
1	AAA
2	BBB
3	CCC
4	DDD
5	EEE

对我来说很好看。

查看输入和输出数据...

It looks fine to me.
Look at your input and output data ...

对在数据库表中维护排序顺序首先是一个问题：排序是一种仅在向最终用户提供数据时才有用的操作。数据库引擎知道如何有效地存储其数据，其标准可能与人类用户的标准不同。



你应该不再担心订单了其中要存储记录:)

亲切。

The desire to maintain a sorted order in a database table is an issue in the first place: sorting is an operation which is really helpful only when presenting data to the end user. The database engine knows how to store its data efficiently, and its criterions for that may not be the same as those of human users.

You should stop worrying about the order in which records are to be stored :)
Kindly.

选择< col1>，< col2>，< col3> dense_rank（）over（由< col2>排序）为< tmp_table>
来自< old_table>的




插入< new_table>

select< col1>，< col2>，< col3>来自< tmp_table>



使用dense_rank订购列。

select <col1>,<col2>,<col3> dense_rank() over(order by <col2>) as d
into <tmp_table>
from <old_table>

insert into <new_table>
select <col1>,<col2>,<col3> from <tmp_table>

using dense_rank to order the column.

这篇关于我想按名称顺序在SQL中的表中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！