Showing posts with label insert. Show all posts
Showing posts with label insert. Show all posts

Sunday, March 25, 2012

Copying tables each other but with new row numbers or something like that

Take T4.ProductID , T4.ProductName from T4 where T4.CartID = @.CartID
.IF EXISTS
.Insert Into T1 values(T4.ProductId) and Get the T1.RowNo (T1.RowNo autoincrement column)
.Take T5.ProductID , T5.ProductGroup where T5.ProductID = T4.ProductID
.IF EXISTS
.INSERT INTO T2 values(T1.RowNo,T5.ProductGroup)
.Take T6.ProductID,T6.ProductGroup,T6.ProductDetail Where T6.ProductID = T4.ProductName AND T6.ProductGroup = T5.ProductGroup
.IF EXISTS
.INSERT INTO T3 values (T1.RowNo,T5.ProductGroup,T6.ProductDetail)

I Hope i am clear. It is very confusing but try to thing. I really need help. I am trying to make a shopping site.
T1 stores ProductID, this table is ShoppingCart
T2 stores DetailGroup of Product stored in T1.
T3 stores Detail of Product.


T1 - T2 - T3
EX: 1,Samsung HDD - 1,External - 1,External,750GB

User can save this cart. When user saves cart, tables are copying each other. T1 to T4 , T2 to T5 , T3 to T6. So everything is ok up to this point. But when user try to add his/her saved cart to current cart problem begins.

T4 - T5 - T6
EX: 1,Samsung HDD - 1,External - 1,External,750GB

When user adds a product, it is inserted into T1 as a new row and first column(T1.RowNo) is autoincrement. I need to copy user cart values from T1 to T4 and get the inserted rows new RowNo and use it when inserteing the T5 into T2 and T6 into T3. I hopw i am clear. I try to write code but it was very long. If you need code please let me know. I REALLY NEED HELP .

AND

Another simple question. Are stored procedures takes values that comes from another stored procedure as Table. I mean SP1 returns a table. And SP2 takes that table row by row and makes the operation?

Ex: you are calling SP2 5 times for different 5 values(nvarchar(50),int). Likewise can you call SP2 one time and getting a Table whisch stores 5 nvarchar(50),int value as rows.

Happy Coding

"MasterG"

Surely your table names are NOT "T1", T2", etc.

|||Sure names are not T1 or T2 etc. But is it important? Just a table name. Why did you asked that?

Tuesday, March 20, 2012

Copying Records into a different table using triggers

I am in the process of writing a trigger that will insert a new record into
another table when a record is added to a specific table. I was wondering if
there is a way to just 'copy' the entire row without using INSERT INTO
...and VALUES?
adv-thanks-anceYou could use
INSERT INTO [AnotherTable]
SELECT * FROM inserted
assuming that [AnotherTable] has the same number of columns, in the same
order, with the same datatypes as the table that has the trigger on it.
"CSHARPITPRO" wrote:

> I am in the process of writing a trigger that will insert a new record int
o
> another table when a record is added to a specific table. I was wondering
if
> there is a way to just 'copy' the entire row without using INSERT INTO
> ...and VALUES?
> adv-thanks-ance|||Thanks Mark,
Is this being copied from the 'INSERTED' table in SQL?
"Mark Williams" wrote:
> You could use
> INSERT INTO [AnotherTable]
> SELECT * FROM inserted
> assuming that [AnotherTable] has the same number of columns, in the same
> order, with the same datatypes as the table that has the trigger on it.
> --
> "CSHARPITPRO" wrote:
>|||I am not sure if there is a way to do this (I have not seen one) but I would
avoid this like the plague.
The tables are identical today, but who knows what will change tomorrow.
Something as simple as adding and audit or archive date to either table
would break any app using such an approach. Structures have a tendency to
evolve over time, and any code that assumes two structures will be identical
is begging for trouble down the line.
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:0078FFA9-4BEB-4A92-9949-CA8B733E7B63@.microsoft.com...
> I am in the process of writing a trigger that will insert a new record
into
> another table when a record is added to a specific table. I was wondering
if
> there is a way to just 'copy' the entire row without using INSERT INTO
> ...and VALUES?
> adv-thanks-ance|||Yes. If all you want to do is a straight copy of the rows inserted into the
table with the trigger on it, you can use the syntax
INSERT INTO [AnotherTable] SELECT * FROM inserted
inserted is the virtual table that conains the inserted rows that caused the
trigger to fire. Note that inserted will also contain rows when an UPDATE is
performed (The new values will be in the inserted virtual table, and the old
values will be in the deleted virtual table).
--
If"CSHARPITPRO" wrote:
> Thanks Mark,
> Is this being copied from the 'INSERTED' table in SQL?
> "Mark Williams" wrote:
>|||Thanks Jim,
What do you think the best method is to copy the 'changed' records to
my AuditTable with out using the INSERTED Table? Should I just use INSERT
INTO ..and VALUES?
Thanks
"Jim Underwood" wrote:

> I am not sure if there is a way to do this (I have not seen one) but I wou
ld
> avoid this like the plague.
> The tables are identical today, but who knows what will change tomorrow.
> Something as simple as adding and audit or archive date to either table
> would break any app using such an approach. Structures have a tendency to
> evolve over time, and any code that assumes two structures will be identic
al
> is begging for trouble down the line.
>
>
> "CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
> news:0078FFA9-4BEB-4A92-9949-CA8B733E7B63@.microsoft.com...
> into
> if
>
>|||I would use insert into with a specific list of values.
i.e. Insert into table1 (field1, field2, field3) values (select field1,
field2, field3 from inserted)
As everyone else mentioned you can use "insert into table select * from
inserted", but if any structures change you will runinto trouble.
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:C1DFF71C-B888-42FC-A6EB-E115C2C25EC0@.microsoft.com...
> Thanks Jim,
> What do you think the best method is to copy the 'changed' records to
> my AuditTable with out using the INSERTED Table? Should I just use INSERT
> INTO ..and VALUES?
> Thanks
> "Jim Underwood" wrote:
>
would
to
identical
wondering|||Yes. Performance and consistency will both be served if you do so. It does
require a bit more work now, and a bit more when you make changes, but if
you design things, this won't be too much of a problem. You can also use
the information_schema.columns view to get the columns that are needed and
build a quicky insert generator (or use the ones in QA/SSMS) to do much of
the hard work of typing out column names.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:C1DFF71C-B888-42FC-A6EB-E115C2C25EC0@.microsoft.com...
> Thanks Jim,
> What do you think the best method is to copy the 'changed' records to
> my AuditTable with out using the INSERTED Table? Should I just use INSERT
> INTO ..and VALUES?
> Thanks
> "Jim Underwood" wrote:
>|||On Wed, 18 Jan 2006 09:23:03 -0800, CSHARPITPRO wrote:

>Thanks Jim,
> What do you think the best method is to copy the 'changed' records to
>my AuditTable with out using the INSERTED Table? Should I just use INSERT
>INTO ..and VALUES?
Hi CSHARPITPRO,
No. INSERT ... VALUES inserts just one row at a time; a trigger can be
fired with a large number of rows in the inserted pseudo-table.
Use
INSERT INTO Tablename (Column1, Column2, ..., ColumnN)
SELECT Column1, Column2, ..., ColumnN
FROM inserted
Hugo Kornelis, SQL Server MVP

Thursday, March 8, 2012

copying data from one table to a new one with some different data types

Is it possible to easily copy data from one table to another if the data types don't match.

I know you can do a INSERT INTO table1(col1,col2) SELECT (col2,col7) FROM table2 if the data types match but is there a way to do this if they don't.

I'm not trying to copy date times into bit fields or anything. I just have an old table that I built when I really didn't know what I was doing now I at least

think I have a better understanding of what data types to use, so I was wanting to move the data in the orignal table to my new one. Most of the fields in the old

database are text datatypes and the new database is nvarchar(50) data types.

Thanks for any suggestions.


If the datatypes are "compatible" then you should be okay. As you mentioned moving from text datatype to nvarchar should be okay. Try using the BCP utility if you have millions of rows in the table.|||

I tried doing that and it gives an error saying string or binary data may be lost so the execution was cancelled.

I was able to do what I needed to by coding up a dummy method in C# that read everything into a datatable and then wrote it out to the new database, but this would be something nice to know how to do in T-Sql. Any idea why I was getting the error above. The only other two data types in the table are datetime and bit, but those fields match exactly no conversion should be necessary so the only reason I can think I'm getting that error is because I'm trying to convert a text field to a nvarchar().

Any way thanks for the help ndinakar!

|||

Try this in your query analyzer:

DECLARE @.tTABLE ( col1INT IDENTITY, col2TEXT, col3BIT )INSERT INTO @.tVALUES ('aaaaaaaaaaaaaaaaaa',0)SELECT *FROM @.tDECLARE @.t2TABLE (col1INT IDENTITY, col2NVARCHAR(50), col3BIT )INSERT INTO @.t2SELECT t1.col2, t1.col3FROM @.t t1SELECT *FROM @.t2

|||

I was essentially doing what you suggested ndinakar and was getting an error about truncating string or binary values.

I found this KB article with that error and by turning of ANSI WARNINGS I was able to get my data copied

http://support.microsoft.com/default.aspx/kb/255765

Thanks for your help!

Saturday, February 25, 2012

Copy/Paste text into a field

I've encountered a strange problem with the Microsoft SQL Server Management Studio.

When I manually edit a record in a table and want to insert text with more lines, only the first line is copied into the field.

The field type I use is nvarchar(max) but it doesn't really matter what type it is.

The problem only exists in the Management studio. When I perform the same task in the "old" Entreprise Manager it works fine.

Am I doing something wrong or does anybody have a key to whats wrong?

Thanks!

Ken

I don't think you are doing anything wrong. I suspect the edit control in the grid just doesn't support multi-line text.

Please report this issue here: http://lab.msdn.microsoft.com/productfeedback/Default.aspx

We use defect reports and feature requests filed at the feedback center to make decisions about future versions and service packs of SQL Server.

As a work around, you could write a T-SQL update statement to make the change you want. That's how the grid control updates the table behind the scenes.

Thanks,
Steve

|||It's been some time since this was originally posted; I wonder if there's been any action taken on this?
I'm also having a similar problem. I often copy/paste text between database tables (usually a development table and later a production table).
This works fine in Enterprise Manager, but Management Stuido(2005), cells that contain multi-lines are not copied (it seems to be "blowing up" on the crlf; it dosen't seem to be an issue with the length of the text copied).

Its possible that there is a setting somewhere to change the behavior of the Results grid to allow this, but thus far I haven't found anything.
I followed the link provided above, but eventually decided it would be easier to write my own interface than to follow all the trails now necessary submit feedback. :)
At least I can complain here. :)|||

I just did a quick search on Microsoft Connect for any bug that was reported by customers, but did not find anything.

https://connect.microsoft.com/SQLServer/feedback/SearchResults.aspx?SearchQuery=copy+paste

Could you this suggestion on http://connect.microsoft.com/SQLServer/?

Thanks,

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Copy/Paste text into a field

I've encountered a strange problem with the Microsoft SQL Server Management Studio.

When I manually edit a record in a table and want to insert text with more lines, only the first line is copied into the field.

The field type I use is nvarchar(max) but it doesn't really matter what type it is.

The problem only exists in the Management studio. When I perform the same task in the "old" Entreprise Manager it works fine.

Am I doing something wrong or does anybody have a key to whats wrong?

Thanks!

Ken

I don't think you are doing anything wrong. I suspect the edit control in the grid just doesn't support multi-line text.

Please report this issue here: http://lab.msdn.microsoft.com/productfeedback/Default.aspx

We use defect reports and feature requests filed at the feedback center to make decisions about future versions and service packs of SQL Server.

As a work around, you could write a T-SQL update statement to make the change you want. That's how the grid control updates the table behind the scenes.

Thanks,
Steve

|||It's been some time since this was originally posted; I wonder if there's been any action taken on this?
I'm also having a similar problem. I often copy/paste text between database tables (usually a development table and later a production table).
This works fine in Enterprise Manager, but Management Stuido(2005), cells that contain multi-lines are not copied (it seems to be "blowing up" on the crlf; it dosen't seem to be an issue with the length of the text copied).

Its possible that there is a setting somewhere to change the behavior of the Results grid to allow this, but thus far I haven't found anything.
I followed the link provided above, but eventually decided it would be easier to write my own interface than to follow all the trails now necessary submit feedback. :)
At least I can complain here. :)|||

I just did a quick search on Microsoft Connect for any bug that was reported by customers, but did not find anything.

https://connect.microsoft.com/SQLServer/feedback/SearchResults.aspx?SearchQuery=copy+paste

Could you this suggestion on http://connect.microsoft.com/SQLServer/?

Thanks,

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Friday, February 17, 2012

Copy table between SQL servers without logging

Does anyone have a non-DTS method for copying large tables between SQL
servers without logging? bcp and BULK INSERT appear to be for files only.
Is there a way to use them with other SQL tables?Mark
DECLARE @.tablename AS SYSNAME
SET @.tablename = 'pubs..titles'
EXECUTE ('SELECT * INTO TEST FROM OPENQUERY([server], ''SELECT * FROM ' +
@.tablename + ''') AS T')
Note: I assume I have created linked server , may I ask you why not using
DTS?
Also ,this appoach will fail if you have already test table ,what's more it
doesnt transfer pk to the new table.
"Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
news:vtecr7qe5d1o7c@.corp.supernews.com...
> Does anyone have a non-DTS method for copying large tables between SQL
> servers without logging? bcp and BULK INSERT appear to be for files only.
> Is there a way to use them with other SQL tables?
>|||If the destination table doesn't have to already exist, you can use SELECT *
INTO new_table_name to minimize, but not entirely eliminate, logging.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
news:vtecr7qe5d1o7c@.corp.supernews.com...
> Does anyone have a non-DTS method for copying large tables between SQL
> servers without logging? bcp and BULK INSERT appear to be for files only.
> Is there a way to use them with other SQL tables?
>|||Hi,
Make the recovery model for the database as "Simple". After that create a
linked server to connect to remote server.
1. Create the table strucute
2. Use insert into select * from server.db.dbo.tablename
Thanks
Hari
MCDBA
"Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
news:vtecr7qe5d1o7c@.corp.supernews.com...
> Does anyone have a non-DTS method for copying large tables between SQL
> servers without logging? bcp and BULK INSERT appear to be for files only.
> Is there a way to use them with other SQL tables?
>|||Hi Aaron,
I feel we cannot use the command "select * into server.dbname.dbo.temptable
from table " between servers.
Thanks
Hari
MCDBA
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uW3NpDzvDHA.3140@.TK2MSFTNGP11.phx.gbl...
> If the destination table doesn't have to already exist, you can use SELECT
*
> INTO new_table_name to minimize, but not entirely eliminate, logging.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
> news:vtecr7qe5d1o7c@.corp.supernews.com...
> > Does anyone have a non-DTS method for copying large tables between SQL
> > servers without logging? bcp and BULK INSERT appear to be for files
only.
> > Is there a way to use them with other SQL tables?
> >
> >
>|||Uri,
Thanks for your reply. I am not using DTS because the tables being copied
may change, I am using a cursor to loop through the tables to copy, then
executing code within the cursor to copy the data. I am running two
parallel cursors to maximize transfer speed. My issue is that the log file
is growing to 4+Gig during the copy operation on a 56Gig database. We can
not just backup and restore or copy the physical files in our case. As far
as keys, indexes, etc. they are all scripted and added at the appropriate
times. Database recovery model is Simple.
Previously, I have tried breaking up the copy into chunks and performing a
BACKUP LOG WITH TRUNCATE ONLY between chunks, but with all the overhead,
that was incredibly slower. However, it did use minimal log space.
Obviously, disk space concerns are driving this effort. It's unnecessary,
in our situation, to log the data, why do it? I feel like I am forced to do
so.
Mark
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#U0usDzvDHA.1996@.TK2MSFTNGP12.phx.gbl...
> Mark
> DECLARE @.tablename AS SYSNAME
> SET @.tablename = 'pubs..titles'
> EXECUTE ('SELECT * INTO TEST FROM OPENQUERY([server], ''SELECT * FROM ' +
> @.tablename + ''') AS T')
> Note: I assume I have created linked server , may I ask you why not using
> DTS?
> Also ,this appoach will fail if you have already test table ,what's more
it
> doesnt transfer pk to the new table.
>
>
> "Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
> news:vtecr7qe5d1o7c@.corp.supernews.com...
> > Does anyone have a non-DTS method for copying large tables between SQL
> > servers without logging? bcp and BULK INSERT appear to be for files
only.
> > Is there a way to use them with other SQL tables?
> >
> >
>|||Mark
Well ,perhaps you need to use Replications , did you think about it?
"Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
news:vteg11mhsao054@.corp.supernews.com...
> Uri,
> Thanks for your reply. I am not using DTS because the tables being copied
> may change, I am using a cursor to loop through the tables to copy, then
> executing code within the cursor to copy the data. I am running two
> parallel cursors to maximize transfer speed. My issue is that the log
file
> is growing to 4+Gig during the copy operation on a 56Gig database. We can
> not just backup and restore or copy the physical files in our case. As
far
> as keys, indexes, etc. they are all scripted and added at the appropriate
> times. Database recovery model is Simple.
> Previously, I have tried breaking up the copy into chunks and performing a
> BACKUP LOG WITH TRUNCATE ONLY between chunks, but with all the overhead,
> that was incredibly slower. However, it did use minimal log space.
> Obviously, disk space concerns are driving this effort. It's unnecessary,
> in our situation, to log the data, why do it? I feel like I am forced to
do
> so.
> Mark
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#U0usDzvDHA.1996@.TK2MSFTNGP12.phx.gbl...
> > Mark
> > DECLARE @.tablename AS SYSNAME
> > SET @.tablename = 'pubs..titles'
> >
> > EXECUTE ('SELECT * INTO TEST FROM OPENQUERY([server], ''SELECT * FROM '
+
> > @.tablename + ''') AS T')
> >
> > Note: I assume I have created linked server , may I ask you why not
using
> > DTS?
> > Also ,this appoach will fail if you have already test table ,what's more
> it
> > doesnt transfer pk to the new table.
> >
> >
> >
> >
> > "Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
> > news:vtecr7qe5d1o7c@.corp.supernews.com...
> > > Does anyone have a non-DTS method for copying large tables between SQL
> > > servers without logging? bcp and BULK INSERT appear to be for files
> only.
> > > Is there a way to use them with other SQL tables?
> > >
> > >
> >
> >
>|||Yes... and then again, a lot of overhead and additional database limitations
to perform a simple copy. I wish I just had the cash for larger drives.
Thanks for your comments.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#RjlOczvDHA.2712@.TK2MSFTNGP11.phx.gbl...
> Mark
> Well ,perhaps you need to use Replications , did you think about it?
>
> "Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
> news:vteg11mhsao054@.corp.supernews.com...
> > Uri,
> >
> > Thanks for your reply. I am not using DTS because the tables being
copied
> > may change, I am using a cursor to loop through the tables to copy, then
> > executing code within the cursor to copy the data. I am running two
> > parallel cursors to maximize transfer speed. My issue is that the log
> file
> > is growing to 4+Gig during the copy operation on a 56Gig database. We
can
> > not just backup and restore or copy the physical files in our case. As
> far
> > as keys, indexes, etc. they are all scripted and added at the
appropriate
> > times. Database recovery model is Simple.
> >
> > Previously, I have tried breaking up the copy into chunks and performing
a
> > BACKUP LOG WITH TRUNCATE ONLY between chunks, but with all the overhead,
> > that was incredibly slower. However, it did use minimal log space.
> >
> > Obviously, disk space concerns are driving this effort. It's
unnecessary,
> > in our situation, to log the data, why do it? I feel like I am forced
to
> do
> > so.
> >
> > Mark
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:#U0usDzvDHA.1996@.TK2MSFTNGP12.phx.gbl...
> > > Mark
> > > DECLARE @.tablename AS SYSNAME
> > > SET @.tablename = 'pubs..titles'
> > >
> > > EXECUTE ('SELECT * INTO TEST FROM OPENQUERY([server], ''SELECT * FROM
'
> +
> > > @.tablename + ''') AS T')
> > >
> > > Note: I assume I have created linked server , may I ask you why not
> using
> > > DTS?
> > > Also ,this appoach will fail if you have already test table ,what's
more
> > it
> > > doesnt transfer pk to the new table.
> > >
> > >
> > >
> > >
> > > "Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
> > > news:vtecr7qe5d1o7c@.corp.supernews.com...
> > > > Does anyone have a non-DTS method for copying large tables between
SQL
> > > > servers without logging? bcp and BULK INSERT appear to be for files
> > only.
> > > > Is there a way to use them with other SQL tables?
> > > >
> > > >
> > >
> > >
> >
> >
>|||Select * into [New Table name]
With the new name being fully qualified
Will
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

copy subscription

I have to insert a lot of subscription, that look very similiar.

How can I copy one subscription to another?

Or is it possible to use a subscription as a template?

Thanks

Just curious, why do you have so many subscriptions?

Monday, February 13, 2012

Copy rows from a table to a new one using cursor in PL/SQL

Hi there,

can someone explain me how to use a cursor to retrieve all the rows in one table and then insert some of the records into another table? (PL/SQL) Or maybe tell me where I can find a good tutorial or information about how to do it?

Thanx a lot, Fausto

The real problem is the following:

I have a table SPECS with attributes (among others) ID, INP1, INP2, INP3, OUT1, OUT2, OUT3 what means that the number of INP/OUT is restricted to a max of 3.

In order to have as many INP/OUT as needed for each SPEC, I created a new table (SPECS_INFO) with attributes ID (foreign key from table SPECS) , SEQUENCE_NR, INPUT, OUTPUT where I want to copy the information from a row ID, INP1, INP2, INP3, OUT1, OUT2, OUT3 (at the SPECS table) to three rows of the new SPECS_INFO table:

For example:
ID INP1 INP2 INP3 OUT1 OUT2 OUT3
A1 CALL A CALL B CALL C ANSW A ANSW B ANSW C

TO

ID SEQUENCE_NR INPUT OUTPUT
A1 1 CALL A ANSW A
A1 2 CALL B ANSW B
A1 3 CALL C ANSW CTry something like this:

begin
for r in (select * from specs)
loop
insert into specs_info
values (r.id,1,r.inp1,r.out1);
insert into specs_info
values (r.id,2,r.inp2,r.out2);
insert into specs_info
values (r.id,3.r.inp3,r.out3);
end loop;
end;
/

;)|||Thanx a lot. It works fine :-)