x

Insert multiple rows and update Primary key

Hi All I have multiple rows to insert in the same table all the data is the same except for the Pk field this needs to be incremented by one as this is then needed to be inserted into another table along with an additional feild. In this instance there is a fixed number of rows that I can retrive using a select statement. thanks in advance for any help

reagrds Mratin
more ▼

asked Feb 23, 2012 at 02:13 PM in Default

Souchie gravatar image

Souchie
14 2 2 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

I'm not completely sure what you're going for here, but have you looked at the ROW_NUMBER function? That will give you the ability to SELECT data and simultaneously create numbers.

Or, do you need to create an IDENTITY column in your table? That increments automatically as you insert data. If you use the [OUTPUT][3] clause you can capture all the values as they get created.

[3]: http://msdn.microsoft.com/en-us/library/ms177564.aspx
more ▼

answered Feb 23, 2012 at 02:46 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.1k 19 21 74

I need to insert 88 rows all have the same information as thery are concessionsn and each task needs a seperate concession the PK is the identifer for the table that joins the concession iformation and task information so I am thinking along thje lines of :

Insert into Table A, (col b, col c, Col d .... Col N) Values (a, b, c, d.....z)

With a loop or counter of some sort to use the PK and increment by 1 each time into Col A

Regards

Martin
Feb 23, 2012 at 02:57 PM Souchie
OK, then you should modify TableA to use an IDENTITY column. It will do that work for you.
Feb 23, 2012 at 02:59 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x103

asked: Feb 23, 2012 at 02:13 PM

Seen: 1159 times

Last Updated: Feb 23, 2012 at 02:59 PM