|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
insert multiple rows select from while incrementing a uniq chr keyunique primary key in chr? i.e. 00000001 - 00000100 What I have tried to no avail, with error: A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations. or Violation of PRIMARY KEY constraint --declare @tempid varchar(8) --set @tempid = '00000001' INSERT INTO TestDestinationTable( Employee_ID, Name ) SELECT -- @tempid = '00' + Cast((@tempid+1) as varchar(8)), --'00' + Cast(((select top 1 employee_id from TestDestinationTable order by employee_id desc)+1) as varchar(8)), 'TestName' FROM TestSourceTable -- Rob You can insert into a temp table first with an IDENTITY field, then insert
those results into the table. There is no auto incrementing function for result sets in sqk 2k; but I think I heard something about that functionality in sql 2005, not sure. Show quote "Rob" wrote: > How do you insert multiple rows from a select statement while incrementing a > unique primary key in chr? i.e. 00000001 - 00000100 > > What I have tried to no avail, with error: > > A SELECT statement that assigns a value to a variable must not be combined > with data-retrieval operations. > > or > > Violation of PRIMARY KEY constraint > > --declare @tempid varchar(8) > --set @tempid = '00000001' > > INSERT INTO TestDestinationTable( > Employee_ID, > Name > ) > SELECT > -- @tempid = '00' + Cast((@tempid+1) as varchar(8)), > --'00' + Cast(((select top 1 employee_id from TestDestinationTable order by > employee_id desc)+1) as varchar(8)), > 'TestName' > > FROM > TestSourceTable > > -- > Rob Rob wrote:
Show quote > How do you insert multiple rows from a select statement while There is no easy way to do this in SQL 2000. What you can try doing is > incrementing a unique primary key in chr? i.e. 00000001 - 00000100 > > What I have tried to no avail, with error: > > A SELECT statement that assigns a value to a variable must not be > combined with data-retrieval operations. > > or > > Violation of PRIMARY KEY constraint > > --declare @tempid varchar(8) > --set @tempid = '00000001' > > INSERT INTO TestDestinationTable( > Employee_ID, > Name > ) > SELECT > -- @tempid = '00' + Cast((@tempid+1) as varchar(8)), > --'00' + Cast(((select top 1 employee_id from TestDestinationTable > order by employee_id desc)+1) as varchar(8)), > 'TestName' > > FROM > TestSourceTable loading the PK columns from the TestSourceTable into a temp table with an identity column and then join with the temp table and turn the identity value into a character string format. For example: Create Table #Source (pk int not null primary key, col2 varchar(10)) Create Table #NewSource (pk int not null primary key, col2 varchar(10), autokey varchar(10)) insert into #Source Values (1, '1') insert into #Source Values (2, '2') insert into #Source Values (3, '3') insert into #Source Values (10, '10') -- Create new temp table Select pk, IDENTITY(INT, 1, 1) as "ID" Into #TempSource From #Source Select * from #TempSource Insert Into #NewSource (pk, col2, autokey) Select a.pk, a.col2, RIGHT('0000000000' + CAST(b.ID as VARCHAR(10)), 10) From #Source a Inner Join #TempSource b On a.pk = b.pk Select * from #NewSource pk col2 autokey ----------- ---------- ---------- 1 1 0000000001 2 2 0000000002 3 3 0000000003 10 10 0000000004 Drop Table #Source Drop Table #NewSource Drop Table #TempSource >> How do you insert multiple rows from a select statement while incrementing a unique primary key in chr? <<You missed the foundation of RDBMS and are still thinking in terms of a sequential file system. There is no ordering in a table,. Relational keys are not invented on the fly; they are discovered in the reality from which you built your data model. You do not understand that a set [table] is not ordered; that is a 1950's magnetic tape concept. Given an (n)-row table, there are (n!) possible ways to put them in a sequence on a magnetic tape. You need to stop programming immediately and do some reading in foundations. You clearly do not know what you are doing. You also need to ignore any kludges you will get from other postings. All they wil do is enable you to kill businesses -- or people. Use the following procedure:
(1) Create a table variable with an identity column. (2) Insert the result set into that table variable in the order needed. (3) Select from the table variable converting the new identity value into a char. Don't use the IDENTITY function of SELECT INTO, use the IDENTITY property of a new table variable. The identity values provided by the IDENTITY function are calculated before the result set is sorted, which I assume is not what you want to accomplish. Inserts in SQL Server, however, are always serialized--that is, the select statement is executed first yeilding a result set in the order specified in an order by clause, then that result set is inserted into a table in that order. That's why you should use the IDENTITY property of a table variable. Note, there are a few articles on MSDN about this that have to do with using a calculated column in an order by clause, so if you need that you should probably take a look at them and make sure your server is patched. Show quote "Rob" <R**@discussions.microsoft.com> wrote in message news:D858A818-297A-4647-8C9E-05FF74C5C5F1@microsoft.com... > How do you insert multiple rows from a select statement while incrementing a > unique primary key in chr? i.e. 00000001 - 00000100 > > What I have tried to no avail, with error: > > A SELECT statement that assigns a value to a variable must not be combined > with data-retrieval operations. > > or > > Violation of PRIMARY KEY constraint > > --declare @tempid varchar(8) > --set @tempid = '00000001' > > INSERT INTO TestDestinationTable( > Employee_ID, > Name > ) > SELECT > -- @tempid = '00' + Cast((@tempid+1) as varchar(8)), > --'00' + Cast(((select top 1 employee_id from TestDestinationTable order by > employee_id desc)+1) as varchar(8)), > 'TestName' > > FROM > TestSourceTable > > -- > Rob Thank you all for the pointers in the right direction.
-- Show quoteRob "Brian Selzer" wrote: > Use the following procedure: > > (1) Create a table variable with an identity column. > (2) Insert the result set into that table variable in the order needed. > (3) Select from the table variable converting the new identity value into a > char. > > Don't use the IDENTITY function of SELECT INTO, use the IDENTITY property of > a new table variable. The identity values provided by the IDENTITY function > are calculated before the result set is sorted, which I assume is not what > you want to accomplish. Inserts in SQL Server, however, are always > serialized--that is, the select statement is executed first yeilding a > result set in the order specified in an order by clause, then that result > set is inserted into a table in that order. That's why you should use the > IDENTITY property of a table variable. > > Note, there are a few articles on MSDN about this that have to do with using > a calculated column in an order by clause, so if you need that you should > probably take a look at them and make sure your server is patched. > > "Rob" <R**@discussions.microsoft.com> wrote in message > news:D858A818-297A-4647-8C9E-05FF74C5C5F1@microsoft.com... > > How do you insert multiple rows from a select statement while incrementing > a > > unique primary key in chr? i.e. 00000001 - 00000100 > > > > What I have tried to no avail, with error: > > > > A SELECT statement that assigns a value to a variable must not be combined > > with data-retrieval operations. > > > > or > > > > Violation of PRIMARY KEY constraint > > > > --declare @tempid varchar(8) > > --set @tempid = '00000001' > > > > INSERT INTO TestDestinationTable( > > Employee_ID, > > Name > > ) > > SELECT > > -- @tempid = '00' + Cast((@tempid+1) as varchar(8)), > > --'00' + Cast(((select top 1 employee_id from TestDestinationTable order > by > > employee_id desc)+1) as varchar(8)), > > 'TestName' > > > > FROM > > TestSourceTable > > > > -- > > Rob > > >  |
|||||||||||||||||||||||
Other interesting topics