Multiple ID's - One insert. - 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

SQL Server Development (2000)

Multiple ID's - One insert.

Author

Topic

TazMania
Yak Posting Veteran

63 Posts

Posted - 2006-03-17 : 05:48:54

Hi,

This has properly been asked about before, but i don't know what to search on, regarding my problem

lets say that i've got 3 different id's selected, which looks something like this.

id number :
2, 5, 10

I then want to make a insert based on these id numbers.

Is that possible ?

The db looks something like this.

id - pk
text - varchar
katid - int

Anyone of you sql guru's who has an solution to this, please ?

Best regards
Taz

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-03-17 : 05:56:12

We need a lot more information, but you can do "multi-row" inserts:

Insert Into TableToInsert (id, text, katid)
select SourceId, SourceText, SourceKatId from SourceTable
where SourceID in (list of ID numbers)

give us you table structure, and the SQL you have so far

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

TazMania
Yak Posting Veteran

63 Posts

Posted - 2006-03-17 : 06:15:12

Thx for you reply. :)

Just got almost to work, totally forgot about the in clause.. hehe

But my table structure looks like this.


PK - MedarbID -	int - 4	
0 - FabrikID - int - 4	
0 - KategoriID - int - 4	
0 - KursusDato - varchar - 5	
0 - MedarbNr - varchar - 50	
0 - MedarbNavn - varchar - 50	
0 - MedarbHalnr - varchar - 50

I just got to thinking of another problem i've got..

That is that the FabrikID and KategoriID will change with some user selected data.

Sample data :



7 - 2 - 1 - 01/06 - 532 - John Nielsen - 1
8 - 2 - 1 - 01/06 - 569 - Hans Erik - 1

so lets say that i want to copy the above data.. the sql statement will look like this :

INSERT INTO medarbData (fabrikID,KategoriID,KursusDato,MedarbNR,Medarbnavn,MedarbHalnr)
SELECT fabrikID,KategoriID,KursusDato,MedarbNR,medarbnavn,MedarbHalnr
FROM MedarbData WHERE MedarbNr IN (532,569)

But now i want to change the fabrikID, kategoriID to :


7 - 3 - 4 - 01/06 - 532 - John Nielsen - 1
8 - 3 - 4 - 01/06 - 569 - Hans Erik - 1

Is that possible ? :)

Best regards
Taz

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-03-17 : 06:48:36

It certainly should be possible. You need to describe how you want to change those numbers, in a SQL query.

Do you want to change the source data (i.e MedarbData )?

Readin your query now, it seems like you are re-inserting into the same table? What is the reason - do you have a denormalized table? Are you sure you want to insert rather than update?


--case to give uptions 
INSERT INTO medarbData 
	(fabrikID,KategoriID,KursusDato,MedarbNR,Medarbnavn,MedarbHalnr) 
SELECT 	 fabrikID,
	case
		when KategoriID = 2 then 3
		else KategoriID
	end as KategoriID,
	case
		when KursusDato = 1 then 4
		else KursusDato
	end as KursusDato,
	MedarbNR,medarbnavn,MedarbHalnr
FROM MedarbData WHERE MedarbNr IN (532,569)

--or hard-coded to give specific new values

INSERT INTO medarbData 
	(fabrikID,KategoriID,KursusDato,MedarbNR,Medarbnavn,MedarbHalnr) 
SELECT 	 fabrikID,
	3 as KategoriID,
	4 as KursusDato,
	MedarbNR,medarbnavn,MedarbHalnr
FROM MedarbData WHERE MedarbNr IN (532,569)

--or update, if that's what you actually need:

Update 	medarbData
set	KategoriID = 3,
	KursusDato = 4
WHERE MedarbNr IN (532,569)

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

TazMania
Yak Posting Veteran

63 Posts

Posted - 2006-03-17 : 06:52:40

Hi Wanderer,

Thx for your reply, just what i needed :)

Best regards
Taz

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-03-17 : 06:56:24

which one? I gave 3 options ...

anyways, glad to be of help

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

TazMania
Yak Posting Veteran

63 Posts

Posted - 2006-03-17 : 07:37:09

hehe.. wups.. to quick there ;)

This one.. ;)


INSERT INTO medarbData 
	(fabrikID,KategoriID,KursusDato,MedarbNR,Medarbnavn,MedarbHalnr) 
SELECT 	 fabrikID,
	3 as KategoriID,
	4 as KursusDato,
	MedarbNR,medarbnavn,MedarbHalnr
FROM MedarbData WHERE MedarbNr IN (532,569)

Best regards
Taz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources