為現(xiàn)有的表添加自增列id并賦值 表中已經(jīng)有好多數(shù)據(jù)

可以用存儲過程

?

Declare @Id int
Declare @Name varchar(500)
DECLARE TabA CURSOR FOR
SELECT jwdatabase1.dbo.ggxxkc.ID,jwdatabase1.dbo.ggxxkc.syzy
FROM jwdatabase1.dbo.ggxxkc

Open TabA
Fetch Next From TabA into @Id,@Name
while(@@FETCH_STATUS=0)
??? BEGIN
??? UPDATE jwdatabase.dbo.ggxxkc set jwdatabase.dbo.ggxxkc.syzybh=@Name WHERE jwdatabase.dbo.ggxxkc.ggxxkcid=@Id?
??? Fetch Next From TabA into @Id,@Name
??? end

Close TabA
DEALLOCATE TabA

?

也可以編一個程序

?

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
??? public partial class Form1 : Form
??? {
??????? public Form1()
??????? {
??????????? InitializeComponent();
??????? }

??????? private void button1_Click(object sender, EventArgs e)
??????? {
??????????? string str = @"server=211.85.185.5;user id=sa;password=;database=newjwc;Connect Timeout=500;
???????????? min pool size=4;max pool size=4;packet size=3072";
??????????? SqlConnection conn = new SqlConnection(str);
??????????? conn.Open();
??????????? string sql1 = "select * from xjbd";
??????????? SqlDataAdapter ada=new SqlDataAdapter(sql1,conn);
??????????? DataSet ds=new DataSet();
??????????? ada.Fill(ds);
??????????? int id = 1;
??????????? foreach (DataRow dr in ds.Tables[0].Rows)
??????????? {
???????????????
??????????????? string sql2= @"update xjbd set id=" + id.ToString() + " where xsid=" + dr["xsid"].ToString() + " and xbh='"
??????????????????? + dr["xbh"].ToString() + "'";
??????????????? SqlCommand comm = new SqlCommand(sql2, conn);
??????????????? comm.ExecuteNonQuery();
??????????????? id++;
??????????? }
??????? }
??? }

運行一下就可以了

轉(zhuǎn)載于:https://www.cnblogs.com/ruanzuzhang/archive/2009/02/28/1400405.html

總結(jié)

以上是生活随笔為你收集整理的为现有的表添加自增列id并赋值的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。