为现有的表添加自增列id并赋值

为现有的表添加自增列id并赋值 表中已经有好多数据

可以用存储过程

 

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++;
            }
        }
    }

运行一下就可以了

转载于:https://www.cnblogs.com/ruanzuzhang/archive/2009/02/28/1400405.html