Mulitple table column values within Single Insert

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

Transact-SQL (2005)

Mulitple table column values within Single Insert

Author

Topic

nash_Sql
Starting Member

6 Posts

Posted - 2010-02-14 : 05:32:38

I am trying to insert mulitple rows into a table.

Id
CreatedTime
AccessId
LibraryId
IsReadOnly

Value for two columns (AccessId,LibraryId) of table should be coming from two different tables (i.e. tblAcessDetails and tblLibraryDetails).
My Query below looks like -
"INSERT INTO table1
SELECT newid(),'2010-01-29 06:35:04',tblAccessDetails.Id,
tblLibraryDetails.Id,’1’
FROM
tblAcessDetails where tblAcessDetails.IsSystem = '1'
Union
tblLibraryDetails where tblLibraryDetails.IssueNumber > 100"

Query part underlined seems to be not working..So does my query require any changes.I want to insert mulitple rows using single sql statement.Both tables are not related.So canot use any join clause.
please give me some suggestion / solution

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-14 : 05:42:01

Some example data would be helpful also wanted result.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-14 : 11:24:14

[code]
"INSERT INTO table1
SELECT newid(),'2010-01-29 06:35:04',tblAccessDetails.Id,
tblLibraryDetails.Id,’1’
FROM
tblAcessDetails where tblAcessDetails.IsSystem = '1'
Union
SELECT relevant columns....
tblLibraryDetails where tblLibraryDetails.IssueNumber > 100"
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-14 : 15:00:27

quote:
Originally posted by visakh16

"INSERT INTO table1
SELECT newid(),'2010-01-29 06:35:04',tblAccessDetails.Id,
tblLibraryDetails.Id,’1’
FROM
tblAcessDetails where tblAcessDetails.IsSystem = '1'
Union
SELECT relevant columns....
tblLibraryDetails where tblLibraryDetails.IssueNumber > 100"

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sorry but that looks senseless to me...

No, you're never too old to Yak'n'Roll if you're too young to die.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-02-14 : 15:10:46

Without any relations between tables, it is seemless.If you UNION you will have null values for either One table or Other.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 08:13:43

quote:
Originally posted by webfred

quote:
Originally posted by visakh16

"INSERT INTO table1
SELECT newid(),'2010-01-29 06:35:04',tblAccessDetails.Id,
tblLibraryDetails.Id,’1’
FROM
tblAcessDetails where tblAcessDetails.IsSystem = '1'
Union
SELECT relevant columns....
tblLibraryDetails where tblLibraryDetails.IssueNumber > 100"

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sorry but that looks senseless to me...

No, you're never too old to Yak'n'Roll if you're too young to die.

Oops sorry I didnt read question carefully. I thought OP was asking on syntax error. reading again I think what OP is looking at is


INSERT INTO table1 
SELECT newid(),'2010-01-29 06:35:04',tblAccessDetails.Id,
tblLibraryDetails.Id,’1’
FROM 
tblAcessDetails 
join tblLibraryDetails  
on tblAcessDetails.linkingcolumn=tblLibraryDetails.linkingcolumn  
where tblAcessDetails.IsSystem = '1'

linking column is column by which tow tables are linked

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nash_Sql
Starting Member

6 Posts

Posted - 2010-02-16 : 02:31:40

quote:
Originally posted by visakh16

quote:
Originally posted by webfred

quote:
Originally posted by visakh16

"INSERT INTO table1
SELECT newid(),'2010-01-29 06:35:04',tblAccessDetails.Id,
tblLibraryDetails.Id,’1’
FROM
tblAcessDetails where tblAcessDetails.IsSystem = '1'
Union
SELECT relevant columns....
tblLibraryDetails where tblLibraryDetails.IssueNumber > 100"

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sorry but that looks senseless to me...

No, you're never too old to Yak'n'Roll if you're too young to die.

Oops sorry I didnt read question carefully. I thought OP was asking on syntax error. reading again I think what OP is looking at is

INSERT INTO table1
SELECT newid(),'2010-01-29 06:35:04',tblAccessDetails.Id,
tblLibraryDetails.Id,’1’
FROM
tblAcessDetails
join tblLibraryDetails
on tblAcessDetails.linkingcolumn=tblLibraryDetails.linkingcolumn
where tblAcessDetails.IsSystem = '1'

linking column is column by which tow tables are linked

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tblAcessDetails.linkingcolumn=tblLibraryDetails.linkingcolumn
cannot used as both tables are not related.

My new table is kind of mapping table between two tables.SO it make sense to have it for my requirement.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 02:36:08

what? how do you link two tables that doesnt have any relation? what sense does that make? if its a mapping table b/w two tables shouldnt it have relation with both tables be means of fk columns?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nash_Sql
Starting Member

6 Posts

Posted - 2010-02-16 : 03:46:35

quote:
Originally posted by visakh16

what? how do you link two tables that doesnt have any relation? what sense does that make? if its a mapping table b/w two tables shouldnt it have relation with both tables be means of fk columns?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

My new mapping table do have relation ship with tblAcessDetails and tblLibraryDetails.But these two tables - tblAcessDetails and tblLibraryDetails are not related to each other.That's why i am going for a mapping table.

I commented because i saw this -
"tblAcessDetails join tblLibraryDetails
on tblAcessDetails.linkingcolumn=tblLibraryDetails.linkingcolumn"
Since both are not related i don't have any linking column b/w tblAcessDetails and tblLibraryDetails.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 03:49:37

then why dont use mapping table in between like


...
tblAcessDetails 
join mappingtbl
on mappingtbl.tblAcessDetailsfk= tblAcessDetails.originalcol
join tblLibraryDetails 
on tblLibraryDetails.originalcol=mappingtbl.tblLibraryDetailsfk
....

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nash_Sql
Starting Member

6 Posts

Posted - 2010-02-16 : 04:33:56

quote:
Originally posted by visakh16

then why dont use mapping table in between like

...
tblAcessDetails
join mappingtbl
on mappingtbl.tblAcessDetailsfk= tblAcessDetails.originalcol
join tblLibraryDetails
on tblLibraryDetails.originalcol=mappingtbl.tblLibraryDetailsfk
....

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

If you look back to my first post,i am trying to insert mulitple rows to my mapping table.So how can i join on mapping table ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 04:38:09

oh ok...so reqmnt was population of mapping table. in that case you should have some inputs from user to determine relation right? how are currently entering those data to two main tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nash_Sql
Starting Member

6 Posts

Posted - 2010-02-18 : 00:10:08

quote:
Originally posted by visakh16

oh ok...so reqmnt was population of mapping table. in that case you should have some inputs from user to determine relation right? how are currently entering those data to two main tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I got the answer.Thanks for help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 00:54:59

quote:
Originally posted by nash_Sql

quote:
Originally posted by visakh16

oh ok...so reqmnt was population of mapping table. in that case you should have some inputs from user to determine relation right? how are currently entering those data to two main tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I got the answer.Thanks for help

Can we see final working code then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources