October 4, 2012 at 2:52 pm
create table dbo.employee
(
empid int primary key,
name varchar(200),
city varchar(100)
)
select * from dbo.employee
insert into dbo.employee values (2,'tony','arlington'), (4,'Jason','Irving'), (5,'Alden','Denvor'),(8,'Lampart','Dallas')
--
create table #newuser
(
name varchar(200),
city varchar(100)
)
select * from #newuser
insert into #newuser values ('Kathy','Dallas'), ('Sriya','Bombay'),('Manny','copercristi'),('Harry','London')
-------------------
-- I am trying to insert from #newuser temp table to dbo.employee,
-- Requirement is find max empid from dbo.employee then, add 100 and insert data from #newuser temp to dbo.emply
--Output of dbo.employee after inserting it should looking like following:
empid name city
2tony arlington
4Jason Irving
5Alden Denvor
8Lampart Dallas
108Kathy Dallas
109Sriya Bombay
110Manny copercristi
111Harry London
I try to do like below, but my logic do not works
--declare @no_row int
--declare @count int
--set @count = 1
--select @no_row = count(*) from #newuser
--while @count <= @no_row
--begin
--insert into dbo.employee (empid, name, city)
--select @a, name, city from #newuser
--set @count = @count + 1
--end
October 4, 2012 at 3:35 pm
The logic that Praveena suggested is far better than looping for this. There is no need for looping here at all. There is no reason to be concerned by the order of the inserts. If data needs to be ordered it needs to be done when selecting it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 4, 2012 at 3:44 pm
This is what I did finally and it works,
declare @a int
select @a = MAX(empid)+100 from dbo.employee
declare @no_row int
declare @count int
set @count = 1
select @no_row = count(*) from #newuser
while @count <= @no_row
begin
insert into dbo.employee (empid, name, city)
select @a, t.name, t.city from (
select ROW_NUMBER() over(order by name) 'serialno', * from #newuser) as t where t.serialno = @count
set @count = @count + 1
end
October 4, 2012 at 3:45 pm
praveen , thank you so much it same whole lot of code
October 4, 2012 at 3:45 pm
Yes, I got answer but its long and it's in look.
October 4, 2012 at 10:41 pm
venus.pvr (10/4/2012)
Not sure if the order of the inserts matter. Else the below would work:DECLARE @a INT
SELECT @a=MAX(empid)+99 FROM dbo.employee
INSERT INTO dbo.employee(empid,name,city)
SELECT @a+ROW_NUMBER() OVER (ORDER BY name),name,city FROM #newuser
-Praveena
I would say that this is ill-advised on a high transaction volume system, where some other process may INSERT into employee between the two SQL statements.
Better might be (omitting appropriate error handling):
BEGIN TRANSACTION T1
DECLARE @a INT
SELECT @a=MAX(empid)+99
FROM dbo.employee WITH(UPDLOCK)
INSERT INTO dbo.employee(empid,name,city)
SELECT @a+ROW_NUMBER() OVER (ORDER BY name),name,city
FROM #newuser
COMMIT TRANSACTION T1
Or an alternative if you don't want to use a transaction:
;WITH MaxID AS (
SELECT a=MAX(empid)+99
FROM dbo.employee
)
INSERT INTO dbo.employee(empid,name,city)
SELECT a+ROW_NUMBER() OVER (ORDER BY name),name,city
FROM #newuser
CROSS APPLY MaxID
Edit: Removed naming of the ROW_NUMBER as it's not required.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 5, 2012 at 9:17 am
Thanks, it's even shorter.
October 5, 2012 at 9:17 am
Thanks for your help
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply