我们可以为两个或多个表提供标识库

I have one qustion please helpme

我有一个问题，请帮助我

Basically i have two tables table1 and table2

基本上我有两个表表表1和表2

table1 has two columns: id and name

表1有两列:id和name

and data is like this

数据是这样的

id      name        
c01    sukanth        
co2    ram

table2 also columns id and name.

表2还列有id和名称。

But now table2 id column has to check ending number of id column in table1 and start next value

但是现在表2 id列必须检查表1中的id列的结束号并开始下一个值

Something like this

像这样的东西

id      name         
c03    sukanth         
co4    ram

and for example if table1 has ending column c99, then table2 starting id is d00 or do1 like that

例如，如果表1有结束列c99，那么表2的起始id是d00或do1

Please help me.

请帮助我。

Here I have n number of tables but same id has to share for all tables

这里有n个表，但所有表都必须使用相同的id

I want to importing Excel file data into database. I have n number of Excel files. And one Excel file one table but id should be unique for all tables. in excel file there is no id column i have to create id according to previous file ending record number

我想将Excel文件数据导入数据库。我有n个Excel文件。一个Excel文件一个表，但是id应该对所有表都是唯一的。在excel文件中，我不需要根据之前的文件结束记录号创建id列

2 个解决方案

#1

For that you have to manage yourself. Because SQL Server does not know about your requirement that, if one table ended with 1001 and start a new identity on another table from 1002. SQL Server Identity syntax is IDENTITY [ ( seed , increment ) ] Which means, each time when you insert an new row it see an Last Seeeed and the Increment it by the value you set up earlier while designing your table.

为此，你必须管好自己。因为SQL Server不知道您的要求，如果一个表以1001结尾，并从1002开始在另一个表上启动新的标识。SQL Server标识语法是Identity [(seed, increment)]，这意味着每次插入一个新行时，它会看到最后一个已见的内容，并根据您在设计表时设置的值对其进行递增。

So, if you want to do so then, Insert all the rows from Excel to SQL Server. Then, Before starting to insert the rows in Next table, Get the Last Seeed value of previous table. Then, you can use that value to insert.

因此，如果您想这样做，那么将所有的行从Excel插入到SQL Server。然后，在开始插入下一个表中的行之前，获取前一个表的最后一个Seeed值。然后，可以使用该值进行插入。

But this is very Bad Solution you know In my case what i would do is. I just setup an Counter Variable in my application which take care of the Row Position, and i'd simply insert that Value in the Required Tables.

但这是一个很糟糕的解决方案在我的例子中，我要做的是。我只是在我的应用程序中设置了一个计数器变量，它负责处理行位置，我只需将该值插入所需的表中。

Anyway, you want to do that then, You can Remove that Identity Property at all. Because you self managing so.

不管怎样，你想要这样做，你就可以移除那个身份属性。因为你自己管理。

I suggest the following way :

我的建议是:

Int RowNum=0
Foreach (Excel doc){
   Foreach (Rows in doc)
     {
        RowNum++;
        Fields1=val ...n
        If data is something 
           {
               Insert into Table 1 values(RowNum,Fields...n)
           }

        If data is something else
           {
               Insert into Table 2 values(RowNum,Fields...n)
           }
     }
}

I think that way you can manage your soultion.

我认为这样你就能控制自己的感情。

This is my suggestion only with Scratch example. Hope you understand why im suggesting you that way.

这是我的建议，仅举个例子。希望你能理解我为什么这样建议你。

#2

So, you want your Dirty way.

所以，你想要肮脏的方式。

This is exactly what you were looking.

这正是你要找的。

Ok, take a look at this now then.

好吧，现在就来看看这个。

Suppose You have an table like the Following :

假设您有如下表格:

CREATE TABLE products1 (id int IDENTITY, product varchar(40))
CREATE TABLE products2 (id int IDENTITY, product varchar(40))

Ok. So now you are inserting an record from Excel into your Product1 Table. Like

好的。现在，您正在将Excel中的一条记录插入到Product1表中。就像

Int LastId=0

Foreach (Rows from ExcelDoc1){
    ProductName=ExcelColumn
    Insert into Products1 values(ProductName)
}

So now get that Last Inserted Row's Identifier so that we can use that to on another table which must be an starting ID, Like the Following :

现在得到最后插入的行标识符以便我们可以在另一个表上使用它它必须是一个起始ID，如下所示:

LastId= Select isnull(Max(ID),0)+1 as NextId from Products1
So now you have an Last Identity on LastID

So Before Inserting an new row on the Next table
Modify that tables, Identity Insert Mode, Because you are self inserting an new Identity on that table. 

SET IDENTITY_INSERT products1 ON

Foreach (Rows from ExcelDoc2){
    ProductName=ExcelColumn
    Insert into Products1 values(LastId,ProductName)
    LastId++
} 

Again reset that Setting so that, you dont have to insert an identity value yourslef.
 SET IDENTITY_INSERT products1 OFF

DOne!!!

完成了! ! !

If anything Take a look at this Link : http://msdn.microsoft.com/en-us/library/aa259221(v=sql.80).aspx

如果有什么可以看看这个链接:http://msdn.microsoft.com/en-us/library/aa259221(v=sql.80).aspx

#1