How to insert data into a table with primary key? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to insert data into a table with primary key?

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-03-05 : 09:31:28

There are two tables with the same columns and data type, A and B.
In order to make OrderID unique, I set up OrderID in B as primary key.
How to code to insert all records from A into B?

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-03-05 : 10:11:22

and A and B have some common OrderID's?

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-03-05 : 10:18:57

A has duplipcate OrderID but I want to make B unique

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-05 : 10:22:19

what about other columns? Can you show sample for Table A and what needs to be done?

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-03-05 : 10:53:36

A and B

OrderID (numeric(16, 0)), OrderName (numeric(16, 0))

There are duplicate OrderID in A but B is empty and OrderID set as primary key to be store unique OrderID.

If I use code below will create an error:

insert into B select * from A

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-05 : 11:16:49

So if there is duplicate OrderID in TableA then which Ordernumber would you like to go in Table B like:

OrderID OrderNumber
1 256
1 356

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-03-05 : 11:35:47

any one

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-05 : 23:35:39

try this one too
declare @tab table (OrderID numeric(16, 0), OrderName numeric(16, 0))
insert into @tab select 1, 256
insert into @tab select 2, 456
insert into @tab select 4, 234
insert into @tab select 2, 356
insert into @tab select 1, 349

declare @tab1 table (OrderID numeric(16, 0) primary key, OrderName numeric(16, 0))

insert into @tab1
select OrderID,OrderName from (select row_number()over(partition by OrderID order by OrderID) as rid,* from @tab ) t where t.rid = 1

select * from @tab1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources