Nested Tables

Hi can you insert nested tables in a table already exsisting in sql server and if so how would i go about do such a thing

Thanks in advance

Quote:

Originally Posted by Taftheman

Hi can you insert nested tables in a table already exsisting in sql server and if so how would i go about do such a thing

Thanks in advance

You can in SQL 2005. See http://msdn2.microsoft.com/en-us/library/ms175659.aspx. You could also insert an XML blob, but this would be more like nesting a row. I would not recomend nexting XML. It uses up too much space because you end up with non-normalized data and makes selects for values in the XML field very slow, at best.

That said, be careful. This is pretty advanced stuff and hard to support long term.

Nested Query Problem

I am writing a stored procedure that has to insert several rows from one table to another. The problem is that the table into which the rows will be inserted, has more columns than the table that the rows come from. When the stored procedure is called, the extra columns in each new row is supposed to be populated by the stored procedure's arguments. Example:

TableA:
Columns: ID, Group, Name, Email, NewletterSubscriber

TableB:
Columns: ID, Name, Email

The arguments provided are Group, and NewsletterSubscriber.

I need to insert into Table A all records from Table B where ID > 1000 and I need to insert the Group and NewsLetterSubscriber arguments at the same time because these columns do not allow nulls.

I think it might be something like

insert into TableA (ID, Group, Name, Email, NewletterSubscriber)
values (
select * from TableB where ID > 1000)

But how do I insert the stored procedure arguments into the correct columns of the rows?

Your insert statement should be something like

insert into TableA(group, newsletterSubscriber)
values (@.arg_group, @.arg_newsletterSubscriber)

|||

As per your example:

You can do the following :

insert TableA (ID, Group, Name, Email, NewletterSubscriber)

select ID,@.Group,Name,Email,@.NewsletterSubscriber from TableB where ID > 1000

Here @.Group and @.NewsletterSubscriber are a arguments from a Stored procedure.

Thanks

Naras.

|||

I think the procedure definition you want is:

Code Snippet

CREATE PROCEDURE TransRecords_AtoB

@.DefGroup AS int,

@.DefNewsLetterSub AS bit

AS

BEGIN

-- Do you need to clear existing records if so uncomment statement below

-- DELETE FROM TableA

-- WHERE [ID] IN (

-- SELECT ID

-- FROM TableB

-- WHERE (ID > 1000)

-- )

-- Insert the required rows

INSERT INTO TableA ([ID], [Group], [Name],

[Email], [NewsLetterSubscriber])

SELECT [ID], @.DefGroup, [Name], [Email], @.DefNewsLetterSub

FROM TableB

WHERE ([ID] > 1000)

END

If you need to remove records already existing for IDs in TableB then use the commented out delete. Change the types of the arguments to match your fields.

I would recomment that you consider changing some of your column names. It is a bad idea to use identifiers that are reserved words (or might become ones). Name and Group fall into that camp and ID is also suspect. These have to be delimited as shown (and this can cause problem with autogenerated SQL in some tools). Use RecID, RecName, RecGroup etc. or something more descriptive.

nested insert?

Can this be done? a nested instert in a trigger or stored proc?Explain|||Originally posted by ispaleny
Explain
I need to insert several checkbox responses into a table, but the only thing that I can think of doing is using a trigger that will will do an insert ofr the first record and then trigger another insert for the next record. But I cannot find anyone on the web that seems to know if that can be done... please let me know or give a little example of how this coul dbe done. thanks

greg|||??
You have several pieces of data in the front end that you want to get into a database - one piece of data per record?

If you insert the first and use a trigger for the rest how is the database going to know what data to insert - it only knows about the datat for the record inserted.

Easiest is to call the insert SP several times from the front end - once for each record - maybe in a transaction.

You could also send a csv lst to the sp in one call and have the SP then do all the inserts.

Need Update Scripts

Hi All!
I need UPDATE scripts for my data. I know several tool's
which create INSERT Scripts ... but this do not help me.
For example i need scripts like that:
UPDATE [tbl] SET fld1 = 'abc' WHERE idxFld = 1
UPDATE [tbl] SET fld1 = 'def' WHERE idxFld = 2
UPDATE [tbl] SET fld1 = 'ghi' WHERE idxFld = 3
.
.
.
Any assistance or suggestions would be appreciated.
Thanks in Advance
SusanneHi John
thanks for you answer.
I need a software or script which created T-SQL Update
statements for different tables/fields (Must be dynamic !
different tables and fields, n records).
Examples (only for demo):
If you choose the master.dbo.sysusers table, the result
must be like that:
UPDATE sysusers SET name = 'xxx' WHERE uid = 1
UPDATE sysusers SET name = 'yyy' WHERE uid = 2
..
.. (for all data)
..
If you choose the master.dbo.sysobjects table, the result
must be like that:
UPDATE sysobjects SET name = 'abc' WHERE id = 1
..
.. (for all data)
..
I need this scripts to send them via eMail and update a
different server/database ! I can't use standard methods
like "UPDATE ... FROM...", DTS, linked servers or
whatever ... i need "simple Update" commands.
Susanne
>--Original Message--
>Hi
>Without knowing more detail it is hard to know or advise
you on what to do.
>If there are a finite number of idxFld values you could
create a lookup
>table and use the FROM clause in the update statement to
populate the new
>values. If this does not help please post DDL and
example data.
>John
>
>"Susanne" <spam@.hotmail.com> wrote in message
>news:04e001c35a5a$85a5add0$a101280a@.phx.gbl...
>> Hi All!
>> I need UPDATE scripts for my data. I know several
tool's
>> which create INSERT Scripts ... but this do not help
me.
>> For example i need scripts like that:
>> UPDATE [tbl] SET fld1 = 'abc' WHERE idxFld = 1
>> UPDATE [tbl] SET fld1 = 'def' WHERE idxFld = 2
>> UPDATE [tbl] SET fld1 = 'ghi' WHERE idxFld = 3
>> .
>> .
>> .
>> Any assistance or suggestions would be appreciated.
>> Thanks in Advance
>> Susanne
>>
>
>.
>|||Hi Susanne !
I think SQL Scripter is the right software for you.
http://www.sqlscripter.com
Regards
Michael
>--Original Message--
>Hi All!
>I need UPDATE scripts for my data. I know several tool's
>which create INSERT Scripts ... but this do not help me.
>For example i need scripts like that:
>UPDATE [tbl] SET fld1 = 'abc' WHERE idxFld = 1
>UPDATE [tbl] SET fld1 = 'def' WHERE idxFld = 2
>UPDATE [tbl] SET fld1 = 'ghi' WHERE idxFld = 3
>..
>..
>..
>Any assistance or suggestions would be appreciated.
>Thanks in Advance
>Susanne
>
>.
>|||Hi
I guess you could write this yourself using dynamic SQL. As you seem to have
to do this regularly, I have the feeling that what may be required is a
redesign of the database(s) as they are probably not normalised.
John
"Susanne" <spam@.hotmail.com> wrote in message
news:043e01c35a63$ccc48840$a601280a@.phx.gbl...
> Hi John
> thanks for you answer.
> I need a software or script which created T-SQL Update
> statements for different tables/fields (Must be dynamic !
> different tables and fields, n records).
> Examples (only for demo):
> If you choose the master.dbo.sysusers table, the result
> must be like that:
> UPDATE sysusers SET name = 'xxx' WHERE uid = 1
> UPDATE sysusers SET name = 'yyy' WHERE uid = 2
> ..
> .. (for all data)
> ..
> If you choose the master.dbo.sysobjects table, the result
> must be like that:
> UPDATE sysobjects SET name = 'abc' WHERE id = 1
> ..
> .. (for all data)
> ..
>
> I need this scripts to send them via eMail and update a
> different server/database ! I can't use standard methods
> like "UPDATE ... FROM...", DTS, linked servers or
> whatever ... i need "simple Update" commands.
> Susanne
>
>
> >--Original Message--
> >Hi
> >
> >Without knowing more detail it is hard to know or advise
> you on what to do.
> >
> >If there are a finite number of idxFld values you could
> create a lookup
> >table and use the FROM clause in the update statement to
> populate the new
> >values. If this does not help please post DDL and
> example data.
> >
> >John
> >
> >
> >"Susanne" <spam@.hotmail.com> wrote in message
> >news:04e001c35a5a$85a5add0$a101280a@.phx.gbl...
> >> Hi All!
> >> I need UPDATE scripts for my data. I know several
> tool's
> >> which create INSERT Scripts ... but this do not help
> me.
> >>
> >> For example i need scripts like that:
> >> UPDATE [tbl] SET fld1 = 'abc' WHERE idxFld = 1
> >> UPDATE [tbl] SET fld1 = 'def' WHERE idxFld = 2
> >> UPDATE [tbl] SET fld1 = 'ghi' WHERE idxFld = 3
> >> .
> >> .
> >> .
> >>
> >> Any assistance or suggestions would be appreciated.
> >>
> >> Thanks in Advance
> >> Susanne
> >>
> >>
> >
> >
> >.
> >