insert two record - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

insert two record

Author

Topic

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2006-10-27 : 06:51:06

hi

how can i insert two record and change the value
eg i have 500 records

col1 col2
abc 101
def 201
ghi 301
till 500 records
i need to insert
col1 col2
abc 101
abc 102
abc 103
def 201
def 202
def 203
ghi 301
ghi 302
ghi 303

thanxs

coorgi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-27 : 06:53:12

Will you try to explain a little more? 500 records of what?
abc101 to abc600?
What happens after ghi?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-27 : 07:00:54

or do you mean something like this?

declare	@test table (original bit, col1 varchar(3), col2 int)

insert	@test (original, col1, col2)
select	1, 'abc', 101 union all
select	1, 'def', 201 union all
select	1, 'ghi', 301

select * from @test order by col1, col2

insert		@test (col1, col2)
select		t1.col1,
		t2.col2
from		@test t1
cross join	@test t2
left join	@test t on t.col1 = t1.col1 and t.col2 = t2.col2
where		t.col1 is null

select * from @test order by col1, col2

Peter Larsson
Helsingborg, Sweden

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2006-10-27 : 07:06:27

hi
peter i should thank u for sloving my pervious query

ok

see i have 500 records
say table car

column1 column2
abc 101
def 201
ghi 301
....
....

zzz 999
500 rows selected
in the table

i need to insert record after each or one row/record say abc 101 to abc 101,abc 102,abc 103 then def 201 to def 201,def 202,def 201 then ghi 301 to ghi 301,ghi 302,ghi 303 till the end of the record zzz 999,zzz 1000,zzz 1001

thanxs

coorgi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-27 : 07:09:12

You still is not clear what happens after ghi. Does jkl come after that?
Or after ghi303 it is zzz101?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-27 : 07:12:08

Something like this?

declare	@test table (original bit, col1 varchar(3), col2 int)

insert	@test (original, col1, col2)
select	1, 'abc', 101 union all
select	1, 'def', 201 union all
select	1, 'zzz', 999 union all
select	1, 'ghi', 301

select * from @test order by col1, col2

insert		@test (col1, col2)
select		col1,
		1 + max(col2)
from		@test
group by	col1

select * from @test order by col1, col2

Peter Larsson
Helsingborg, Sweden

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2006-10-27 : 07:16:34

no it will be jkl mno pqr goes on till 500 rows
i ran ur query out put is
1 abc 101
NULL abc 201
NULL abc 301
NULL def 101
1 def 201
NULL def 301
NULL ghi 101
NULL ghi 201
1 ghi 301

i need to get
abc 101
abc 102
abc 103
def 201
def 202
def 203
ghi 301
ghi 302
ghi 303
till zzz
thanxs

coorgi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-27 : 07:24:19

Ok, so you have abc, def, ghi. And then jkl, mno, pqr, stu, vwx, yz?

DEFINE THE RIGHT ORDER, PLEASE!!!!!!! Do you expect us to read your mind?

Also, do you need only three records for each "group"?
You have 101-103, 201-203 and 301-303.
And then what? 401-403, 501-503, 601-603, 701-703, 801-803, 901-903 and 1001-1003?

Peter Larsson
Helsingborg, Sweden

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2006-10-27 : 07:38:11

hi peter thanxs its correct changed little

declare @test table ( col1 varchar(3), col2 int)

insert @test (col1, col2)
select 'abc', 101 union all
select 'def', 201 union all
select 'zzz', 999 union all
select 'ghi', 301

select * from @test order by col1, col2

insert @test (col1, col2)
select col1,
1 + max(col2)
from @test
group by col1

insert @test (col1, col2)
select col1,
1 + max(col2)
from @test
group by col1

select * from @test order by col1, col2

out put is

abc 101
abc 102
abc 103
def 201
def 202
def 203
ghi 301
ghi 302
ghi 303
zzz 999
zzz 1000
zzz 1001

this is correct

but one small request peter i will only send column name so that i dont need to put
insert @test (col1, col2)
select 'abc', 101 union all
select 'def', 201 union all
select 'zzz', 999 union all
select 'ghi', 301
for 500 to thousand of records

thanxs once agin

coorgi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-27 : 07:56:03

Please!? Why don't you put in a little effort yourself?

insert @test (col1, col2)
select @colname,
1 + max(col2)
from @test where col1 = @colname

Peter Larsson
Helsingborg, Sweden

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2006-10-27 : 08:47:09

declare @test table ( col1 varchar(3), col2 int)
declare @colname varchar(8)

select * from @test order by col1, col2

insert @test (col1, col2)
select @colname,
1 + max(col2)
from @test where col1 = col2

insert @test (col1, col2)
select @colname,
1 + max(col2)
from @test where col1 = col2

select * from @test order by col1, col2

coorgi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-27 : 09:01:13

This makes no sense at all!
col1 will never be equal to col2. Or?

Why aren't you able to understand and listen?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-27 : 09:15:15

And most important, why don't you learn?

Peter Larsson
Helsingborg, Sweden

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2006-10-27 : 09:15:36

one sec i think i posted wrong sorry

coorgi

mr_mist
Grunnio

1870 Posts

Posted - 2006-10-27 : 09:39:54

WHY are you doing this?

-------
Moo. :)

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2006-10-27 : 09:43:08

hi
sorry peter for miss interpeting some info
i have data

col1 col2 col3 col4

PM 1 MONTH PLAN FOR IMPORTANT EQPTS CAR11MBH05AC201-M3 11MBH05AC201-M3
PM 1 MONTH PLAN FOR IMPORTANT EQPTS CAR11MBH05AC201-F1 11MBH05AC201-F1
PM 1 MONTH PLAN FOR IMPORTANT EQPTS CAR11MBH05AC201-F2 11MBH05AC201-F2
PM 1 MONTH PLAN FOR IMPORTANT EQPTS CAR11MBH05AC201-F3 11MBH05AC201-F3
PM 1 MONTH PLAN FOR CRITICAL EQPTS CAR11MBV01AP401 11MBV01AP401
PM 1 MONTH PLAN FOR CRITICAL EQPTS CAR11MAY02AA151 11MAY02AA151
PM 1 MONTH PLAN FOR CRITICAL EQPTS CAR11MAY02AA152 11MAY02AA152
PM 1 MONTH PLAN FOR CRITICAL EQPTS CAR11MBV01AP401 11MBV01AP401

i need to INSERT col3 only for first 5 charcters eg CAR11MBH05AC201-M3 TO CAR11MBH05AC201-M3,CAR12MBH05AC201-M3,CAR13MBH05AC201-M3

FOR Functional location COLUMN

coorgi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-27 : 09:49:58

You really, really, really need to normalize your database data.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-27 : 09:56:00

[code]declare @test table (col1 varchar(4), col2 varchar(30), col3 varchar(18), col4 varchar(15))

insert @test
select 'PM 1', 'MONTH PLAN FOR IMPORTANT EQPTS', 'CAR11MBH05AC201-M3', '11MBH05AC201-M3' union all
select 'PM 1', 'MONTH PLAN FOR IMPORTANT EQPTS', 'CAR11MBH05AC201-F1', '11MBH05AC201-F1' union all
select 'PM 1', 'MONTH PLAN FOR IMPORTANT EQPTS', 'CAR11MBH05AC201-F2', '11MBH05AC201-F2' union all
select 'PM 1', 'MONTH PLAN FOR IMPORTANT EQPTS', 'CAR11MBH05AC201-F3', '11MBH05AC201-F3' union all
select 'PM 1', 'MONTH PLAN FOR CRITICAL EQPTS', 'CAR11MBV01AP401', '11MBV01AP401' union all
select 'PM 1', 'MONTH PLAN FOR CRITICAL EQPTS', 'CAR11MAY02AA151', '11MAY02AA151' union all
select 'PM 1', 'MONTH PLAN FOR CRITICAL EQPTS', 'CAR11MAY02AA152', '11MAY02AA152' union all
select 'PM 1', 'MONTH PLAN FOR CRITICAL EQPTS', 'CAR11MBV01AP401', '11MBV01AP401'

select * from @test

insert @test
select col1,
col2,
car + newserial + oldcol3,
newserial + oldcol4
from (
select col1,
col2,
left(col3, 3) car,
convert(varchar, 1 + cast(substring(col3, 4, 2) as int)) newserial,
substring(col3, 6, 8000) oldcol3,
substring(col4, 3, 8000) oldcol4
from @test
) x

select * from @test[/code]

Peter Larsson
Helsingborg, Sweden

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2006-10-27 : 09:56:35

STRUCTURE OF TABLE
CREATE TABLE Criticaland_INportant
([F1] [float],
[Mainenance Plan category] [nvarchar] (255) ,
[Maintenace Plan Description] [nvarchar] (255) ,
[Functional location] [nvarchar] (255) ,
[Equipment No KKS] [nvarchar] (255) ,
[Equipment internal number] [nvarchar] (255) ,
[Tasklist Type] [nvarchar] (255) ,
[Tasklist Group] [nvarchar] (255) ,
[Group Counter] [float] ,
[Maintenance cycle] [nvarchar] (255) ,
[Cycle Unit] [nvarchar] (255) ,
[Call horizon] [float] ,
[Scheduling period] [float],
[Unit] [nvarchar] (255) ,
[Completion Requirement] [nvarchar] (255) ,
[F16] [nvarchar] (255)

)

coorgi

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2006-10-27 : 10:07:19

hi

Insert query for two one after another two like eg: CAR11MBH05AC201-F2,
CAR12MBH05AC201-F2,CAR13MBH05AC201-F2
not CAR11MBH05AC201-F2,CAR12MBH05AC201-F2
actually it should be 24 records

thanxs

coorgi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-27 : 10:12:52

Here we go again...

declare @test table (col1 varchar(4), col2 varchar(30), col3 varchar(18), col4 varchar(15))

insert	@test
select	'PM 1', 'MONTH PLAN FOR IMPORTANT EQPTS', 'CAR11MBH05AC201-M3', '11MBH05AC201-M3' union all
select	'PM 1', 'MONTH PLAN FOR IMPORTANT EQPTS', 'CAR11MBH05AC201-F1', '11MBH05AC201-F1' union all
select	'PM 1', 'MONTH PLAN FOR IMPORTANT EQPTS', 'CAR11MBH05AC201-F2', '11MBH05AC201-F2' union all
select	'PM 1', 'MONTH PLAN FOR IMPORTANT EQPTS', 'CAR11MBH05AC201-F3', '11MBH05AC201-F3' union all
select	'PM 1', 'MONTH PLAN FOR CRITICAL EQPTS', 'CAR11MBV01AP401', '11MBV01AP401' union all
select	'PM 1', 'MONTH PLAN FOR CRITICAL EQPTS', 'CAR11MAY02AA151', '11MAY02AA151' union all
select	'PM 1', 'MONTH PLAN FOR CRITICAL EQPTS', 'CAR11MAY02AA152', '11MAY02AA152' union all
select	'PM 1', 'MONTH PLAN FOR CRITICAL EQPTS', 'CAR11MBV01AP401', '11MBV01AP401'

select * from @test

insert	@test
select	col1,
	col2,
	car + newserial + oldcol3,
	newserial + oldcol4
from	(
		select	col1,
			col2,
			left(col3, 3) car,
			convert(varchar, 1 + cast(substring(col3, 4, 2) as int)) newserial,
			substring(col3, 6, 8000) oldcol3,
			substring(col4, 3, 8000) oldcol4
		from	@test
	) x

insert	@test
select	col1,
	col2,
	car + newserial + oldcol3,
	newserial + oldcol4
from	(
		select	col1,
			col2,
			left(col3, 3) car,
			convert(varchar, 1 + cast(substring(col3, 4, 2) as int)) newserial,
			substring(col3, 6, 8000) oldcol3,
			substring(col4, 3, 8000) oldcol4
		from	@test
	) x

select * from @test

Peter Larsson
Helsingborg, Sweden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources