Hi,
I need to store data in XML format in the database. When the data is retrieved and printed onto a file, say, it should be automatically printed in a file format i.e. each seperate line on a new line. How can this be done? Can we append a newline character to each line before storing it as a record in the database?/If so, how can this be done?
Thanks in advanceYou can add a Carriage Return and Line Feed to a string as follows:
'your string' + CHAR(13) + CHAR(10)sqlsql
Showing posts with label file. Show all posts
Showing posts with label file. Show all posts
Tuesday, March 27, 2012
ConCat data
I need to create a csv file where it's just one giant file.
There is only one field I pull from a table called USerID(Char8). Then for
every record in the table create a file like this.
Receipents-c/n=XXXXX%Receipents-c/n=XXXXXReceipents-c/n=XXXXXReceipents-c/n=
XXXXX This will then Import into Exchange for a Distrubution List. Any IdeasSee if this link gives you some ideas
http://www.rac4sql.net/xp_execresultset.asp
Anith|||Would that not put each order on a separate line.
I need it all strung together ..as one big file...
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23QJSioLBFHA.936@.TK2MSFTNGP12.phx.gbl...
> See if this link gives you some ideas
> http://www.rac4sql.net/xp_execresultset.asp
> --
> Anith
>|||Anith has pointed you to a trick to create a file for each record/row for
your table.
Look like you want to concatenate the rows into a single string? If so, it's
probably best to do it from the client side (i.e. vb/script/etc).
Though, I am bit
about your comment regarding CSV in your first
post. Perhaps, you want to clarify so we can help.
-oj
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:L92dnbdsqJ2N6WTcRVn-gg@.kconline.com...
> Would that not put each order on a separate line.
> I need it all strung together ..as one big file...
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:%23QJSioLBFHA.936@.TK2MSFTNGP12.phx.gbl...
>|||sorry .. I just meant to have a csv extension to the filename.
do you have an example vbscript to concat these records from the sql table
?
thanks again.
"oj" <nospam_ojngo@.home.com> wrote in message
news:%23C0Ko0MBFHA.3492@.TK2MSFTNGP12.phx.gbl...
> Anith has pointed you to a trick to create a file for each record/row for
> your table.
> Look like you want to concatenate the rows into a single string? If so,
> it's probably best to do it from the client side (i.e. vb/script/etc).
> Though, I am bit about your comment regarding CSV in your first
> post. Perhaps, you want to clarify so we can help.
> --
> -oj
>
> "HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
> news:L92dnbdsqJ2N6WTcRVn-gg@.kconline.com...
>|||Here is a vbscript.
Main()
Sub Main()
Dim sqlcnt,rs,s
s="Begin"
Set cntsql = CreateObject("ADODB.Connection")
With cntsql
.provider = "SQLOLEDB"
.connectionstring = "Data Source=.\dev;integrated security=SSPI"
.Open
Set rs = .Execute("select OrderID from Northwind..Orders")
Do Until rs.EOF
s = s & rs.Fields("OrderID") & ","
rs.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set cntsql = Nothing
s = s & "End"
Call WriteToFile(s)
End Sub
Function WriteToFile(s)
Dim fso, tf
Set fso = CreateObject("Scripting.FileSystemObject")
Set tf = fso.CreateTextFile("c:\test.csv", True)
tf.Write(s)
tf.Close()
Set fso= Nothing
Set tf= Nothing
End Function
-oj
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:ZLydnddhH-JSVWTcRVn-tw@.kconline.com...
> sorry .. I just meant to have a csv extension to the filename.
> do you have an example vbscript to concat these records from the sql table
> ?
> thanks again.
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23C0Ko0MBFHA.3492@.TK2MSFTNGP12.phx.gbl...
>|||Thanks again.
"oj" <nospam_ojngo@.home.com> wrote in message
news:uyuNXkQBFHA.3368@.TK2MSFTNGP10.phx.gbl...
> Here is a vbscript.
> Main()
> Sub Main()
> Dim sqlcnt,rs,s
> s="Begin"
> Set cntsql = CreateObject("ADODB.Connection")
> With cntsql
> .provider = "SQLOLEDB"
> .connectionstring = "Data Source=.\dev;integrated security=SSPI"
> .Open
> Set rs = .Execute("select OrderID from Northwind..Orders")
> Do Until rs.EOF
> s = s & rs.Fields("OrderID") & ","
> rs.MoveNext
> Loop
> .Close
> End With
> Set rs = Nothing
> Set cntsql = Nothing
> s = s & "End"
> Call WriteToFile(s)
> End Sub
> Function WriteToFile(s)
> Dim fso, tf
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set tf = fso.CreateTextFile("c:\test.csv", True)
> tf.Write(s)
> tf.Close()
> Set fso= Nothing
> Set tf= Nothing
> End Function
>
> --
> -oj
>
> "HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
> news:ZLydnddhH-JSVWTcRVn-tw@.kconline.com...
>|||oj The script worked great but...
The problem I'm having it puts an extra %Recipients/cn= at the end of the
file. The Import process that is using this output fails on this bogus
record since it doesn't have an ID attached. How can I remove this last
record from the file if it doesn't have a valid record.
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:msKdnbiYuoaxv2fcRVn-vw@.kconline.com...
> Thanks again.
>
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:uyuNXkQBFHA.3368@.TK2MSFTNGP10.phx.gbl...
>|||You would need to check the returned value before concatenating it in your
vbscript.
e.g.
if rs("your_keycol")="abc" then
'it is good and concatenate
else
'it is bad and ignore
endif
Take a look at this site for help on vbscripting
http://msdn.microsoft.com/library/e...me=true
-oj
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:g5WdnagRsbodGpzfRVn-2A@.kconline.com...
> oj The script worked great but...
> The problem I'm having it puts an extra %Recipients/cn= at the end of the
> file. The Import process that is using this output fails on this bogus
> record since it doesn't have an ID attached. How can I remove this last
> record from the file if it doesn't have a valid record.
>
>
> "HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
> news:msKdnbiYuoaxv2fcRVn-vw@.kconline.com...
>
There is only one field I pull from a table called USerID(Char8). Then for
every record in the table create a file like this.
Receipents-c/n=XXXXX%Receipents-c/n=XXXXXReceipents-c/n=XXXXXReceipents-c/n=
XXXXX This will then Import into Exchange for a Distrubution List. Any IdeasSee if this link gives you some ideas
http://www.rac4sql.net/xp_execresultset.asp
Anith|||Would that not put each order on a separate line.
I need it all strung together ..as one big file...
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23QJSioLBFHA.936@.TK2MSFTNGP12.phx.gbl...
> See if this link gives you some ideas
> http://www.rac4sql.net/xp_execresultset.asp
> --
> Anith
>|||Anith has pointed you to a trick to create a file for each record/row for
your table.
Look like you want to concatenate the rows into a single string? If so, it's
probably best to do it from the client side (i.e. vb/script/etc).
Though, I am bit
about your comment regarding CSV in your firstpost. Perhaps, you want to clarify so we can help.
-oj
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:L92dnbdsqJ2N6WTcRVn-gg@.kconline.com...
> Would that not put each order on a separate line.
> I need it all strung together ..as one big file...
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:%23QJSioLBFHA.936@.TK2MSFTNGP12.phx.gbl...
>|||sorry .. I just meant to have a csv extension to the filename.
do you have an example vbscript to concat these records from the sql table
?
thanks again.
"oj" <nospam_ojngo@.home.com> wrote in message
news:%23C0Ko0MBFHA.3492@.TK2MSFTNGP12.phx.gbl...
> Anith has pointed you to a trick to create a file for each record/row for
> your table.
> Look like you want to concatenate the rows into a single string? If so,
> it's probably best to do it from the client side (i.e. vb/script/etc).
> Though, I am bit
about your comment regarding CSV in your first> post. Perhaps, you want to clarify so we can help.
> --
> -oj
>
> "HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
> news:L92dnbdsqJ2N6WTcRVn-gg@.kconline.com...
>|||Here is a vbscript.
Main()
Sub Main()
Dim sqlcnt,rs,s
s="Begin"
Set cntsql = CreateObject("ADODB.Connection")
With cntsql
.provider = "SQLOLEDB"
.connectionstring = "Data Source=.\dev;integrated security=SSPI"
.Open
Set rs = .Execute("select OrderID from Northwind..Orders")
Do Until rs.EOF
s = s & rs.Fields("OrderID") & ","
rs.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set cntsql = Nothing
s = s & "End"
Call WriteToFile(s)
End Sub
Function WriteToFile(s)
Dim fso, tf
Set fso = CreateObject("Scripting.FileSystemObject")
Set tf = fso.CreateTextFile("c:\test.csv", True)
tf.Write(s)
tf.Close()
Set fso= Nothing
Set tf= Nothing
End Function
-oj
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:ZLydnddhH-JSVWTcRVn-tw@.kconline.com...
> sorry .. I just meant to have a csv extension to the filename.
> do you have an example vbscript to concat these records from the sql table
> ?
> thanks again.
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23C0Ko0MBFHA.3492@.TK2MSFTNGP12.phx.gbl...
>|||Thanks again.
"oj" <nospam_ojngo@.home.com> wrote in message
news:uyuNXkQBFHA.3368@.TK2MSFTNGP10.phx.gbl...
> Here is a vbscript.
> Main()
> Sub Main()
> Dim sqlcnt,rs,s
> s="Begin"
> Set cntsql = CreateObject("ADODB.Connection")
> With cntsql
> .provider = "SQLOLEDB"
> .connectionstring = "Data Source=.\dev;integrated security=SSPI"
> .Open
> Set rs = .Execute("select OrderID from Northwind..Orders")
> Do Until rs.EOF
> s = s & rs.Fields("OrderID") & ","
> rs.MoveNext
> Loop
> .Close
> End With
> Set rs = Nothing
> Set cntsql = Nothing
> s = s & "End"
> Call WriteToFile(s)
> End Sub
> Function WriteToFile(s)
> Dim fso, tf
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set tf = fso.CreateTextFile("c:\test.csv", True)
> tf.Write(s)
> tf.Close()
> Set fso= Nothing
> Set tf= Nothing
> End Function
>
> --
> -oj
>
> "HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
> news:ZLydnddhH-JSVWTcRVn-tw@.kconline.com...
>|||oj The script worked great but...
The problem I'm having it puts an extra %Recipients/cn= at the end of the
file. The Import process that is using this output fails on this bogus
record since it doesn't have an ID attached. How can I remove this last
record from the file if it doesn't have a valid record.
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:msKdnbiYuoaxv2fcRVn-vw@.kconline.com...
> Thanks again.
>
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:uyuNXkQBFHA.3368@.TK2MSFTNGP10.phx.gbl...
>|||You would need to check the returned value before concatenating it in your
vbscript.
e.g.
if rs("your_keycol")="abc" then
'it is good and concatenate
else
'it is bad and ignore
endif
Take a look at this site for help on vbscripting
http://msdn.microsoft.com/library/e...me=true
-oj
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:g5WdnagRsbodGpzfRVn-2A@.kconline.com...
> oj The script worked great but...
> The problem I'm having it puts an extra %Recipients/cn= at the end of the
> file. The Import process that is using this output fails on this bogus
> record since it doesn't have an ID attached. How can I remove this last
> record from the file if it doesn't have a valid record.
>
>
> "HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
> news:msKdnbiYuoaxv2fcRVn-vw@.kconline.com...
>
Tuesday, March 20, 2012
Compressing SQL Server Database
Newbie - SQL 2000. I have a database file 150 MB and my log file is 400 MB. Is there anyway I can compress the size of these files.
Thanks,If you don't need to take the backup of the Transaction Log:::
Take the full database backup(Recommended not Reqd)
go to Query Analyzer and
use [your database]
backup TRAN [your dbname] with no_log
once it's over then you can run:
dbcc SHRINKFILE([YOUR DBNAME_LOGFILENAME,TRUNCATEONLY)
and it will reduce the size of the Transaction Log
Thanks,If you don't need to take the backup of the Transaction Log:::
Take the full database backup(Recommended not Reqd)
go to Query Analyzer and
use [your database]
backup TRAN [your dbname] with no_log
once it's over then you can run:
dbcc SHRINKFILE([YOUR DBNAME_LOGFILENAME,TRUNCATEONLY)
and it will reduce the size of the Transaction Log
Compressed Snapshot Files!
Hello All,
I'm using Transactional Replication. I have compress snapshot file now I
want to configure it on Subscriber.
I saw BOL and it says,
1. In Microsoft SQL Server Enterprise Manager, expand the subscription
database and the Subscriptions directory, right- click a subscription,
and then click Properties.
but didnt find the Subscription directory under subscription database.
How can I configure?
Thanks in advance.
Naveed.
the snapshot files will be compressed in the cab format in your alternate
snapshot folder. If you are doing a pull subscription you have the option to
applying the snapshot (compressed or uncompressed) from an alternate
location by using the "Use snapshot files from the following folder option."
You will have to prove a path to the root of the snapshot location, ie
\\PublisherServerName\Share\ where this will map to c:\temp or wherever you
place your files in. If you copy these files locally you have to copy the
directory structure from unc on, ie if you place the files in c:\temp you
would copy the contents of unc to c:\temp.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Naveed" <nrehman@.marsonssoft.com> wrote in message
news:OoosGusqEHA.2732@.TK2MSFTNGP09.phx.gbl...
> Hello All,
> I'm using Transactional Replication. I have compress snapshot file now I
> want to configure it on Subscriber.
> I saw BOL and it says,
> 1. In Microsoft SQL Server Enterprise Manager, expand the subscription
> database and the Subscriptions directory, right- click a
subscription,
> and then click Properties.
> but didnt find the Subscription directory under subscription database.
> How can I configure?
> Thanks in advance.
> Naveed.
>
|||Hello Hilary!!!
Thanks for your reply, You gave solution for Pull Subscription But We are
using Push Subscription and we have very slow connection for publishing data
on subscriber, i.e why we compress snapshot file in alternate location and
now we want to configure it on subscriber.
How to do that?
Thanks,
Naveed.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eQGmKXtqEHA.3416@.TK2MSFTNGP15.phx.gbl...
> the snapshot files will be compressed in the cab format in your alternate
> snapshot folder. If you are doing a pull subscription you have the option
to
> applying the snapshot (compressed or uncompressed) from an alternate
> location by using the "Use snapshot files from the following folder
option."
> You will have to prove a path to the root of the snapshot location, ie
> \\PublisherServerName\Share\ where this will map to c:\temp or wherever
you
> place your files in. If you copy these files locally you have to copy the
> directory structure from unc on, ie if you place the files in c:\temp you
> would copy the contents of unc to c:\temp.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Naveed" <nrehman@.marsonssoft.com> wrote in message
> news:OoosGusqEHA.2732@.TK2MSFTNGP09.phx.gbl...
> subscription,
>
|||use the -AltSnapshotFolder switch on your distribution agent. make sure you
uncheck the generate snapshot in the default folder location for your
snapshot properties.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Naveed" <nrehman@.marsonssoft.com> wrote in message
news:eUFealtqEHA.376@.TK2MSFTNGP14.phx.gbl...
> Hello Hilary!!!
> Thanks for your reply, You gave solution for Pull Subscription But We are
> using Push Subscription and we have very slow connection for publishing
data[vbcol=seagreen]
> on subscriber, i.e why we compress snapshot file in alternate location and
> now we want to configure it on subscriber.
> How to do that?
> Thanks,
> Naveed.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eQGmKXtqEHA.3416@.TK2MSFTNGP15.phx.gbl...
alternate[vbcol=seagreen]
option[vbcol=seagreen]
> to
> option."
> you
the[vbcol=seagreen]
you[vbcol=seagreen]
I[vbcol=seagreen]
subscription
>
sqlsql
I'm using Transactional Replication. I have compress snapshot file now I
want to configure it on Subscriber.
I saw BOL and it says,
1. In Microsoft SQL Server Enterprise Manager, expand the subscription
database and the Subscriptions directory, right- click a subscription,
and then click Properties.
but didnt find the Subscription directory under subscription database.
How can I configure?
Thanks in advance.
Naveed.
the snapshot files will be compressed in the cab format in your alternate
snapshot folder. If you are doing a pull subscription you have the option to
applying the snapshot (compressed or uncompressed) from an alternate
location by using the "Use snapshot files from the following folder option."
You will have to prove a path to the root of the snapshot location, ie
\\PublisherServerName\Share\ where this will map to c:\temp or wherever you
place your files in. If you copy these files locally you have to copy the
directory structure from unc on, ie if you place the files in c:\temp you
would copy the contents of unc to c:\temp.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Naveed" <nrehman@.marsonssoft.com> wrote in message
news:OoosGusqEHA.2732@.TK2MSFTNGP09.phx.gbl...
> Hello All,
> I'm using Transactional Replication. I have compress snapshot file now I
> want to configure it on Subscriber.
> I saw BOL and it says,
> 1. In Microsoft SQL Server Enterprise Manager, expand the subscription
> database and the Subscriptions directory, right- click a
subscription,
> and then click Properties.
> but didnt find the Subscription directory under subscription database.
> How can I configure?
> Thanks in advance.
> Naveed.
>
|||Hello Hilary!!!
Thanks for your reply, You gave solution for Pull Subscription But We are
using Push Subscription and we have very slow connection for publishing data
on subscriber, i.e why we compress snapshot file in alternate location and
now we want to configure it on subscriber.
How to do that?
Thanks,
Naveed.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eQGmKXtqEHA.3416@.TK2MSFTNGP15.phx.gbl...
> the snapshot files will be compressed in the cab format in your alternate
> snapshot folder. If you are doing a pull subscription you have the option
to
> applying the snapshot (compressed or uncompressed) from an alternate
> location by using the "Use snapshot files from the following folder
option."
> You will have to prove a path to the root of the snapshot location, ie
> \\PublisherServerName\Share\ where this will map to c:\temp or wherever
you
> place your files in. If you copy these files locally you have to copy the
> directory structure from unc on, ie if you place the files in c:\temp you
> would copy the contents of unc to c:\temp.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Naveed" <nrehman@.marsonssoft.com> wrote in message
> news:OoosGusqEHA.2732@.TK2MSFTNGP09.phx.gbl...
> subscription,
>
|||use the -AltSnapshotFolder switch on your distribution agent. make sure you
uncheck the generate snapshot in the default folder location for your
snapshot properties.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Naveed" <nrehman@.marsonssoft.com> wrote in message
news:eUFealtqEHA.376@.TK2MSFTNGP14.phx.gbl...
> Hello Hilary!!!
> Thanks for your reply, You gave solution for Pull Subscription But We are
> using Push Subscription and we have very slow connection for publishing
data[vbcol=seagreen]
> on subscriber, i.e why we compress snapshot file in alternate location and
> now we want to configure it on subscriber.
> How to do that?
> Thanks,
> Naveed.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eQGmKXtqEHA.3416@.TK2MSFTNGP15.phx.gbl...
alternate[vbcol=seagreen]
option[vbcol=seagreen]
> to
> option."
> you
the[vbcol=seagreen]
you[vbcol=seagreen]
I[vbcol=seagreen]
subscription
>
sqlsql
Compress SQL Server Backup File.
I have a Windows 2003 Server, what my plan is to compress sql server backup
file every night and ship in to different SQL Server Server, the question
is, is there any builtin zip/compress facility in windows which I can use it
for this purpose.
Thanks
If you are using the NTFS file system, you can just configure a directory to
be compressed in the advanced properties but when you read the file to send
it to the backup server, Windows will uncompress it so if you want the data
to be compressed on the wire, you will probably need to use some kind of zip
utility. Depending on how big the files are, compressing on the wire might
not make that big a difference so it might be enough to make the backup
directories compressed. Be sure you don't accidentally compress the
database files - this will break SQL Server.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Rogers" <naissani@.hotmail.com> wrote in message
news:eZves4UPGHA.1312@.TK2MSFTNGP09.phx.gbl...
>I have a Windows 2003 Server, what my plan is to compress sql server backup
>file every night and ship in to different SQL Server Server, the question
>is, is there any builtin zip/compress facility in windows which I can use
>it for this purpose.
> Thanks
>
|||Rogers wrote:
> I have a Windows 2003 Server, what my plan is to compress sql server
> backup file every night and ship in to different SQL Server Server,
> the question is, is there any builtin zip/compress facility in
> windows which I can use it for this purpose.
> Thanks
I would recommend you consider using a SQL Server backup and recovery
program that compresses and optionally encrypts backups in memory.
You'll save yourself a lot of backup (and recovery) time and won't have
to worry about post-backup compression. There are a number of companies
who have this type of software.
David Gugick - SQL Server MVP
Quest Software
|||Hi,
of course there are several tools in the market making a good job, but
sometimes you are really interested in a subset of their functionality
or your budget is really reduced. Four these cases, I would suggest
take a look here
http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11_blogpart_blogpart =blogview&_c=blogpart#permalink
Regards
file every night and ship in to different SQL Server Server, the question
is, is there any builtin zip/compress facility in windows which I can use it
for this purpose.
Thanks
If you are using the NTFS file system, you can just configure a directory to
be compressed in the advanced properties but when you read the file to send
it to the backup server, Windows will uncompress it so if you want the data
to be compressed on the wire, you will probably need to use some kind of zip
utility. Depending on how big the files are, compressing on the wire might
not make that big a difference so it might be enough to make the backup
directories compressed. Be sure you don't accidentally compress the
database files - this will break SQL Server.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Rogers" <naissani@.hotmail.com> wrote in message
news:eZves4UPGHA.1312@.TK2MSFTNGP09.phx.gbl...
>I have a Windows 2003 Server, what my plan is to compress sql server backup
>file every night and ship in to different SQL Server Server, the question
>is, is there any builtin zip/compress facility in windows which I can use
>it for this purpose.
> Thanks
>
|||Rogers wrote:
> I have a Windows 2003 Server, what my plan is to compress sql server
> backup file every night and ship in to different SQL Server Server,
> the question is, is there any builtin zip/compress facility in
> windows which I can use it for this purpose.
> Thanks
I would recommend you consider using a SQL Server backup and recovery
program that compresses and optionally encrypts backups in memory.
You'll save yourself a lot of backup (and recovery) time and won't have
to worry about post-backup compression. There are a number of companies
who have this type of software.
David Gugick - SQL Server MVP
Quest Software
|||Hi,
of course there are several tools in the market making a good job, but
sometimes you are really interested in a subset of their functionality
or your budget is really reduced. Four these cases, I would suggest
take a look here
http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11_blogpart_blogpart =blogview&_c=blogpart#permalink
Regards
Compress full backup file
Hello,
Is it possible to compress a full backup file? I've noticed that the backup file size is usually the same size of the working database.
My goal is to compress and break in small parts the backup file to be send to another location.
Does anyone knows a program to do that?
Thanks for the help!
Diogo SantosRefer to http://www.sqllitespeed.com/ (SQL Litespeed) for more information which is lot quicker and reliable.
Is it possible to compress a full backup file? I've noticed that the backup file size is usually the same size of the working database.
My goal is to compress and break in small parts the backup file to be send to another location.
Does anyone knows a program to do that?
Thanks for the help!
Diogo SantosRefer to http://www.sqllitespeed.com/ (SQL Litespeed) for more information which is lot quicker and reliable.
compress backup file
One KB article (http://support.microsoft.com/kb/231347/en-us) says that
backing up databases onto compressed volumes is not recommended and not
supported. My question is: Is it ok/supported if we backup the databases
regularly and then zip the backup files?
Thanks in advance.
Claudia
Hello,
You can do that. As a precausion once in while just unzip the file and
restore the database in your testing environment and make sure
that UNZIP process and RESTORE work fine.
Thanks
Hari
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
|||Claudia,
Or move the files to a compressed volume after the backup is complete. Move
them back to an uncompressed volume if you have to restore.
-- Bill
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
|||Claudia wrote:
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
I've backed up to compressed volumes for years, never had a problem.
Definately don't put the live data files on a compressed volume, but the
backups seem to work without a hitch.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E4D86.6000705@.realsqlguy.com...
> Claudia wrote:
> I've backed up to compressed volumes for years, never had a problem.
> Definately don't put the live data files on a compressed volume, but the
> backups seem to work without a hitch.
Sure, but have you restored from the compressed volume? ;-)
Seriously, I think there's a few reasons MS recommends against this, but I
haven't seen problems either.
Also, another option is to look at some of the 3rd party backup tools out
there that will compress on the fly as they backup.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Greg D. Moore (Strider) wrote:
> Sure, but have you restored from the compressed volume? ;-)
Yep - each backup gets restored to three different servers - DR,
Standby, and Reporting. Full backups every night, logs every 5 minutes,
haven't had one fail yet.
> Seriously, I think there's a few reasons MS recommends against this, but I
> haven't seen problems either.
To be honest, I always viewed it as a "CYA" statement.
> Also, another option is to look at some of the 3rd party backup tools out
> there that will compress on the fly as they backup.
Definately an option. As much as we exercise our backups, I'm pretty
confident that the compressed volumes are safe though.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E5E0C.6040402@.realsqlguy.com...
> Greg D. Moore (Strider) wrote:
> Yep - each backup gets restored to three different servers - DR, Standby,
> and Reporting. Full backups every night, logs every 5 minutes, haven't
> had one fail yet.
Actually to be honest, knowing you, I sort of assumed you did. I've just
seen too many people who say, "Oh the backups work fine..." only to find
out the restores don't. ;-)
>
> To be honest, I always viewed it as a "CYA" statement.
I recall it having to do something with aligning writes on sector boundaries
and the like for speed, but that was about it.
>
> Definately an option. As much as we exercise our backups, I'm pretty
> confident that the compressed volumes are safe though.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
backing up databases onto compressed volumes is not recommended and not
supported. My question is: Is it ok/supported if we backup the databases
regularly and then zip the backup files?
Thanks in advance.
Claudia
Hello,
You can do that. As a precausion once in while just unzip the file and
restore the database in your testing environment and make sure
that UNZIP process and RESTORE work fine.
Thanks
Hari
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
|||Claudia,
Or move the files to a compressed volume after the backup is complete. Move
them back to an uncompressed volume if you have to restore.
-- Bill
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
|||Claudia wrote:
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
I've backed up to compressed volumes for years, never had a problem.
Definately don't put the live data files on a compressed volume, but the
backups seem to work without a hitch.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E4D86.6000705@.realsqlguy.com...
> Claudia wrote:
> I've backed up to compressed volumes for years, never had a problem.
> Definately don't put the live data files on a compressed volume, but the
> backups seem to work without a hitch.
Sure, but have you restored from the compressed volume? ;-)
Seriously, I think there's a few reasons MS recommends against this, but I
haven't seen problems either.
Also, another option is to look at some of the 3rd party backup tools out
there that will compress on the fly as they backup.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Greg D. Moore (Strider) wrote:
> Sure, but have you restored from the compressed volume? ;-)
Yep - each backup gets restored to three different servers - DR,
Standby, and Reporting. Full backups every night, logs every 5 minutes,
haven't had one fail yet.
> Seriously, I think there's a few reasons MS recommends against this, but I
> haven't seen problems either.
To be honest, I always viewed it as a "CYA" statement.
> Also, another option is to look at some of the 3rd party backup tools out
> there that will compress on the fly as they backup.
Definately an option. As much as we exercise our backups, I'm pretty
confident that the compressed volumes are safe though.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E5E0C.6040402@.realsqlguy.com...
> Greg D. Moore (Strider) wrote:
> Yep - each backup gets restored to three different servers - DR, Standby,
> and Reporting. Full backups every night, logs every 5 minutes, haven't
> had one fail yet.
Actually to be honest, knowing you, I sort of assumed you did. I've just
seen too many people who say, "Oh the backups work fine..." only to find
out the restores don't. ;-)
>
> To be honest, I always viewed it as a "CYA" statement.
I recall it having to do something with aligning writes on sector boundaries
and the like for speed, but that was about it.
>
> Definately an option. As much as we exercise our backups, I'm pretty
> confident that the compressed volumes are safe though.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
compress backup file
One KB article (http://support.microsoft.com/kb/231347/en-us) says that
backing up databases onto compressed volumes is not recommended and not
supported. My question is: Is it ok/supported if we backup the databases
regularly and then zip the backup files?
Thanks in advance.
ClaudiaHello,
You can do that. As a precausion once in while just unzip the file and
restore the database in your testing environment and make sure
that UNZIP process and RESTORE work fine.
Thanks
Hari
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia|||Claudia,
Or move the files to a compressed volume after the backup is complete. Move
them back to an uncompressed volume if you have to restore.
-- Bill
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia|||Claudia wrote:
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
I've backed up to compressed volumes for years, never had a problem.
Definately don't put the live data files on a compressed volume, but the
backups seem to work without a hitch.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E4D86.6000705@.realsqlguy.com...
> Claudia wrote:
> I've backed up to compressed volumes for years, never had a problem.
> Definately don't put the live data files on a compressed volume, but the
> backups seem to work without a hitch.
Sure, but have you restored from the compressed volume? ;-)
Seriously, I think there's a few reasons MS recommends against this, but I
haven't seen problems either.
Also, another option is to look at some of the 3rd party backup tools out
there that will compress on the fly as they backup.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Greg D. Moore (Strider) wrote:
> Sure, but have you restored from the compressed volume? ;-)
Yep - each backup gets restored to three different servers - DR,
Standby, and Reporting. Full backups every night, logs every 5 minutes,
haven't had one fail yet.
> Seriously, I think there's a few reasons MS recommends against this, but I
> haven't seen problems either.
To be honest, I always viewed it as a "CYA" statement.
> Also, another option is to look at some of the 3rd party backup tools out
> there that will compress on the fly as they backup.
Definately an option. As much as we exercise our backups, I'm pretty
confident that the compressed volumes are safe though.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E5E0C.6040402@.realsqlguy.com...
> Greg D. Moore (Strider) wrote:
> Yep - each backup gets restored to three different servers - DR, Standby,
> and Reporting. Full backups every night, logs every 5 minutes, haven't
> had one fail yet.
Actually to be honest, knowing you, I sort of assumed you did. I've just
seen too many people who say, "Oh the backups work fine..." only to find
out the restores don't. ;-)
>
> To be honest, I always viewed it as a "CYA" statement.
I recall it having to do something with aligning writes on sector boundaries
and the like for speed, but that was about it.
>
> Definately an option. As much as we exercise our backups, I'm pretty
> confident that the compressed volumes are safe though.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
backing up databases onto compressed volumes is not recommended and not
supported. My question is: Is it ok/supported if we backup the databases
regularly and then zip the backup files?
Thanks in advance.
ClaudiaHello,
You can do that. As a precausion once in while just unzip the file and
restore the database in your testing environment and make sure
that UNZIP process and RESTORE work fine.
Thanks
Hari
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia|||Claudia,
Or move the files to a compressed volume after the backup is complete. Move
them back to an uncompressed volume if you have to restore.
-- Bill
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia|||Claudia wrote:
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
I've backed up to compressed volumes for years, never had a problem.
Definately don't put the live data files on a compressed volume, but the
backups seem to work without a hitch.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E4D86.6000705@.realsqlguy.com...
> Claudia wrote:
> I've backed up to compressed volumes for years, never had a problem.
> Definately don't put the live data files on a compressed volume, but the
> backups seem to work without a hitch.
Sure, but have you restored from the compressed volume? ;-)
Seriously, I think there's a few reasons MS recommends against this, but I
haven't seen problems either.
Also, another option is to look at some of the 3rd party backup tools out
there that will compress on the fly as they backup.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Greg D. Moore (Strider) wrote:
> Sure, but have you restored from the compressed volume? ;-)
Yep - each backup gets restored to three different servers - DR,
Standby, and Reporting. Full backups every night, logs every 5 minutes,
haven't had one fail yet.
> Seriously, I think there's a few reasons MS recommends against this, but I
> haven't seen problems either.
To be honest, I always viewed it as a "CYA" statement.
> Also, another option is to look at some of the 3rd party backup tools out
> there that will compress on the fly as they backup.
Definately an option. As much as we exercise our backups, I'm pretty
confident that the compressed volumes are safe though.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E5E0C.6040402@.realsqlguy.com...
> Greg D. Moore (Strider) wrote:
> Yep - each backup gets restored to three different servers - DR, Standby,
> and Reporting. Full backups every night, logs every 5 minutes, haven't
> had one fail yet.
Actually to be honest, knowing you, I sort of assumed you did. I've just
seen too many people who say, "Oh the backups work fine..." only to find
out the restores don't. ;-)
>
> To be honest, I always viewed it as a "CYA" statement.
I recall it having to do something with aligning writes on sector boundaries
and the like for speed, but that was about it.
>
> Definately an option. As much as we exercise our backups, I'm pretty
> confident that the compressed volumes are safe though.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
compress backup file
One KB article (http://support.microsoft.com/kb/231347/en-us) says that
backing up databases onto compressed volumes is not recommended and not
supported. My question is: Is it ok/supported if we backup the databases
regularly and then zip the backup files?
Thanks in advance.
ClaudiaHello,
You can do that. As a precausion once in while just unzip the file and
restore the database in your testing environment and make sure
that UNZIP process and RESTORE work fine.
Thanks
Hari
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia|||Claudia,
Or move the files to a compressed volume after the backup is complete. Move
them back to an uncompressed volume if you have to restore.
-- Bill
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia|||Claudia wrote:
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
I've backed up to compressed volumes for years, never had a problem.
Definately don't put the live data files on a compressed volume, but the
backups seem to work without a hitch.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E4D86.6000705@.realsqlguy.com...
> Claudia wrote:
>> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
>> backing up databases onto compressed volumes is not recommended and not
>> supported. My question is: Is it ok/supported if we backup the databases
>> regularly and then zip the backup files?
>> Thanks in advance.
>> Claudia
> I've backed up to compressed volumes for years, never had a problem.
> Definately don't put the live data files on a compressed volume, but the
> backups seem to work without a hitch.
Sure, but have you restored from the compressed volume? ;-)
Seriously, I think there's a few reasons MS recommends against this, but I
haven't seen problems either.
Also, another option is to look at some of the 3rd party backup tools out
there that will compress on the fly as they backup.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Greg D. Moore (Strider) wrote:
> Sure, but have you restored from the compressed volume? ;-)
Yep - each backup gets restored to three different servers - DR,
Standby, and Reporting. Full backups every night, logs every 5 minutes,
haven't had one fail yet.
> Seriously, I think there's a few reasons MS recommends against this, but I
> haven't seen problems either.
To be honest, I always viewed it as a "CYA" statement.
> Also, another option is to look at some of the 3rd party backup tools out
> there that will compress on the fly as they backup.
Definately an option. As much as we exercise our backups, I'm pretty
confident that the compressed volumes are safe though.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E5E0C.6040402@.realsqlguy.com...
> Greg D. Moore (Strider) wrote:
>> Sure, but have you restored from the compressed volume? ;-)
> Yep - each backup gets restored to three different servers - DR, Standby,
> and Reporting. Full backups every night, logs every 5 minutes, haven't
> had one fail yet.
Actually to be honest, knowing you, I sort of assumed you did. I've just
seen too many people who say, "Oh the backups work fine..." only to find
out the restores don't. ;-)
>> Seriously, I think there's a few reasons MS recommends against this, but
>> I haven't seen problems either.
> To be honest, I always viewed it as a "CYA" statement.
I recall it having to do something with aligning writes on sector boundaries
and the like for speed, but that was about it.
>> Also, another option is to look at some of the 3rd party backup tools out
>> there that will compress on the fly as they backup.
> Definately an option. As much as we exercise our backups, I'm pretty
> confident that the compressed volumes are safe though.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.comsqlsql
backing up databases onto compressed volumes is not recommended and not
supported. My question is: Is it ok/supported if we backup the databases
regularly and then zip the backup files?
Thanks in advance.
ClaudiaHello,
You can do that. As a precausion once in while just unzip the file and
restore the database in your testing environment and make sure
that UNZIP process and RESTORE work fine.
Thanks
Hari
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia|||Claudia,
Or move the files to a compressed volume after the backup is complete. Move
them back to an uncompressed volume if you have to restore.
-- Bill
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia|||Claudia wrote:
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
I've backed up to compressed volumes for years, never had a problem.
Definately don't put the live data files on a compressed volume, but the
backups seem to work without a hitch.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E4D86.6000705@.realsqlguy.com...
> Claudia wrote:
>> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
>> backing up databases onto compressed volumes is not recommended and not
>> supported. My question is: Is it ok/supported if we backup the databases
>> regularly and then zip the backup files?
>> Thanks in advance.
>> Claudia
> I've backed up to compressed volumes for years, never had a problem.
> Definately don't put the live data files on a compressed volume, but the
> backups seem to work without a hitch.
Sure, but have you restored from the compressed volume? ;-)
Seriously, I think there's a few reasons MS recommends against this, but I
haven't seen problems either.
Also, another option is to look at some of the 3rd party backup tools out
there that will compress on the fly as they backup.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Greg D. Moore (Strider) wrote:
> Sure, but have you restored from the compressed volume? ;-)
Yep - each backup gets restored to three different servers - DR,
Standby, and Reporting. Full backups every night, logs every 5 minutes,
haven't had one fail yet.
> Seriously, I think there's a few reasons MS recommends against this, but I
> haven't seen problems either.
To be honest, I always viewed it as a "CYA" statement.
> Also, another option is to look at some of the 3rd party backup tools out
> there that will compress on the fly as they backup.
Definately an option. As much as we exercise our backups, I'm pretty
confident that the compressed volumes are safe though.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E5E0C.6040402@.realsqlguy.com...
> Greg D. Moore (Strider) wrote:
>> Sure, but have you restored from the compressed volume? ;-)
> Yep - each backup gets restored to three different servers - DR, Standby,
> and Reporting. Full backups every night, logs every 5 minutes, haven't
> had one fail yet.
Actually to be honest, knowing you, I sort of assumed you did. I've just
seen too many people who say, "Oh the backups work fine..." only to find
out the restores don't. ;-)
>> Seriously, I think there's a few reasons MS recommends against this, but
>> I haven't seen problems either.
> To be honest, I always viewed it as a "CYA" statement.
I recall it having to do something with aligning writes on sector boundaries
and the like for speed, but that was about it.
>> Also, another option is to look at some of the 3rd party backup tools out
>> there that will compress on the fly as they backup.
> Definately an option. As much as we exercise our backups, I'm pretty
> confident that the compressed volumes are safe though.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.comsqlsql
Sunday, March 11, 2012
Complicated Query Question
How can the data in a relational table be parsed into a flat file table?
For instance; table1 has an id for each entry and an sid and name, table2 has an
sid and name1 name2 name3...456.
I have both tables in the same database and want to import the data from table1
into table2. The records in table1 with identical sid's should create only one
record in table2. Here is a sample of the tables, and what I have started
with>>
CREATE DATABASE SAMPLE002
GO
USE SAMPLE002
CREATE TABLE table1
(
sID int NOT NULL,
Name1 varchar(50) NULL,
Name2 varchar(50) NULL,
Name3 varchar(50) NULL,
Name4 varchar(50) NULL,
Name5 varchar(50) NULL,
Name6 varchar(50) NULL
)
GO
INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (1,
'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (2,
'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
GO
CREATE TABLE table2
(
pID int IDENTITY (1, 1) NOT NULL,
sID int NOT NULL,
Name varchar(50) NOT NULL
)
GO
INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
--View the tables as they are
select * from table1
select * from table2
--I tried this first but of course it duplicates the sid in table 2 and that
can't happen. I expect there needs to be a looping process on each sid but I am
not sure how to write that
insert into table1 (sid,Name1)
select sid,Name from table2
--Use this to clean out the mess made by the previous insert
delete from table1
where sid >2
THANK YOU !!!
=================================
Try this:
select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
from table2 as a
join table2 as b on b.sID = a.sID and b.Name < a.Name
join table2 as c on c.sID = a.sID and c.Name < b.Name
join table2 as d on d.sID = a.sID and d.Name < c.Name
join table2 as e on e.sID = a.sID and e.Name < d.Name
join table2 as f on f.sID = a.sID and f.Name < e.Name
This will give one row with the names in alphabetical order.
You need as many tables as the number of entries per sID in table2. If table2 does not always have six entries for each
sID, use OUTER JOINs
WANNABE wrote:
> How can the data in a relational table be parsed into a flat file table?
> For instance; table1 has an id for each entry and an sid and name, table2 has an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from table1
> into table2. The records in table1 with identical sid's should create only one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and that
> can't happen. I expect there needs to be a looping process on each sid but I am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================
>
|||After I posted, I realized that outer joins will not give you what you want. They will just give you all permutations
of the names.
I will have to give this some more thought. I have never run into this situation before, but someone else probably has.
Maybe they have a neat solution.
WANNABE wrote:
> There's much that I do not understand;
> Can I use the select statement on it's own to view what will be inserted into
> the table when I run the full query?
> Does this join table2 to itself 6 times?
> Table2 does not always have 6 entries per sid, and neither does table1, but
> should I use FULL OUTER?
> HOW is this suppose to work?
> ======================================
> "Ed Enstrom" <nospam@.invalid.net> wrote in message
> news:Ey0di.75$LW6.21@.newsfe12.lga...
> Try this:
> select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
> from table2 as a
> join table2 as b on b.sID = a.sID and b.Name < a.Name
> join table2 as c on c.sID = a.sID and c.Name < b.Name
> join table2 as d on d.sID = a.sID and d.Name < c.Name
> join table2 as e on e.sID = a.sID and e.Name < d.Name
> join table2 as f on f.sID = a.sID and f.Name < e.Name
> This will give one row with the names in alphabetical order.
> You need as many tables as the number of entries per sID in table2. If table2
> does not always have six entries for each
> sID, use OUTER JOINs
> WANNABE wrote:
>
|||I think you got your tables mixed up -
CREATE TABLE Wtable1
(
pID int primary key,
sID int NOT NULL,
Name varchar(8) NOT NULL
)
GO
INSERT INTO Wtable1 (pID,sID,Name) VALUES (1,3, 'Robert')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (2,3, 'Roger')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (3,3, 'Edgar')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (4,3, 'Mitch')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (5,4, 'Julie')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (6,4, 'Wendy')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (7,4, 'Roberto')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (8,5, 'Jean')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (9,5, 'Jill')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (10,5, 'Alice')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (11,5, 'Claire')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (12,5, 'Steve')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (13,5, 'Hugh')
CREATE TABLE Wtable2
(
sID int primary key,
Name1 varchar(8) NULL,
Name2 varchar(8) NULL,
Name3 varchar(8) NULL,
Name4 varchar(8) NULL,
Name5 varchar(8) NULL,
Name6 varchar(8) NULL
)
GO
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(1,
'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(2,
'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
The RAC utility was designed by a brillant developer (I know him) to
solve these types of problems in the easiest and lest painful way
possible. This is really a pivoting problem and Rac does this well.
Rac is a system of stored procedures and functions that run in
sql server (2k/2005). It has many parameters that allow you to
manipulate (pivot) data and get exactly the result you want. You can
browse the site and look at the online Help to check out each
parameter.
Just insert the Rac result into Wtable2:
insert into Wtable2
Exec Rac
@.transform='Max(Name) as [Name]',
@.rows='sID',
@.pvtcol='pID',
--@.ranklimit would be the maximum NameN in Wtable2
@.from='Wtable1',@.rank='Col',@.ranklimit='6',
@.defaults1='y',@.racheck='y',@.shell='n',
@.replacepvtcols=
'{case when [value]>~~ then [value] else null end for []}',
@.select='select 1*sID as sID,_pvtcols_
from rac '
select * from Wtable2:
sID Name1 Name2 Name3 Name4 Name5 Name6
-- -- -- -- -- -- --
1 Hal Jenny Hank Mary NULL NULL
2 Leroy Anne Curtis NULL NULL NULL
3 Robert Roger Edgar Mitch NULL NULL
4 Julie Wendy Roberto NULL NULL NULL
5 Jean Jill Alice Claire Steve Hugh
Rac inserted rows 3-5.
Rac @.
www.rac4sql.net
More @.
www.beyondsql.blogspot.com
|||Warning: code not run, hence may have typos.
with T1 as (
select sId, name1 as Name from table1 where name1 is not null
union -- not union ALL, so as to eliminate duplicates
select sId, name2 from table1 where name2 is not null
union
... etc. ...
) -- you could also use the UNPIVOT operator to create T1 (briefer code)
insert into table2
select * from T1
where not exists (select * from table2 T2
where T2.sId = T1.Sid and T2.name = T1.Name)
"WANNABE" <SameAsB4> wrote in message
news:uE3dc1FsHHA.5008@.TK2MSFTNGP05.phx.gbl...
> How can the data in a relational table be parsed into a flat file table?
> For instance; table1 has an id for each entry and an sid and name, table2
> has an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from
> table1
> into table2. The records in table1 with identical sid's should create
> only one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and
> that
> can't happen. I expect there needs to be a looping process on each sid
> but I am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================
>
|||These statements should work for you. It may not be the most elegant T-SQL code, but it works.
The first statement seeds table1 with the distinct sID values from table2,
with "N/A" value for all the name columns.
The next statement updates Name1 in table1 with the lowest value in table2
for that sID.
The next statement updates Name2 in table1 with the lowest value in table2
that is not equal to the Name1 value in table1.
The next statement updates Name3 in table1 with the lowest value in table2
that is not equal to both the Name1 and Name2 values in table1.
And so on.
You need as many UPDATE statements as the maximum number of names for any sID.
For those sIDs in table2 that have fewer rows than the number of UPDATE statements, the SELECT subquery will return NULL
and the ISNULL function will substitute "N/A" for the value to be updated.
insert into table1 select distinct sID, 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A'
from table2;
update table1
set Name1 =
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID);
update table1
set Name2 =
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1);
update table1
set Name3=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2);
update table1
set Name4=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3);
update table1
set Name5=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4);
update table1
set Name6=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4
and table2.Name <> table1.Name5);
Ed Enstrom wrote:[vbcol=seagreen]
> After I posted, I realized that outer joins will not give you what you
> want. They will just give you all permutations of the names.
> I will have to give this some more thought. I have never run into this
> situation before, but someone else probably has. Maybe they have a neat
> solution.
> WANNABE wrote:
|||Thanks Mark, I was able to make this code work but it parsed the data INTO the
relational table, and I am looking to parse the data INTO the FLAT file table.
================================================== ==
"Mark Yudkin" <DoNotContactMe@.boingboing.org> wrote in message
news:%23IOEPIMsHHA.3628@.TK2MSFTNGP02.phx.gbl...
Warning: code not run, hence may have typos.
with T1 as (
select sId, name1 as Name from table1 where name1 is not null
union -- not union ALL, so as to eliminate duplicates
select sId, name2 from table1 where name2 is not null
union
... etc. ...
) -- you could also use the UNPIVOT operator to create T1 (briefer code)
insert into table2
select * from T1
where not exists (select * from table2 T2
where T2.sId = T1.Sid and T2.name = T1.Name)
"WANNABE" <SameAsB4> wrote in message
news:uE3dc1FsHHA.5008@.TK2MSFTNGP05.phx.gbl...
> How can the data in a relational table be parsed into a flat file table?
> For instance; table1 has an id for each entry and an sid and name, table2
> has an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from
> table1
> into table2. The records in table1 with identical sid's should create
> only one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and
> that
> can't happen. I expect there needs to be a looping process on each sid
> but I am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================
>
|||Thanks allot Ed, but this replaces NULL values with N/A, and replaces all the
existing Name1...6 fields with N/A
==========================================
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:lOcdi.6$Nx2.2@.newsfe12.lga...
These statements should work for you. It may not be the most elegant T-SQL
code, but it works.
The first statement seeds table1 with the distinct sID values from table2,
with "N/A" value for all the name columns.
The next statement updates Name1 in table1 with the lowest value in table2
for that sID.
The next statement updates Name2 in table1 with the lowest value in table2
that is not equal to the Name1 value in table1.
The next statement updates Name3 in table1 with the lowest value in table2
that is not equal to both the Name1 and Name2 values in table1.
And so on.
You need as many UPDATE statements as the maximum number of names for any sID.
For those sIDs in table2 that have fewer rows than the number of UPDATE
statements, the SELECT subquery will return NULL
and the ISNULL function will substitute "N/A" for the value to be updated.
insert into table1 select distinct sID, 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A'
from table2;
update table1
set Name1 =
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID);
update table1
set Name2 =
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1);
update table1
set Name3=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2);
update table1
set Name4=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3);
update table1
set Name5=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4);
update table1
set Name6=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4
and table2.Name <> table1.Name5);
Ed Enstrom wrote:[vbcol=seagreen]
> After I posted, I realized that outer joins will not give you what you
> want. They will just give you all permutations of the names.
> I will have to give this some more thought. I have never run into this
> situation before, but someone else probably has. Maybe they have a neat
> solution.
> WANNABE wrote:
|||Thanks Susan, That looks KOOL, but as a learning experiment I would like to
understand how this can be done with a simple query. Can you please explain
what you meant, "I think you got your tables mixed up" DID I DO SOMETHING
WRONG?
LOL
========================================
"Steve Dassin" <rac4sqlnospam@.net> wrote in message
news:eZRxmOLsHHA.4932@.TK2MSFTNGP02.phx.gbl...
I think you got your tables mixed up -
CREATE TABLE Wtable1
(
pID int primary key,
sID int NOT NULL,
Name varchar(8) NOT NULL
)
GO
INSERT INTO Wtable1 (pID,sID,Name) VALUES (1,3, 'Robert')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (2,3, 'Roger')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (3,3, 'Edgar')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (4,3, 'Mitch')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (5,4, 'Julie')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (6,4, 'Wendy')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (7,4, 'Roberto')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (8,5, 'Jean')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (9,5, 'Jill')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (10,5, 'Alice')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (11,5, 'Claire')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (12,5, 'Steve')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (13,5, 'Hugh')
CREATE TABLE Wtable2
(
sID int primary key,
Name1 varchar(8) NULL,
Name2 varchar(8) NULL,
Name3 varchar(8) NULL,
Name4 varchar(8) NULL,
Name5 varchar(8) NULL,
Name6 varchar(8) NULL
)
GO
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(1,
'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(2,
'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
The RAC utility was designed by a brillant developer (I know him) to
solve these types of problems in the easiest and lest painful way
possible. This is really a pivoting problem and Rac does this well.
Rac is a system of stored procedures and functions that run in
sql server (2k/2005). It has many parameters that allow you to
manipulate (pivot) data and get exactly the result you want. You can
browse the site and look at the online Help to check out each
parameter.
Just insert the Rac result into Wtable2:
insert into Wtable2
Exec Rac
@.transform='Max(Name) as [Name]',
@.rows='sID',
@.pvtcol='pID',
--@.ranklimit would be the maximum NameN in Wtable2
@.from='Wtable1',@.rank='Col',@.ranklimit='6',
@.defaults1='y',@.racheck='y',@.shell='n',
@.replacepvtcols=
'{case when [value]>~~ then [value] else null end for []}',
@.select='select 1*sID as sID,_pvtcols_
from rac '
select * from Wtable2:
sID Name1 Name2 Name3 Name4 Name5 Name6
-- -- -- -- -- -- --
1 Hal Jenny Hank Mary NULL NULL
2 Leroy Anne Curtis NULL NULL NULL
3 Robert Roger Edgar Mitch NULL NULL
4 Julie Wendy Roberto NULL NULL NULL
5 Jean Jill Alice Claire Steve Hugh
Rac inserted rows 3-5.
Rac @.
www.rac4sql.net
More @.
www.beyondsql.blogspot.com
|||"WANNABE" <SameAsB4> wrote in message
news:uhGNLeVsHHA.484@.TK2MSFTNGP06.phx.gbl...
> Thanks Susan, That looks KOOL, but as a learning experiment I would like
to
> understand how this can be done with a simple query. Can you please
explain
> what you meant, "I think you got your tables mixed up" DID I DO SOMETHING
> WRONG?
> LOL
Susan?...You got something going with my sister? -
>For instance; table1 has an id for each entry and an sid and name, table2
has an
>sid and name1 name2 name3...456.
That's backwards -
For instance; table1 has an id for each entry and an sid and name, table2 has an
sid and name1 name2 name3...456.
I have both tables in the same database and want to import the data from table1
into table2. The records in table1 with identical sid's should create only one
record in table2. Here is a sample of the tables, and what I have started
with>>
CREATE DATABASE SAMPLE002
GO
USE SAMPLE002
CREATE TABLE table1
(
sID int NOT NULL,
Name1 varchar(50) NULL,
Name2 varchar(50) NULL,
Name3 varchar(50) NULL,
Name4 varchar(50) NULL,
Name5 varchar(50) NULL,
Name6 varchar(50) NULL
)
GO
INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (1,
'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (2,
'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
GO
CREATE TABLE table2
(
pID int IDENTITY (1, 1) NOT NULL,
sID int NOT NULL,
Name varchar(50) NOT NULL
)
GO
INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
--View the tables as they are
select * from table1
select * from table2
--I tried this first but of course it duplicates the sid in table 2 and that
can't happen. I expect there needs to be a looping process on each sid but I am
not sure how to write that
insert into table1 (sid,Name1)
select sid,Name from table2
--Use this to clean out the mess made by the previous insert
delete from table1
where sid >2
THANK YOU !!!
=================================
Try this:
select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
from table2 as a
join table2 as b on b.sID = a.sID and b.Name < a.Name
join table2 as c on c.sID = a.sID and c.Name < b.Name
join table2 as d on d.sID = a.sID and d.Name < c.Name
join table2 as e on e.sID = a.sID and e.Name < d.Name
join table2 as f on f.sID = a.sID and f.Name < e.Name
This will give one row with the names in alphabetical order.
You need as many tables as the number of entries per sID in table2. If table2 does not always have six entries for each
sID, use OUTER JOINs
WANNABE wrote:
> How can the data in a relational table be parsed into a flat file table?
> For instance; table1 has an id for each entry and an sid and name, table2 has an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from table1
> into table2. The records in table1 with identical sid's should create only one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and that
> can't happen. I expect there needs to be a looping process on each sid but I am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================
>
|||After I posted, I realized that outer joins will not give you what you want. They will just give you all permutations
of the names.
I will have to give this some more thought. I have never run into this situation before, but someone else probably has.
Maybe they have a neat solution.
WANNABE wrote:
> There's much that I do not understand;
> Can I use the select statement on it's own to view what will be inserted into
> the table when I run the full query?
> Does this join table2 to itself 6 times?
> Table2 does not always have 6 entries per sid, and neither does table1, but
> should I use FULL OUTER?
> HOW is this suppose to work?
> ======================================
> "Ed Enstrom" <nospam@.invalid.net> wrote in message
> news:Ey0di.75$LW6.21@.newsfe12.lga...
> Try this:
> select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
> from table2 as a
> join table2 as b on b.sID = a.sID and b.Name < a.Name
> join table2 as c on c.sID = a.sID and c.Name < b.Name
> join table2 as d on d.sID = a.sID and d.Name < c.Name
> join table2 as e on e.sID = a.sID and e.Name < d.Name
> join table2 as f on f.sID = a.sID and f.Name < e.Name
> This will give one row with the names in alphabetical order.
> You need as many tables as the number of entries per sID in table2. If table2
> does not always have six entries for each
> sID, use OUTER JOINs
> WANNABE wrote:
>
|||I think you got your tables mixed up -
CREATE TABLE Wtable1
(
pID int primary key,
sID int NOT NULL,
Name varchar(8) NOT NULL
)
GO
INSERT INTO Wtable1 (pID,sID,Name) VALUES (1,3, 'Robert')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (2,3, 'Roger')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (3,3, 'Edgar')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (4,3, 'Mitch')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (5,4, 'Julie')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (6,4, 'Wendy')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (7,4, 'Roberto')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (8,5, 'Jean')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (9,5, 'Jill')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (10,5, 'Alice')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (11,5, 'Claire')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (12,5, 'Steve')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (13,5, 'Hugh')
CREATE TABLE Wtable2
(
sID int primary key,
Name1 varchar(8) NULL,
Name2 varchar(8) NULL,
Name3 varchar(8) NULL,
Name4 varchar(8) NULL,
Name5 varchar(8) NULL,
Name6 varchar(8) NULL
)
GO
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(1,
'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(2,
'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
The RAC utility was designed by a brillant developer (I know him) to
solve these types of problems in the easiest and lest painful way
possible. This is really a pivoting problem and Rac does this well.
Rac is a system of stored procedures and functions that run in
sql server (2k/2005). It has many parameters that allow you to
manipulate (pivot) data and get exactly the result you want. You can
browse the site and look at the online Help to check out each
parameter.
Just insert the Rac result into Wtable2:
insert into Wtable2
Exec Rac
@.transform='Max(Name) as [Name]',
@.rows='sID',
@.pvtcol='pID',
--@.ranklimit would be the maximum NameN in Wtable2
@.from='Wtable1',@.rank='Col',@.ranklimit='6',
@.defaults1='y',@.racheck='y',@.shell='n',
@.replacepvtcols=
'{case when [value]>~~ then [value] else null end for []}',
@.select='select 1*sID as sID,_pvtcols_
from rac '
select * from Wtable2:
sID Name1 Name2 Name3 Name4 Name5 Name6
-- -- -- -- -- -- --
1 Hal Jenny Hank Mary NULL NULL
2 Leroy Anne Curtis NULL NULL NULL
3 Robert Roger Edgar Mitch NULL NULL
4 Julie Wendy Roberto NULL NULL NULL
5 Jean Jill Alice Claire Steve Hugh
Rac inserted rows 3-5.
Rac @.
www.rac4sql.net
More @.
www.beyondsql.blogspot.com
|||Warning: code not run, hence may have typos.
with T1 as (
select sId, name1 as Name from table1 where name1 is not null
union -- not union ALL, so as to eliminate duplicates
select sId, name2 from table1 where name2 is not null
union
... etc. ...
) -- you could also use the UNPIVOT operator to create T1 (briefer code)
insert into table2
select * from T1
where not exists (select * from table2 T2
where T2.sId = T1.Sid and T2.name = T1.Name)
"WANNABE" <SameAsB4> wrote in message
news:uE3dc1FsHHA.5008@.TK2MSFTNGP05.phx.gbl...
> How can the data in a relational table be parsed into a flat file table?
> For instance; table1 has an id for each entry and an sid and name, table2
> has an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from
> table1
> into table2. The records in table1 with identical sid's should create
> only one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and
> that
> can't happen. I expect there needs to be a looping process on each sid
> but I am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================
>
|||These statements should work for you. It may not be the most elegant T-SQL code, but it works.
The first statement seeds table1 with the distinct sID values from table2,
with "N/A" value for all the name columns.
The next statement updates Name1 in table1 with the lowest value in table2
for that sID.
The next statement updates Name2 in table1 with the lowest value in table2
that is not equal to the Name1 value in table1.
The next statement updates Name3 in table1 with the lowest value in table2
that is not equal to both the Name1 and Name2 values in table1.
And so on.
You need as many UPDATE statements as the maximum number of names for any sID.
For those sIDs in table2 that have fewer rows than the number of UPDATE statements, the SELECT subquery will return NULL
and the ISNULL function will substitute "N/A" for the value to be updated.
insert into table1 select distinct sID, 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A'
from table2;
update table1
set Name1 =
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID);
update table1
set Name2 =
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1);
update table1
set Name3=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2);
update table1
set Name4=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3);
update table1
set Name5=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4);
update table1
set Name6=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4
and table2.Name <> table1.Name5);
Ed Enstrom wrote:[vbcol=seagreen]
> After I posted, I realized that outer joins will not give you what you
> want. They will just give you all permutations of the names.
> I will have to give this some more thought. I have never run into this
> situation before, but someone else probably has. Maybe they have a neat
> solution.
> WANNABE wrote:
|||Thanks Mark, I was able to make this code work but it parsed the data INTO the
relational table, and I am looking to parse the data INTO the FLAT file table.
================================================== ==
"Mark Yudkin" <DoNotContactMe@.boingboing.org> wrote in message
news:%23IOEPIMsHHA.3628@.TK2MSFTNGP02.phx.gbl...
Warning: code not run, hence may have typos.
with T1 as (
select sId, name1 as Name from table1 where name1 is not null
union -- not union ALL, so as to eliminate duplicates
select sId, name2 from table1 where name2 is not null
union
... etc. ...
) -- you could also use the UNPIVOT operator to create T1 (briefer code)
insert into table2
select * from T1
where not exists (select * from table2 T2
where T2.sId = T1.Sid and T2.name = T1.Name)
"WANNABE" <SameAsB4> wrote in message
news:uE3dc1FsHHA.5008@.TK2MSFTNGP05.phx.gbl...
> How can the data in a relational table be parsed into a flat file table?
> For instance; table1 has an id for each entry and an sid and name, table2
> has an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from
> table1
> into table2. The records in table1 with identical sid's should create
> only one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and
> that
> can't happen. I expect there needs to be a looping process on each sid
> but I am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================
>
|||Thanks allot Ed, but this replaces NULL values with N/A, and replaces all the
existing Name1...6 fields with N/A
==========================================
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:lOcdi.6$Nx2.2@.newsfe12.lga...
These statements should work for you. It may not be the most elegant T-SQL
code, but it works.
The first statement seeds table1 with the distinct sID values from table2,
with "N/A" value for all the name columns.
The next statement updates Name1 in table1 with the lowest value in table2
for that sID.
The next statement updates Name2 in table1 with the lowest value in table2
that is not equal to the Name1 value in table1.
The next statement updates Name3 in table1 with the lowest value in table2
that is not equal to both the Name1 and Name2 values in table1.
And so on.
You need as many UPDATE statements as the maximum number of names for any sID.
For those sIDs in table2 that have fewer rows than the number of UPDATE
statements, the SELECT subquery will return NULL
and the ISNULL function will substitute "N/A" for the value to be updated.
insert into table1 select distinct sID, 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A'
from table2;
update table1
set Name1 =
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID);
update table1
set Name2 =
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1);
update table1
set Name3=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2);
update table1
set Name4=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3);
update table1
set Name5=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4);
update table1
set Name6=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4
and table2.Name <> table1.Name5);
Ed Enstrom wrote:[vbcol=seagreen]
> After I posted, I realized that outer joins will not give you what you
> want. They will just give you all permutations of the names.
> I will have to give this some more thought. I have never run into this
> situation before, but someone else probably has. Maybe they have a neat
> solution.
> WANNABE wrote:
|||Thanks Susan, That looks KOOL, but as a learning experiment I would like to
understand how this can be done with a simple query. Can you please explain
what you meant, "I think you got your tables mixed up" DID I DO SOMETHING
WRONG?
LOL
========================================
"Steve Dassin" <rac4sqlnospam@.net> wrote in message
news:eZRxmOLsHHA.4932@.TK2MSFTNGP02.phx.gbl...
I think you got your tables mixed up -
CREATE TABLE Wtable1
(
pID int primary key,
sID int NOT NULL,
Name varchar(8) NOT NULL
)
GO
INSERT INTO Wtable1 (pID,sID,Name) VALUES (1,3, 'Robert')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (2,3, 'Roger')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (3,3, 'Edgar')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (4,3, 'Mitch')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (5,4, 'Julie')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (6,4, 'Wendy')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (7,4, 'Roberto')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (8,5, 'Jean')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (9,5, 'Jill')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (10,5, 'Alice')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (11,5, 'Claire')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (12,5, 'Steve')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (13,5, 'Hugh')
CREATE TABLE Wtable2
(
sID int primary key,
Name1 varchar(8) NULL,
Name2 varchar(8) NULL,
Name3 varchar(8) NULL,
Name4 varchar(8) NULL,
Name5 varchar(8) NULL,
Name6 varchar(8) NULL
)
GO
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(1,
'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(2,
'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
The RAC utility was designed by a brillant developer (I know him) to
solve these types of problems in the easiest and lest painful way
possible. This is really a pivoting problem and Rac does this well.
Rac is a system of stored procedures and functions that run in
sql server (2k/2005). It has many parameters that allow you to
manipulate (pivot) data and get exactly the result you want. You can
browse the site and look at the online Help to check out each
parameter.
Just insert the Rac result into Wtable2:
insert into Wtable2
Exec Rac
@.transform='Max(Name) as [Name]',
@.rows='sID',
@.pvtcol='pID',
--@.ranklimit would be the maximum NameN in Wtable2
@.from='Wtable1',@.rank='Col',@.ranklimit='6',
@.defaults1='y',@.racheck='y',@.shell='n',
@.replacepvtcols=
'{case when [value]>~~ then [value] else null end for []}',
@.select='select 1*sID as sID,_pvtcols_
from rac '
select * from Wtable2:
sID Name1 Name2 Name3 Name4 Name5 Name6
-- -- -- -- -- -- --
1 Hal Jenny Hank Mary NULL NULL
2 Leroy Anne Curtis NULL NULL NULL
3 Robert Roger Edgar Mitch NULL NULL
4 Julie Wendy Roberto NULL NULL NULL
5 Jean Jill Alice Claire Steve Hugh
Rac inserted rows 3-5.
Rac @.
www.rac4sql.net
More @.
www.beyondsql.blogspot.com
|||"WANNABE" <SameAsB4> wrote in message
news:uhGNLeVsHHA.484@.TK2MSFTNGP06.phx.gbl...
> Thanks Susan, That looks KOOL, but as a learning experiment I would like
to
> understand how this can be done with a simple query. Can you please
explain
> what you meant, "I think you got your tables mixed up" DID I DO SOMETHING
> WRONG?
> LOL
Susan?...You got something going with my sister? -
>For instance; table1 has an id for each entry and an sid and name, table2
has an
>sid and name1 name2 name3...456.
That's backwards -
Complicated Query Question
How can the data in a relational table be parsed into a flat file table'
For instance; table1 has an id for each entry and an sid and name, table2 ha
s an
sid and name1 name2 name3...456.
I have both tables in the same database and want to import the data from tab
le1
into table2. The records in table1 with identical sid's should create only
one
record in table2. Here is a sample of the tables, and what I have started
with>>
CREATE DATABASE SAMPLE002
GO
USE SAMPLE002
CREATE TABLE table1
(
sID int NOT NULL,
Name1 varchar(50) NULL,
Name2 varchar(50) NULL,
Name3 varchar(50) NULL,
Name4 varchar(50) NULL,
Name5 varchar(50) NULL,
Name6 varchar(50) NULL
)
GO
INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (1
,
'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (2
,
'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
GO
CREATE TABLE table2
(
pID int IDENTITY (1, 1) NOT NULL,
sID int NOT NULL,
Name varchar(50) NOT NULL
)
GO
INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
--View the tables as they are
select * from table1
select * from table2
--I tried this first but of course it duplicates the sid in table 2 and that
can't happen. I expect there needs to be a looping process on each sid but
I am
not sure how to write that
insert into table1 (sid,Name1)
select sid,Name from table2
--Use this to clean out the mess made by the previous insert
delete from table1
where sid >2
THANK YOU !!!
=================================Try this:
select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
from table2 as a
join table2 as b on b.sID = a.sID and b.Name < a.Name
join table2 as c on c.sID = a.sID and c.Name < b.Name
join table2 as d on d.sID = a.sID and d.Name < c.Name
join table2 as e on e.sID = a.sID and e.Name < d.Name
join table2 as f on f.sID = a.sID and f.Name < e.Name
This will give one row with the names in alphabetical order.
You need as many tables as the number of entries per sID in table2. If tabl
e2 does not always have six entries for each
sID, use OUTER JOINs
WANNABE wrote:
> How can the data in a relational table be parsed into a flat file table'
> For instance; table1 has an id for each entry and an sid and name, table2
has an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from t
able1
> into table2. The records in table1 with identical sid's should create onl
y one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and th
at
> can't happen. I expect there needs to be a looping process on each sid bu
t I am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================
>|||There's much that I do not understand;
Can I use the select statement on it's own to view what will be inserted int
o
the table when I run the full query?
Does this join table2 to itself 6 times?
Table2 does not always have 6 entries per sid, and neither does table1, but
should I use FULL OUTER?
HOW is this suppose to work?
======================================
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:Ey0di.75$LW6.21@.newsfe12.lga...
Try this:
select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
from table2 as a
join table2 as b on b.sID = a.sID and b.Name < a.Name
join table2 as c on c.sID = a.sID and c.Name < b.Name
join table2 as d on d.sID = a.sID and d.Name < c.Name
join table2 as e on e.sID = a.sID and e.Name < d.Name
join table2 as f on f.sID = a.sID and f.Name < e.Name
This will give one row with the names in alphabetical order.
You need as many tables as the number of entries per sID in table2. If tabl
e2
does not always have six entries for each
sID, use OUTER JOINs
WANNABE wrote:
> How can the data in a relational table be parsed into a flat file table'
> For instance; table1 has an id for each entry and an sid and name, table2
has
> an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from
> table1
> into table2. The records in table1 with identical sid's should create onl
y
> one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and th
at
> can't happen. I expect there needs to be a looping process on each sid bu
t I
> am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================
>|||After I posted, I realized that outer joins will not give you what you want.
They will just give you all permutations
of the names.
I will have to give this some more thought. I have never run into this situ
ation before, but someone else probably has.
Maybe they have a neat solution.
WANNABE wrote:
> There's much that I do not understand;
> Can I use the select statement on it's own to view what will be inserted
into
> the table when I run the full query?
> Does this join table2 to itself 6 times?
> Table2 does not always have 6 entries per sid, and neither does table1,
but
> should I use FULL OUTER?
> HOW is this suppose to work?
> ======================================
> "Ed Enstrom" <nospam@.invalid.net> wrote in message
> news:Ey0di.75$LW6.21@.newsfe12.lga...
> Try this:
> select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
> from table2 as a
> join table2 as b on b.sID = a.sID and b.Name < a.Name
> join table2 as c on c.sID = a.sID and c.Name < b.Name
> join table2 as d on d.sID = a.sID and d.Name < c.Name
> join table2 as e on e.sID = a.sID and e.Name < d.Name
> join table2 as f on f.sID = a.sID and f.Name < e.Name
> This will give one row with the names in alphabetical order.
> You need as many tables as the number of entries per sID in table2. If ta
ble2
> does not always have six entries for each
> sID, use OUTER JOINs
> WANNABE wrote:
>|||I think you got your tables mixed up -
CREATE TABLE Wtable1
(
pID int primary key,
sID int NOT NULL,
Name varchar(8) NOT NULL
)
GO
INSERT INTO Wtable1 (pID,sID,Name) VALUES (1,3, 'Robert')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (2,3, 'Roger')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (3,3, 'Edgar')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (4,3, 'Mitch')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (5,4, 'Julie')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (6,4, 'Wendy')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (7,4, 'Roberto')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (8,5, 'Jean')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (9,5, 'Jill')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (10,5, 'Alice')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (11,5, 'Claire')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (12,5, 'Steve')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (13,5, 'Hugh')
CREATE TABLE Wtable2
(
sID int primary key,
Name1 varchar(8) NULL,
Name2 varchar(8) NULL,
Name3 varchar(8) NULL,
Name4 varchar(8) NULL,
Name5 varchar(8) NULL,
Name6 varchar(8) NULL
)
GO
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(1,
'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(2,
'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
The RAC utility was designed by a brillant developer (I know him) to
solve these types of problems in the easiest and lest painful way
possible. This is really a pivoting problem and Rac does this well.
Rac is a system of stored procedures and functions that run in
sql server (2k/2005). It has many parameters that allow you to
manipulate (pivot) data and get exactly the result you want. You can
browse the site and look at the online Help to check out each
parameter.
Just insert the Rac result into Wtable2:
insert into Wtable2
Exec Rac
@.transform='Max(Name) as [Name]',
@.rows='sID',
@.pvtcol='pID',
--@.ranklimit would be the maximum NameN in Wtable2
@.from='Wtable1',@.rank='Col',@.ranklimit='
6',
@.defaults1='y',@.racheck='y',@.shell='n',
@.replacepvtcols=
'{case when [value]>~~ then [value] else null end for []}',
@.select='select 1*sID as sID,_pvtcols_
from rac '
select * from Wtable2:
sID Name1 Name2 Name3 Name4 Name5 Name6
-- -- -- -- -- -- --
1 Hal Jenny Hank Mary NULL NULL
2 Leroy Anne Curtis NULL NULL NULL
3 Robert Roger Edgar Mitch NULL NULL
4 Julie Wendy Roberto NULL NULL NULL
5 Jean Jill Alice Claire Steve Hugh
Rac inserted rows 3-5.
Rac @.
www.rac4sql.net
More @.
www.beyondsql.blogspot.com|||Warning: code not run, hence may have typos.
with T1 as (
select sId, name1 as Name from table1 where name1 is not null
union -- not union ALL, so as to eliminate duplicates
select sId, name2 from table1 where name2 is not null
union
... etc. ...
) -- you could also use the UNPIVOT operator to create T1 (briefer code)
insert into table2
select * from T1
where not exists (select * from table2 T2
where T2.sId = T1.Sid and T2.name = T1.Name)
"WANNABE" <SameAsB4> wrote in message
news:uE3dc1FsHHA.5008@.TK2MSFTNGP05.phx.gbl...
> How can the data in a relational table be parsed into a flat file table'
> For instance; table1 has an id for each entry and an sid and name, table2
> has an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from
> table1
> into table2. The records in table1 with identical sid's should create
> only one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and
> that
> can't happen. I expect there needs to be a looping process on each sid
> but I am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================
>|||These statements should work for you. It may not be the most elegant T-SQL
code, but it works.
The first statement seeds table1 with the distinct sID values from table2,
with "N/A" value for all the name columns.
The next statement updates Name1 in table1 with the lowest value in table2
for that sID.
The next statement updates Name2 in table1 with the lowest value in table2
that is not equal to the Name1 value in table1.
The next statement updates Name3 in table1 with the lowest value in table2
that is not equal to both the Name1 and Name2 values in table1.
And so on.
You need as many UPDATE statements as the maximum number of names for any sI
D.
For those sIDs in table2 that have fewer rows than the number of UPDATE stat
ements, the SELECT subquery will return NULL
and the ISNULL function will substitute "N/A" for the value to be updated.
insert into table1 select distinct sID, 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', '
N/A'
from table2;
update table1
set Name1 =
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID);
update table1
set Name2 =
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1);
update table1
set Name3=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2);
update table1
set Name4=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3);
update table1
set Name5=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4);
update table1
set Name6=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4
and table2.Name <> table1.Name5);
Ed Enstrom wrote:[vbcol=seagreen]
> After I posted, I realized that outer joins will not give you what you
> want. They will just give you all permutations of the names.
> I will have to give this some more thought. I have never run into this
> situation before, but someone else probably has. Maybe they have a neat
> solution.
> WANNABE wrote:|||Thanks Mark, I was able to make this code work but it parsed the data INTO t
he
relational table, and I am looking to parse the data INTO the FLAT file tabl
e.
========================================
============
"Mark Yudkin" <DoNotContactMe@.boingboing.org> wrote in message
news:%23IOEPIMsHHA.3628@.TK2MSFTNGP02.phx.gbl...
Warning: code not run, hence may have typos.
with T1 as (
select sId, name1 as Name from table1 where name1 is not null
union -- not union ALL, so as to eliminate duplicates
select sId, name2 from table1 where name2 is not null
union
... etc. ...
) -- you could also use the UNPIVOT operator to create T1 (briefer code)
insert into table2
select * from T1
where not exists (select * from table2 T2
where T2.sId = T1.Sid and T2.name = T1.Name)
"WANNABE" <SameAsB4> wrote in message
news:uE3dc1FsHHA.5008@.TK2MSFTNGP05.phx.gbl...
> How can the data in a relational table be parsed into a flat file table'
> For instance; table1 has an id for each entry and an sid and name, table2
> has an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from
> table1
> into table2. The records in table1 with identical sid's should create
> only one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and
> that
> can't happen. I expect there needs to be a looping process on each sid
> but I am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================
>|||Thanks allot Ed, but this replaces NULL values with N/A, and replaces all th
e
existing Name1...6 fields with N/A
========================================
==
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:lOcdi.6$Nx2.2@.newsfe12.lga...
These statements should work for you. It may not be the most elegant T-SQL
code, but it works.
The first statement seeds table1 with the distinct sID values from table2,
with "N/A" value for all the name columns.
The next statement updates Name1 in table1 with the lowest value in table2
for that sID.
The next statement updates Name2 in table1 with the lowest value in table2
that is not equal to the Name1 value in table1.
The next statement updates Name3 in table1 with the lowest value in table2
that is not equal to both the Name1 and Name2 values in table1.
And so on.
You need as many UPDATE statements as the maximum number of names for any sI
D.
For those sIDs in table2 that have fewer rows than the number of UPDATE
statements, the SELECT subquery will return NULL
and the ISNULL function will substitute "N/A" for the value to be updated.
insert into table1 select distinct sID, 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', '
N/A'
from table2;
update table1
set Name1 =
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID);
update table1
set Name2 =
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1);
update table1
set Name3=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2);
update table1
set Name4=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3);
update table1
set Name5=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4);
update table1
set Name6=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4
and table2.Name <> table1.Name5);
Ed Enstrom wrote:[vbcol=seagreen]
> After I posted, I realized that outer joins will not give you what you
> want. They will just give you all permutations of the names.
> I will have to give this some more thought. I have never run into this
> situation before, but someone else probably has. Maybe they have a neat
> solution.
> WANNABE wrote:|||Thanks Susan, That looks KOOL, but as a learning experiment I would like to
understand how this can be done with a simple query. Can you please explain
what you meant, "I think you got your tables mixed up" DID I DO SOMETHING
WRONG'
LOL
========================================
"Steve Dassin" <rac4sqlnospam@.net> wrote in message
news:eZRxmOLsHHA.4932@.TK2MSFTNGP02.phx.gbl...
I think you got your tables mixed up -
CREATE TABLE Wtable1
(
pID int primary key,
sID int NOT NULL,
Name varchar(8) NOT NULL
)
GO
INSERT INTO Wtable1 (pID,sID,Name) VALUES (1,3, 'Robert')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (2,3, 'Roger')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (3,3, 'Edgar')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (4,3, 'Mitch')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (5,4, 'Julie')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (6,4, 'Wendy')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (7,4, 'Roberto')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (8,5, 'Jean')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (9,5, 'Jill')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (10,5, 'Alice')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (11,5, 'Claire')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (12,5, 'Steve')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (13,5, 'Hugh')
CREATE TABLE Wtable2
(
sID int primary key,
Name1 varchar(8) NULL,
Name2 varchar(8) NULL,
Name3 varchar(8) NULL,
Name4 varchar(8) NULL,
Name5 varchar(8) NULL,
Name6 varchar(8) NULL
)
GO
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(1,
'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(2,
'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
The RAC utility was designed by a brillant developer (I know him) to
solve these types of problems in the easiest and lest painful way
possible. This is really a pivoting problem and Rac does this well.
Rac is a system of stored procedures and functions that run in
sql server (2k/2005). It has many parameters that allow you to
manipulate (pivot) data and get exactly the result you want. You can
browse the site and look at the online Help to check out each
parameter.
Just insert the Rac result into Wtable2:
insert into Wtable2
Exec Rac
@.transform='Max(Name) as [Name]',
@.rows='sID',
@.pvtcol='pID',
--@.ranklimit would be the maximum NameN in Wtable2
@.from='Wtable1',@.rank='Col',@.ranklimit='
6',
@.defaults1='y',@.racheck='y',@.shell='n',
@.replacepvtcols=
'{case when [value]>~~ then [value] else null end for []}',
@.select='select 1*sID as sID,_pvtcols_
from rac '
select * from Wtable2:
sID Name1 Name2 Name3 Name4 Name5 Name6
-- -- -- -- -- -- --
1 Hal Jenny Hank Mary NULL NULL
2 Leroy Anne Curtis NULL NULL NULL
3 Robert Roger Edgar Mitch NULL NULL
4 Julie Wendy Roberto NULL NULL NULL
5 Jean Jill Alice Claire Steve Hugh
Rac inserted rows 3-5.
Rac @.
www.rac4sql.net
More @.
www.beyondsql.blogspot.com
For instance; table1 has an id for each entry and an sid and name, table2 ha
s an
sid and name1 name2 name3...456.
I have both tables in the same database and want to import the data from tab
le1
into table2. The records in table1 with identical sid's should create only
one
record in table2. Here is a sample of the tables, and what I have started
with>>
CREATE DATABASE SAMPLE002
GO
USE SAMPLE002
CREATE TABLE table1
(
sID int NOT NULL,
Name1 varchar(50) NULL,
Name2 varchar(50) NULL,
Name3 varchar(50) NULL,
Name4 varchar(50) NULL,
Name5 varchar(50) NULL,
Name6 varchar(50) NULL
)
GO
INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (1
,
'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (2
,
'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
GO
CREATE TABLE table2
(
pID int IDENTITY (1, 1) NOT NULL,
sID int NOT NULL,
Name varchar(50) NOT NULL
)
GO
INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
--View the tables as they are
select * from table1
select * from table2
--I tried this first but of course it duplicates the sid in table 2 and that
can't happen. I expect there needs to be a looping process on each sid but
I am
not sure how to write that
insert into table1 (sid,Name1)
select sid,Name from table2
--Use this to clean out the mess made by the previous insert
delete from table1
where sid >2
THANK YOU !!!
=================================Try this:
select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
from table2 as a
join table2 as b on b.sID = a.sID and b.Name < a.Name
join table2 as c on c.sID = a.sID and c.Name < b.Name
join table2 as d on d.sID = a.sID and d.Name < c.Name
join table2 as e on e.sID = a.sID and e.Name < d.Name
join table2 as f on f.sID = a.sID and f.Name < e.Name
This will give one row with the names in alphabetical order.
You need as many tables as the number of entries per sID in table2. If tabl
e2 does not always have six entries for each
sID, use OUTER JOINs
WANNABE wrote:
> How can the data in a relational table be parsed into a flat file table'
> For instance; table1 has an id for each entry and an sid and name, table2
has an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from t
able1
> into table2. The records in table1 with identical sid's should create onl
y one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and th
at
> can't happen. I expect there needs to be a looping process on each sid bu
t I am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================
>|||There's much that I do not understand;
Can I use the select statement on it's own to view what will be inserted int
o
the table when I run the full query?
Does this join table2 to itself 6 times?
Table2 does not always have 6 entries per sid, and neither does table1, but
should I use FULL OUTER?
HOW is this suppose to work?
======================================
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:Ey0di.75$LW6.21@.newsfe12.lga...
Try this:
select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
from table2 as a
join table2 as b on b.sID = a.sID and b.Name < a.Name
join table2 as c on c.sID = a.sID and c.Name < b.Name
join table2 as d on d.sID = a.sID and d.Name < c.Name
join table2 as e on e.sID = a.sID and e.Name < d.Name
join table2 as f on f.sID = a.sID and f.Name < e.Name
This will give one row with the names in alphabetical order.
You need as many tables as the number of entries per sID in table2. If tabl
e2
does not always have six entries for each
sID, use OUTER JOINs
WANNABE wrote:
> How can the data in a relational table be parsed into a flat file table'
> For instance; table1 has an id for each entry and an sid and name, table2
has
> an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from
> table1
> into table2. The records in table1 with identical sid's should create onl
y
> one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and th
at
> can't happen. I expect there needs to be a looping process on each sid bu
t I
> am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================
>|||After I posted, I realized that outer joins will not give you what you want.
They will just give you all permutations
of the names.
I will have to give this some more thought. I have never run into this situ
ation before, but someone else probably has.
Maybe they have a neat solution.
WANNABE wrote:
> There's much that I do not understand;
> Can I use the select statement on it's own to view what will be inserted
into
> the table when I run the full query?
> Does this join table2 to itself 6 times?
> Table2 does not always have 6 entries per sid, and neither does table1,
but
> should I use FULL OUTER?
> HOW is this suppose to work?
> ======================================
> "Ed Enstrom" <nospam@.invalid.net> wrote in message
> news:Ey0di.75$LW6.21@.newsfe12.lga...
> Try this:
> select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
> from table2 as a
> join table2 as b on b.sID = a.sID and b.Name < a.Name
> join table2 as c on c.sID = a.sID and c.Name < b.Name
> join table2 as d on d.sID = a.sID and d.Name < c.Name
> join table2 as e on e.sID = a.sID and e.Name < d.Name
> join table2 as f on f.sID = a.sID and f.Name < e.Name
> This will give one row with the names in alphabetical order.
> You need as many tables as the number of entries per sID in table2. If ta
ble2
> does not always have six entries for each
> sID, use OUTER JOINs
> WANNABE wrote:
>|||I think you got your tables mixed up -
CREATE TABLE Wtable1
(
pID int primary key,
sID int NOT NULL,
Name varchar(8) NOT NULL
)
GO
INSERT INTO Wtable1 (pID,sID,Name) VALUES (1,3, 'Robert')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (2,3, 'Roger')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (3,3, 'Edgar')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (4,3, 'Mitch')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (5,4, 'Julie')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (6,4, 'Wendy')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (7,4, 'Roberto')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (8,5, 'Jean')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (9,5, 'Jill')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (10,5, 'Alice')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (11,5, 'Claire')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (12,5, 'Steve')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (13,5, 'Hugh')
CREATE TABLE Wtable2
(
sID int primary key,
Name1 varchar(8) NULL,
Name2 varchar(8) NULL,
Name3 varchar(8) NULL,
Name4 varchar(8) NULL,
Name5 varchar(8) NULL,
Name6 varchar(8) NULL
)
GO
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(1,
'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(2,
'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
The RAC utility was designed by a brillant developer (I know him) to
solve these types of problems in the easiest and lest painful way
possible. This is really a pivoting problem and Rac does this well.
Rac is a system of stored procedures and functions that run in
sql server (2k/2005). It has many parameters that allow you to
manipulate (pivot) data and get exactly the result you want. You can
browse the site and look at the online Help to check out each
parameter.
Just insert the Rac result into Wtable2:
insert into Wtable2
Exec Rac
@.transform='Max(Name) as [Name]',
@.rows='sID',
@.pvtcol='pID',
--@.ranklimit would be the maximum NameN in Wtable2
@.from='Wtable1',@.rank='Col',@.ranklimit='
6',
@.defaults1='y',@.racheck='y',@.shell='n',
@.replacepvtcols=
'{case when [value]>~~ then [value] else null end for []}',
@.select='select 1*sID as sID,_pvtcols_
from rac '
select * from Wtable2:
sID Name1 Name2 Name3 Name4 Name5 Name6
-- -- -- -- -- -- --
1 Hal Jenny Hank Mary NULL NULL
2 Leroy Anne Curtis NULL NULL NULL
3 Robert Roger Edgar Mitch NULL NULL
4 Julie Wendy Roberto NULL NULL NULL
5 Jean Jill Alice Claire Steve Hugh
Rac inserted rows 3-5.
Rac @.
www.rac4sql.net
More @.
www.beyondsql.blogspot.com|||Warning: code not run, hence may have typos.
with T1 as (
select sId, name1 as Name from table1 where name1 is not null
union -- not union ALL, so as to eliminate duplicates
select sId, name2 from table1 where name2 is not null
union
... etc. ...
) -- you could also use the UNPIVOT operator to create T1 (briefer code)
insert into table2
select * from T1
where not exists (select * from table2 T2
where T2.sId = T1.Sid and T2.name = T1.Name)
"WANNABE" <SameAsB4> wrote in message
news:uE3dc1FsHHA.5008@.TK2MSFTNGP05.phx.gbl...
> How can the data in a relational table be parsed into a flat file table'
> For instance; table1 has an id for each entry and an sid and name, table2
> has an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from
> table1
> into table2. The records in table1 with identical sid's should create
> only one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and
> that
> can't happen. I expect there needs to be a looping process on each sid
> but I am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================
>|||These statements should work for you. It may not be the most elegant T-SQL
code, but it works.
The first statement seeds table1 with the distinct sID values from table2,
with "N/A" value for all the name columns.
The next statement updates Name1 in table1 with the lowest value in table2
for that sID.
The next statement updates Name2 in table1 with the lowest value in table2
that is not equal to the Name1 value in table1.
The next statement updates Name3 in table1 with the lowest value in table2
that is not equal to both the Name1 and Name2 values in table1.
And so on.
You need as many UPDATE statements as the maximum number of names for any sI
D.
For those sIDs in table2 that have fewer rows than the number of UPDATE stat
ements, the SELECT subquery will return NULL
and the ISNULL function will substitute "N/A" for the value to be updated.
insert into table1 select distinct sID, 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', '
N/A'
from table2;
update table1
set Name1 =
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID);
update table1
set Name2 =
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1);
update table1
set Name3=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2);
update table1
set Name4=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3);
update table1
set Name5=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4);
update table1
set Name6=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4
and table2.Name <> table1.Name5);
Ed Enstrom wrote:[vbcol=seagreen]
> After I posted, I realized that outer joins will not give you what you
> want. They will just give you all permutations of the names.
> I will have to give this some more thought. I have never run into this
> situation before, but someone else probably has. Maybe they have a neat
> solution.
> WANNABE wrote:|||Thanks Mark, I was able to make this code work but it parsed the data INTO t
he
relational table, and I am looking to parse the data INTO the FLAT file tabl
e.
========================================
============
"Mark Yudkin" <DoNotContactMe@.boingboing.org> wrote in message
news:%23IOEPIMsHHA.3628@.TK2MSFTNGP02.phx.gbl...
Warning: code not run, hence may have typos.
with T1 as (
select sId, name1 as Name from table1 where name1 is not null
union -- not union ALL, so as to eliminate duplicates
select sId, name2 from table1 where name2 is not null
union
... etc. ...
) -- you could also use the UNPIVOT operator to create T1 (briefer code)
insert into table2
select * from T1
where not exists (select * from table2 T2
where T2.sId = T1.Sid and T2.name = T1.Name)
"WANNABE" <SameAsB4> wrote in message
news:uE3dc1FsHHA.5008@.TK2MSFTNGP05.phx.gbl...
> How can the data in a relational table be parsed into a flat file table'
> For instance; table1 has an id for each entry and an sid and name, table2
> has an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from
> table1
> into table2. The records in table1 with identical sid's should create
> only one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and
> that
> can't happen. I expect there needs to be a looping process on each sid
> but I am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================
>|||Thanks allot Ed, but this replaces NULL values with N/A, and replaces all th
e
existing Name1...6 fields with N/A
========================================
==
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:lOcdi.6$Nx2.2@.newsfe12.lga...
These statements should work for you. It may not be the most elegant T-SQL
code, but it works.
The first statement seeds table1 with the distinct sID values from table2,
with "N/A" value for all the name columns.
The next statement updates Name1 in table1 with the lowest value in table2
for that sID.
The next statement updates Name2 in table1 with the lowest value in table2
that is not equal to the Name1 value in table1.
The next statement updates Name3 in table1 with the lowest value in table2
that is not equal to both the Name1 and Name2 values in table1.
And so on.
You need as many UPDATE statements as the maximum number of names for any sI
D.
For those sIDs in table2 that have fewer rows than the number of UPDATE
statements, the SELECT subquery will return NULL
and the ISNULL function will substitute "N/A" for the value to be updated.
insert into table1 select distinct sID, 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', '
N/A'
from table2;
update table1
set Name1 =
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID);
update table1
set Name2 =
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1);
update table1
set Name3=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2);
update table1
set Name4=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3);
update table1
set Name5=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4);
update table1
set Name6=
(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4
and table2.Name <> table1.Name5);
Ed Enstrom wrote:[vbcol=seagreen]
> After I posted, I realized that outer joins will not give you what you
> want. They will just give you all permutations of the names.
> I will have to give this some more thought. I have never run into this
> situation before, but someone else probably has. Maybe they have a neat
> solution.
> WANNABE wrote:|||Thanks Susan, That looks KOOL, but as a learning experiment I would like to
understand how this can be done with a simple query. Can you please explain
what you meant, "I think you got your tables mixed up" DID I DO SOMETHING
WRONG'
LOL
========================================
"Steve Dassin" <rac4sqlnospam@.net> wrote in message
news:eZRxmOLsHHA.4932@.TK2MSFTNGP02.phx.gbl...
I think you got your tables mixed up -
CREATE TABLE Wtable1
(
pID int primary key,
sID int NOT NULL,
Name varchar(8) NOT NULL
)
GO
INSERT INTO Wtable1 (pID,sID,Name) VALUES (1,3, 'Robert')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (2,3, 'Roger')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (3,3, 'Edgar')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (4,3, 'Mitch')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (5,4, 'Julie')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (6,4, 'Wendy')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (7,4, 'Roberto')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (8,5, 'Jean')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (9,5, 'Jill')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (10,5, 'Alice')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (11,5, 'Claire')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (12,5, 'Steve')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (13,5, 'Hugh')
CREATE TABLE Wtable2
(
sID int primary key,
Name1 varchar(8) NULL,
Name2 varchar(8) NULL,
Name3 varchar(8) NULL,
Name4 varchar(8) NULL,
Name5 varchar(8) NULL,
Name6 varchar(8) NULL
)
GO
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(1,
'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(2,
'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
The RAC utility was designed by a brillant developer (I know him) to
solve these types of problems in the easiest and lest painful way
possible. This is really a pivoting problem and Rac does this well.
Rac is a system of stored procedures and functions that run in
sql server (2k/2005). It has many parameters that allow you to
manipulate (pivot) data and get exactly the result you want. You can
browse the site and look at the online Help to check out each
parameter.
Just insert the Rac result into Wtable2:
insert into Wtable2
Exec Rac
@.transform='Max(Name) as [Name]',
@.rows='sID',
@.pvtcol='pID',
--@.ranklimit would be the maximum NameN in Wtable2
@.from='Wtable1',@.rank='Col',@.ranklimit='
6',
@.defaults1='y',@.racheck='y',@.shell='n',
@.replacepvtcols=
'{case when [value]>~~ then [value] else null end for []}',
@.select='select 1*sID as sID,_pvtcols_
from rac '
select * from Wtable2:
sID Name1 Name2 Name3 Name4 Name5 Name6
-- -- -- -- -- -- --
1 Hal Jenny Hank Mary NULL NULL
2 Leroy Anne Curtis NULL NULL NULL
3 Robert Roger Edgar Mitch NULL NULL
4 Julie Wendy Roberto NULL NULL NULL
5 Jean Jill Alice Claire Steve Hugh
Rac inserted rows 3-5.
Rac @.
www.rac4sql.net
More @.
www.beyondsql.blogspot.com
Complicated Query Question
How can the data in a relational table be parsed into a flat file table'
For instance; table1 has an id for each entry and an sid and name, table2 has an
sid and name1 name2 name3...456.
I have both tables in the same database and want to import the data from table1
into table2. The records in table1 with identical sid's should create only one
record in table2. Here is a sample of the tables, and what I have started
with>>
CREATE DATABASE SAMPLE002
GO
USE SAMPLE002
CREATE TABLE table1
(
sID int NOT NULL,
Name1 varchar(50) NULL,
Name2 varchar(50) NULL,
Name3 varchar(50) NULL,
Name4 varchar(50) NULL,
Name5 varchar(50) NULL,
Name6 varchar(50) NULL
)
GO
INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (1,
'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (2,
'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
GO
CREATE TABLE table2
(
pID int IDENTITY (1, 1) NOT NULL,
sID int NOT NULL,
Name varchar(50) NOT NULL
)
GO
INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
--View the tables as they are
select * from table1
select * from table2
--I tried this first but of course it duplicates the sid in table 2 and that
can't happen. I expect there needs to be a looping process on each sid but I am
not sure how to write that
insert into table1 (sid,Name1)
select sid,Name from table2
--Use this to clean out the mess made by the previous insert
delete from table1
where sid >2
THANK YOU !!!
=================================Try this:
select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
from table2 as a
join table2 as b on b.sID = a.sID and b.Name < a.Name
join table2 as c on c.sID = a.sID and c.Name < b.Name
join table2 as d on d.sID = a.sID and d.Name < c.Name
join table2 as e on e.sID = a.sID and e.Name < d.Name
join table2 as f on f.sID = a.sID and f.Name < e.Name
This will give one row with the names in alphabetical order.
You need as many tables as the number of entries per sID in table2. If table2 does not always have six entries for each
sID, use OUTER JOINs
WANNABE wrote:
> How can the data in a relational table be parsed into a flat file table'
> For instance; table1 has an id for each entry and an sid and name, table2 has an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from table1
> into table2. The records in table1 with identical sid's should create only one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and that
> can't happen. I expect there needs to be a looping process on each sid but I am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================>|||There's much that I do not understand;
Can I use the select statement on it's own to view what will be inserted into
the table when I run the full query?
Does this join table2 to itself 6 times?
Table2 does not always have 6 entries per sid, and neither does table1, but
should I use FULL OUTER?
HOW is this suppose to work?
======================================"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:Ey0di.75$LW6.21@.newsfe12.lga...
Try this:
select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
from table2 as a
join table2 as b on b.sID = a.sID and b.Name < a.Name
join table2 as c on c.sID = a.sID and c.Name < b.Name
join table2 as d on d.sID = a.sID and d.Name < c.Name
join table2 as e on e.sID = a.sID and e.Name < d.Name
join table2 as f on f.sID = a.sID and f.Name < e.Name
This will give one row with the names in alphabetical order.
You need as many tables as the number of entries per sID in table2. If table2
does not always have six entries for each
sID, use OUTER JOINs
WANNABE wrote:
> How can the data in a relational table be parsed into a flat file table'
> For instance; table1 has an id for each entry and an sid and name, table2 has
> an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from
> table1
> into table2. The records in table1 with identical sid's should create only
> one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and that
> can't happen. I expect there needs to be a looping process on each sid but I
> am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================>|||After I posted, I realized that outer joins will not give you what you want. They will just give you all permutations
of the names.
I will have to give this some more thought. I have never run into this situation before, but someone else probably has.
Maybe they have a neat solution.
WANNABE wrote:
> There's much that I do not understand;
> Can I use the select statement on it's own to view what will be inserted into
> the table when I run the full query?
> Does this join table2 to itself 6 times?
> Table2 does not always have 6 entries per sid, and neither does table1, but
> should I use FULL OUTER?
> HOW is this suppose to work?
> ======================================> "Ed Enstrom" <nospam@.invalid.net> wrote in message
> news:Ey0di.75$LW6.21@.newsfe12.lga...
> Try this:
> select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
> from table2 as a
> join table2 as b on b.sID = a.sID and b.Name < a.Name
> join table2 as c on c.sID = a.sID and c.Name < b.Name
> join table2 as d on d.sID = a.sID and d.Name < c.Name
> join table2 as e on e.sID = a.sID and e.Name < d.Name
> join table2 as f on f.sID = a.sID and f.Name < e.Name
> This will give one row with the names in alphabetical order.
> You need as many tables as the number of entries per sID in table2. If table2
> does not always have six entries for each
> sID, use OUTER JOINs
> WANNABE wrote:
>> How can the data in a relational table be parsed into a flat file table'
>> For instance; table1 has an id for each entry and an sid and name, table2 has
>> an
>> sid and name1 name2 name3...456.
>> I have both tables in the same database and want to import the data from
>> table1
>> into table2. The records in table1 with identical sid's should create only
>> one
>> record in table2. Here is a sample of the tables, and what I have started
>> with>>
>> CREATE DATABASE SAMPLE002
>> GO
>> USE SAMPLE002
>> CREATE TABLE table1
>> (
>> sID int NOT NULL,
>> Name1 varchar(50) NULL,
>> Name2 varchar(50) NULL,
>> Name3 varchar(50) NULL,
>> Name4 varchar(50) NULL,
>> Name5 varchar(50) NULL,
>> Name6 varchar(50) NULL
>> )
>> GO
>> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (1,
>> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
>> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (2,
>> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
>> GO
>> CREATE TABLE table2
>> (
>> pID int IDENTITY (1, 1) NOT NULL,
>> sID int NOT NULL,
>> Name varchar(50) NOT NULL
>> )
>> GO
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
>> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
>> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
>> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
>> --View the tables as they are
>> select * from table1
>> select * from table2
>> --I tried this first but of course it duplicates the sid in table 2 and that
>> can't happen. I expect there needs to be a looping process on each sid but I
>> am
>> not sure how to write that
>> insert into table1 (sid,Name1)
>> select sid,Name from table2
>> --Use this to clean out the mess made by the previous insert
>> delete from table1
>> where sid >2
>> THANK YOU !!!
>> =================================>>
>|||I think you got your tables mixed up -:)
CREATE TABLE Wtable1
(
pID int primary key,
sID int NOT NULL,
Name varchar(8) NOT NULL
)
GO
INSERT INTO Wtable1 (pID,sID,Name) VALUES (1,3, 'Robert')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (2,3, 'Roger')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (3,3, 'Edgar')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (4,3, 'Mitch')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (5,4, 'Julie')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (6,4, 'Wendy')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (7,4, 'Roberto')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (8,5, 'Jean')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (9,5, 'Jill')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (10,5, 'Alice')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (11,5, 'Claire')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (12,5, 'Steve')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (13,5, 'Hugh')
CREATE TABLE Wtable2
(
sID int primary key,
Name1 varchar(8) NULL,
Name2 varchar(8) NULL,
Name3 varchar(8) NULL,
Name4 varchar(8) NULL,
Name5 varchar(8) NULL,
Name6 varchar(8) NULL
)
GO
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(1,
'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(2,
'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
The RAC utility was designed by a brillant developer (I know him) to
solve these types of problems in the easiest and lest painful way
possible. This is really a pivoting problem and Rac does this well.
Rac is a system of stored procedures and functions that run in
sql server (2k/2005). It has many parameters that allow you to
manipulate (pivot) data and get exactly the result you want. You can
browse the site and look at the online Help to check out each
parameter.
Just insert the Rac result into Wtable2:
insert into Wtable2
Exec Rac
@.transform='Max(Name) as [Name]',
@.rows='sID',
@.pvtcol='pID',
--@.ranklimit would be the maximum NameN in Wtable2
@.from='Wtable1',@.rank='Col',@.ranklimit='6',
@.defaults1='y',@.racheck='y',@.shell='n',
@.replacepvtcols='{case when [value]>~~ then [value] else null end for []}',
@.select='select 1*sID as sID,_pvtcols_
from rac '
select * from Wtable2:
sID Name1 Name2 Name3 Name4 Name5 Name6
-- -- -- -- -- -- --
1 Hal Jenny Hank Mary NULL NULL
2 Leroy Anne Curtis NULL NULL NULL
3 Robert Roger Edgar Mitch NULL NULL
4 Julie Wendy Roberto NULL NULL NULL
5 Jean Jill Alice Claire Steve Hugh
Rac inserted rows 3-5.
Rac @.
www.rac4sql.net
More @.
www.beyondsql.blogspot.com|||Warning: code not run, hence may have typos.
with T1 as (
select sId, name1 as Name from table1 where name1 is not null
union -- not union ALL, so as to eliminate duplicates
select sId, name2 from table1 where name2 is not null
union
... etc. ...
) -- you could also use the UNPIVOT operator to create T1 (briefer code)
insert into table2
select * from T1
where not exists (select * from table2 T2
where T2.sId = T1.Sid and T2.name = T1.Name)
"WANNABE" <SameAsB4> wrote in message
news:uE3dc1FsHHA.5008@.TK2MSFTNGP05.phx.gbl...
> How can the data in a relational table be parsed into a flat file table'
> For instance; table1 has an id for each entry and an sid and name, table2
> has an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from
> table1
> into table2. The records in table1 with identical sid's should create
> only one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and
> that
> can't happen. I expect there needs to be a looping process on each sid
> but I am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================>|||These statements should work for you. It may not be the most elegant T-SQL code, but it works.
The first statement seeds table1 with the distinct sID values from table2,
with "N/A" value for all the name columns.
The next statement updates Name1 in table1 with the lowest value in table2
for that sID.
The next statement updates Name2 in table1 with the lowest value in table2
that is not equal to the Name1 value in table1.
The next statement updates Name3 in table1 with the lowest value in table2
that is not equal to both the Name1 and Name2 values in table1.
And so on.
You need as many UPDATE statements as the maximum number of names for any sID.
For those sIDs in table2 that have fewer rows than the number of UPDATE statements, the SELECT subquery will return NULL
and the ISNULL function will substitute "N/A" for the value to be updated.
insert into table1 select distinct sID, 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A'
from table2;
update table1
set Name1 =(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID);
update table1
set Name2 =(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1);
update table1
set Name3=(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2);
update table1
set Name4=(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3);
update table1
set Name5=(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4);
update table1
set Name6=(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4
and table2.Name <> table1.Name5);
Ed Enstrom wrote:
> After I posted, I realized that outer joins will not give you what you
> want. They will just give you all permutations of the names.
> I will have to give this some more thought. I have never run into this
> situation before, but someone else probably has. Maybe they have a neat
> solution.
> WANNABE wrote:
>> There's much that I do not understand;
>> Can I use the select statement on it's own to view what will be
>> inserted into the table when I run the full query?
>> Does this join table2 to itself 6 times?
>> Table2 does not always have 6 entries per sid, and neither does
>> table1, but should I use FULL OUTER?
>> HOW is this suppose to work?
>> ======================================>> "Ed Enstrom" <nospam@.invalid.net> wrote in message
>> news:Ey0di.75$LW6.21@.newsfe12.lga...
>> Try this:
>> select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
>> from table2 as a
>> join table2 as b on b.sID = a.sID and b.Name < a.Name
>> join table2 as c on c.sID = a.sID and c.Name < b.Name
>> join table2 as d on d.sID = a.sID and d.Name < c.Name
>> join table2 as e on e.sID = a.sID and e.Name < d.Name
>> join table2 as f on f.sID = a.sID and f.Name < e.Name
>> This will give one row with the names in alphabetical order.
>> You need as many tables as the number of entries per sID in table2.
>> If table2 does not always have six entries for each
>> sID, use OUTER JOINs
>> WANNABE wrote:
>> How can the data in a relational table be parsed into a flat file
>> table'
>> For instance; table1 has an id for each entry and an sid and name,
>> table2 has an
>> sid and name1 name2 name3...456.
>> I have both tables in the same database and want to import the data
>> from table1
>> into table2. The records in table1 with identical sid's should
>> create only one
>> record in table2. Here is a sample of the tables, and what I have
>> started
>> with>>
>> CREATE DATABASE SAMPLE002
>> GO
>> USE SAMPLE002
>> CREATE TABLE table1
>> (
>> sID int NOT NULL,
>> Name1 varchar(50) NULL,
>> Name2 varchar(50) NULL,
>> Name3 varchar(50) NULL,
>> Name4 varchar(50) NULL,
>> Name5 varchar(50) NULL,
>> Name6 varchar(50) NULL
>> )
>> GO
>> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6)
>> VALUES (1,
>> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
>> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6)
>> VALUES (2,
>> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
>> GO
>> CREATE TABLE table2
>> (
>> pID int IDENTITY (1, 1) NOT NULL,
>> sID int NOT NULL,
>> Name varchar(50) NOT NULL
>> )
>> GO
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
>> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
>> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
>> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
>> --View the tables as they are
>> select * from table1
>> select * from table2
>> --I tried this first but of course it duplicates the sid in table 2
>> and that
>> can't happen. I expect there needs to be a looping process on each
>> sid but I am
>> not sure how to write that
>> insert into table1 (sid,Name1)
>> select sid,Name from table2
>> --Use this to clean out the mess made by the previous insert
>> delete from table1
>> where sid >2
>> THANK YOU !!!
>> =================================>>
>>|||Thanks Mark, I was able to make this code work but it parsed the data INTO the
relational table, and I am looking to parse the data INTO the FLAT file table.
===================================================="Mark Yudkin" <DoNotContactMe@.boingboing.org> wrote in message
news:%23IOEPIMsHHA.3628@.TK2MSFTNGP02.phx.gbl...
Warning: code not run, hence may have typos.
with T1 as (
select sId, name1 as Name from table1 where name1 is not null
union -- not union ALL, so as to eliminate duplicates
select sId, name2 from table1 where name2 is not null
union
... etc. ...
) -- you could also use the UNPIVOT operator to create T1 (briefer code)
insert into table2
select * from T1
where not exists (select * from table2 T2
where T2.sId = T1.Sid and T2.name = T1.Name)
"WANNABE" <SameAsB4> wrote in message
news:uE3dc1FsHHA.5008@.TK2MSFTNGP05.phx.gbl...
> How can the data in a relational table be parsed into a flat file table'
> For instance; table1 has an id for each entry and an sid and name, table2
> has an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from
> table1
> into table2. The records in table1 with identical sid's should create
> only one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and
> that
> can't happen. I expect there needs to be a looping process on each sid
> but I am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================>|||Thanks allot Ed, but this replaces NULL values with N/A, and replaces all the
existing Name1...6 fields with N/A
=========================================="Ed Enstrom" <nospam@.invalid.net> wrote in message
news:lOcdi.6$Nx2.2@.newsfe12.lga...
These statements should work for you. It may not be the most elegant T-SQL
code, but it works.
The first statement seeds table1 with the distinct sID values from table2,
with "N/A" value for all the name columns.
The next statement updates Name1 in table1 with the lowest value in table2
for that sID.
The next statement updates Name2 in table1 with the lowest value in table2
that is not equal to the Name1 value in table1.
The next statement updates Name3 in table1 with the lowest value in table2
that is not equal to both the Name1 and Name2 values in table1.
And so on.
You need as many UPDATE statements as the maximum number of names for any sID.
For those sIDs in table2 that have fewer rows than the number of UPDATE
statements, the SELECT subquery will return NULL
and the ISNULL function will substitute "N/A" for the value to be updated.
insert into table1 select distinct sID, 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A'
from table2;
update table1
set Name1 =(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID);
update table1
set Name2 =(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1);
update table1
set Name3=(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2);
update table1
set Name4=(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3);
update table1
set Name5=(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4);
update table1
set Name6=(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4
and table2.Name <> table1.Name5);
Ed Enstrom wrote:
> After I posted, I realized that outer joins will not give you what you
> want. They will just give you all permutations of the names.
> I will have to give this some more thought. I have never run into this
> situation before, but someone else probably has. Maybe they have a neat
> solution.
> WANNABE wrote:
>> There's much that I do not understand;
>> Can I use the select statement on it's own to view what will be
>> inserted into the table when I run the full query?
>> Does this join table2 to itself 6 times?
>> Table2 does not always have 6 entries per sid, and neither does
>> table1, but should I use FULL OUTER?
>> HOW is this suppose to work?
>> ======================================>> "Ed Enstrom" <nospam@.invalid.net> wrote in message
>> news:Ey0di.75$LW6.21@.newsfe12.lga...
>> Try this:
>> select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
>> from table2 as a
>> join table2 as b on b.sID = a.sID and b.Name < a.Name
>> join table2 as c on c.sID = a.sID and c.Name < b.Name
>> join table2 as d on d.sID = a.sID and d.Name < c.Name
>> join table2 as e on e.sID = a.sID and e.Name < d.Name
>> join table2 as f on f.sID = a.sID and f.Name < e.Name
>> This will give one row with the names in alphabetical order.
>> You need as many tables as the number of entries per sID in table2.
>> If table2 does not always have six entries for each
>> sID, use OUTER JOINs
>> WANNABE wrote:
>> How can the data in a relational table be parsed into a flat file
>> table'
>> For instance; table1 has an id for each entry and an sid and name,
>> table2 has an
>> sid and name1 name2 name3...456.
>> I have both tables in the same database and want to import the data
>> from table1
>> into table2. The records in table1 with identical sid's should
>> create only one
>> record in table2. Here is a sample of the tables, and what I have
>> started
>> with>>
>> CREATE DATABASE SAMPLE002
>> GO
>> USE SAMPLE002
>> CREATE TABLE table1
>> (
>> sID int NOT NULL,
>> Name1 varchar(50) NULL,
>> Name2 varchar(50) NULL,
>> Name3 varchar(50) NULL,
>> Name4 varchar(50) NULL,
>> Name5 varchar(50) NULL,
>> Name6 varchar(50) NULL
>> )
>> GO
>> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6)
>> VALUES (1,
>> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
>> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6)
>> VALUES (2,
>> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
>> GO
>> CREATE TABLE table2
>> (
>> pID int IDENTITY (1, 1) NOT NULL,
>> sID int NOT NULL,
>> Name varchar(50) NOT NULL
>> )
>> GO
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
>> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
>> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
>> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
>> --View the tables as they are
>> select * from table1
>> select * from table2
>> --I tried this first but of course it duplicates the sid in table 2
>> and that
>> can't happen. I expect there needs to be a looping process on each
>> sid but I am
>> not sure how to write that
>> insert into table1 (sid,Name1)
>> select sid,Name from table2
>> --Use this to clean out the mess made by the previous insert
>> delete from table1
>> where sid >2
>> THANK YOU !!!
>> =================================>>
>>|||Thanks Susan, That looks KOOL, but as a learning experiment I would like to
understand how this can be done with a simple query. Can you please explain
what you meant, "I think you got your tables mixed up" DID I DO SOMETHING
WRONG'
LOL
========================================"Steve Dassin" <rac4sqlnospam@.net> wrote in message
news:eZRxmOLsHHA.4932@.TK2MSFTNGP02.phx.gbl...
I think you got your tables mixed up -:)
CREATE TABLE Wtable1
(
pID int primary key,
sID int NOT NULL,
Name varchar(8) NOT NULL
)
GO
INSERT INTO Wtable1 (pID,sID,Name) VALUES (1,3, 'Robert')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (2,3, 'Roger')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (3,3, 'Edgar')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (4,3, 'Mitch')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (5,4, 'Julie')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (6,4, 'Wendy')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (7,4, 'Roberto')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (8,5, 'Jean')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (9,5, 'Jill')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (10,5, 'Alice')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (11,5, 'Claire')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (12,5, 'Steve')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (13,5, 'Hugh')
CREATE TABLE Wtable2
(
sID int primary key,
Name1 varchar(8) NULL,
Name2 varchar(8) NULL,
Name3 varchar(8) NULL,
Name4 varchar(8) NULL,
Name5 varchar(8) NULL,
Name6 varchar(8) NULL
)
GO
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(1,
'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(2,
'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
The RAC utility was designed by a brillant developer (I know him) to
solve these types of problems in the easiest and lest painful way
possible. This is really a pivoting problem and Rac does this well.
Rac is a system of stored procedures and functions that run in
sql server (2k/2005). It has many parameters that allow you to
manipulate (pivot) data and get exactly the result you want. You can
browse the site and look at the online Help to check out each
parameter.
Just insert the Rac result into Wtable2:
insert into Wtable2
Exec Rac
@.transform='Max(Name) as [Name]',
@.rows='sID',
@.pvtcol='pID',
--@.ranklimit would be the maximum NameN in Wtable2
@.from='Wtable1',@.rank='Col',@.ranklimit='6',
@.defaults1='y',@.racheck='y',@.shell='n',
@.replacepvtcols='{case when [value]>~~ then [value] else null end for []}',
@.select='select 1*sID as sID,_pvtcols_
from rac '
select * from Wtable2:
sID Name1 Name2 Name3 Name4 Name5 Name6
-- -- -- -- -- -- --
1 Hal Jenny Hank Mary NULL NULL
2 Leroy Anne Curtis NULL NULL NULL
3 Robert Roger Edgar Mitch NULL NULL
4 Julie Wendy Roberto NULL NULL NULL
5 Jean Jill Alice Claire Steve Hugh
Rac inserted rows 3-5.
Rac @.
www.rac4sql.net
More @.
www.beyondsql.blogspot.com|||"WANNABE" <SameAsB4> wrote in message
news:uhGNLeVsHHA.484@.TK2MSFTNGP06.phx.gbl...
> Thanks Susan, That looks KOOL, but as a learning experiment I would like
to
> understand how this can be done with a simple query. Can you please
explain
> what you meant, "I think you got your tables mixed up" DID I DO SOMETHING
> WRONG'
> LOL
Susan?...You got something going with my sister? -:)
>For instance; table1 has an id for each entry and an sid and name, table2
has an
>sid and name1 name2 name3...456.
That's backwards -:)|||Sorry Steve, Thanks Steve :-) LOL to you to, is your sister, never mind... :-)
I must have some dyslexic thing, mixing up my tables and names..
I see it now, Thanks again for all your help...
====================================="Steve Dassin" <rac4sqlnospam@.net> wrote in message
news:%23eZ$T4VsHHA.1208@.TK2MSFTNGP03.phx.gbl...
"WANNABE" <SameAsB4> wrote in message
news:uhGNLeVsHHA.484@.TK2MSFTNGP06.phx.gbl...
> Thanks Susan, That looks KOOL, but as a learning experiment I would like
to
> understand how this can be done with a simple query. Can you please
explain
> what you meant, "I think you got your tables mixed up" DID I DO SOMETHING
> WRONG'
> LOL
Susan?...You got something going with my sister? -:)
>For instance; table1 has an id for each entry and an sid and name, table2
has an
>sid and name1 name2 name3...456.
That's backwards -:)
For instance; table1 has an id for each entry and an sid and name, table2 has an
sid and name1 name2 name3...456.
I have both tables in the same database and want to import the data from table1
into table2. The records in table1 with identical sid's should create only one
record in table2. Here is a sample of the tables, and what I have started
with>>
CREATE DATABASE SAMPLE002
GO
USE SAMPLE002
CREATE TABLE table1
(
sID int NOT NULL,
Name1 varchar(50) NULL,
Name2 varchar(50) NULL,
Name3 varchar(50) NULL,
Name4 varchar(50) NULL,
Name5 varchar(50) NULL,
Name6 varchar(50) NULL
)
GO
INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (1,
'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (2,
'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
GO
CREATE TABLE table2
(
pID int IDENTITY (1, 1) NOT NULL,
sID int NOT NULL,
Name varchar(50) NOT NULL
)
GO
INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
--View the tables as they are
select * from table1
select * from table2
--I tried this first but of course it duplicates the sid in table 2 and that
can't happen. I expect there needs to be a looping process on each sid but I am
not sure how to write that
insert into table1 (sid,Name1)
select sid,Name from table2
--Use this to clean out the mess made by the previous insert
delete from table1
where sid >2
THANK YOU !!!
=================================Try this:
select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
from table2 as a
join table2 as b on b.sID = a.sID and b.Name < a.Name
join table2 as c on c.sID = a.sID and c.Name < b.Name
join table2 as d on d.sID = a.sID and d.Name < c.Name
join table2 as e on e.sID = a.sID and e.Name < d.Name
join table2 as f on f.sID = a.sID and f.Name < e.Name
This will give one row with the names in alphabetical order.
You need as many tables as the number of entries per sID in table2. If table2 does not always have six entries for each
sID, use OUTER JOINs
WANNABE wrote:
> How can the data in a relational table be parsed into a flat file table'
> For instance; table1 has an id for each entry and an sid and name, table2 has an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from table1
> into table2. The records in table1 with identical sid's should create only one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and that
> can't happen. I expect there needs to be a looping process on each sid but I am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================>|||There's much that I do not understand;
Can I use the select statement on it's own to view what will be inserted into
the table when I run the full query?
Does this join table2 to itself 6 times?
Table2 does not always have 6 entries per sid, and neither does table1, but
should I use FULL OUTER?
HOW is this suppose to work?
======================================"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:Ey0di.75$LW6.21@.newsfe12.lga...
Try this:
select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
from table2 as a
join table2 as b on b.sID = a.sID and b.Name < a.Name
join table2 as c on c.sID = a.sID and c.Name < b.Name
join table2 as d on d.sID = a.sID and d.Name < c.Name
join table2 as e on e.sID = a.sID and e.Name < d.Name
join table2 as f on f.sID = a.sID and f.Name < e.Name
This will give one row with the names in alphabetical order.
You need as many tables as the number of entries per sID in table2. If table2
does not always have six entries for each
sID, use OUTER JOINs
WANNABE wrote:
> How can the data in a relational table be parsed into a flat file table'
> For instance; table1 has an id for each entry and an sid and name, table2 has
> an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from
> table1
> into table2. The records in table1 with identical sid's should create only
> one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and that
> can't happen. I expect there needs to be a looping process on each sid but I
> am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================>|||After I posted, I realized that outer joins will not give you what you want. They will just give you all permutations
of the names.
I will have to give this some more thought. I have never run into this situation before, but someone else probably has.
Maybe they have a neat solution.
WANNABE wrote:
> There's much that I do not understand;
> Can I use the select statement on it's own to view what will be inserted into
> the table when I run the full query?
> Does this join table2 to itself 6 times?
> Table2 does not always have 6 entries per sid, and neither does table1, but
> should I use FULL OUTER?
> HOW is this suppose to work?
> ======================================> "Ed Enstrom" <nospam@.invalid.net> wrote in message
> news:Ey0di.75$LW6.21@.newsfe12.lga...
> Try this:
> select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
> from table2 as a
> join table2 as b on b.sID = a.sID and b.Name < a.Name
> join table2 as c on c.sID = a.sID and c.Name < b.Name
> join table2 as d on d.sID = a.sID and d.Name < c.Name
> join table2 as e on e.sID = a.sID and e.Name < d.Name
> join table2 as f on f.sID = a.sID and f.Name < e.Name
> This will give one row with the names in alphabetical order.
> You need as many tables as the number of entries per sID in table2. If table2
> does not always have six entries for each
> sID, use OUTER JOINs
> WANNABE wrote:
>> How can the data in a relational table be parsed into a flat file table'
>> For instance; table1 has an id for each entry and an sid and name, table2 has
>> an
>> sid and name1 name2 name3...456.
>> I have both tables in the same database and want to import the data from
>> table1
>> into table2. The records in table1 with identical sid's should create only
>> one
>> record in table2. Here is a sample of the tables, and what I have started
>> with>>
>> CREATE DATABASE SAMPLE002
>> GO
>> USE SAMPLE002
>> CREATE TABLE table1
>> (
>> sID int NOT NULL,
>> Name1 varchar(50) NULL,
>> Name2 varchar(50) NULL,
>> Name3 varchar(50) NULL,
>> Name4 varchar(50) NULL,
>> Name5 varchar(50) NULL,
>> Name6 varchar(50) NULL
>> )
>> GO
>> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (1,
>> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
>> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES (2,
>> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
>> GO
>> CREATE TABLE table2
>> (
>> pID int IDENTITY (1, 1) NOT NULL,
>> sID int NOT NULL,
>> Name varchar(50) NOT NULL
>> )
>> GO
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
>> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
>> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
>> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
>> --View the tables as they are
>> select * from table1
>> select * from table2
>> --I tried this first but of course it duplicates the sid in table 2 and that
>> can't happen. I expect there needs to be a looping process on each sid but I
>> am
>> not sure how to write that
>> insert into table1 (sid,Name1)
>> select sid,Name from table2
>> --Use this to clean out the mess made by the previous insert
>> delete from table1
>> where sid >2
>> THANK YOU !!!
>> =================================>>
>|||I think you got your tables mixed up -:)
CREATE TABLE Wtable1
(
pID int primary key,
sID int NOT NULL,
Name varchar(8) NOT NULL
)
GO
INSERT INTO Wtable1 (pID,sID,Name) VALUES (1,3, 'Robert')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (2,3, 'Roger')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (3,3, 'Edgar')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (4,3, 'Mitch')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (5,4, 'Julie')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (6,4, 'Wendy')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (7,4, 'Roberto')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (8,5, 'Jean')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (9,5, 'Jill')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (10,5, 'Alice')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (11,5, 'Claire')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (12,5, 'Steve')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (13,5, 'Hugh')
CREATE TABLE Wtable2
(
sID int primary key,
Name1 varchar(8) NULL,
Name2 varchar(8) NULL,
Name3 varchar(8) NULL,
Name4 varchar(8) NULL,
Name5 varchar(8) NULL,
Name6 varchar(8) NULL
)
GO
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(1,
'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(2,
'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
The RAC utility was designed by a brillant developer (I know him) to
solve these types of problems in the easiest and lest painful way
possible. This is really a pivoting problem and Rac does this well.
Rac is a system of stored procedures and functions that run in
sql server (2k/2005). It has many parameters that allow you to
manipulate (pivot) data and get exactly the result you want. You can
browse the site and look at the online Help to check out each
parameter.
Just insert the Rac result into Wtable2:
insert into Wtable2
Exec Rac
@.transform='Max(Name) as [Name]',
@.rows='sID',
@.pvtcol='pID',
--@.ranklimit would be the maximum NameN in Wtable2
@.from='Wtable1',@.rank='Col',@.ranklimit='6',
@.defaults1='y',@.racheck='y',@.shell='n',
@.replacepvtcols='{case when [value]>~~ then [value] else null end for []}',
@.select='select 1*sID as sID,_pvtcols_
from rac '
select * from Wtable2:
sID Name1 Name2 Name3 Name4 Name5 Name6
-- -- -- -- -- -- --
1 Hal Jenny Hank Mary NULL NULL
2 Leroy Anne Curtis NULL NULL NULL
3 Robert Roger Edgar Mitch NULL NULL
4 Julie Wendy Roberto NULL NULL NULL
5 Jean Jill Alice Claire Steve Hugh
Rac inserted rows 3-5.
Rac @.
www.rac4sql.net
More @.
www.beyondsql.blogspot.com|||Warning: code not run, hence may have typos.
with T1 as (
select sId, name1 as Name from table1 where name1 is not null
union -- not union ALL, so as to eliminate duplicates
select sId, name2 from table1 where name2 is not null
union
... etc. ...
) -- you could also use the UNPIVOT operator to create T1 (briefer code)
insert into table2
select * from T1
where not exists (select * from table2 T2
where T2.sId = T1.Sid and T2.name = T1.Name)
"WANNABE" <SameAsB4> wrote in message
news:uE3dc1FsHHA.5008@.TK2MSFTNGP05.phx.gbl...
> How can the data in a relational table be parsed into a flat file table'
> For instance; table1 has an id for each entry and an sid and name, table2
> has an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from
> table1
> into table2. The records in table1 with identical sid's should create
> only one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and
> that
> can't happen. I expect there needs to be a looping process on each sid
> but I am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================>|||These statements should work for you. It may not be the most elegant T-SQL code, but it works.
The first statement seeds table1 with the distinct sID values from table2,
with "N/A" value for all the name columns.
The next statement updates Name1 in table1 with the lowest value in table2
for that sID.
The next statement updates Name2 in table1 with the lowest value in table2
that is not equal to the Name1 value in table1.
The next statement updates Name3 in table1 with the lowest value in table2
that is not equal to both the Name1 and Name2 values in table1.
And so on.
You need as many UPDATE statements as the maximum number of names for any sID.
For those sIDs in table2 that have fewer rows than the number of UPDATE statements, the SELECT subquery will return NULL
and the ISNULL function will substitute "N/A" for the value to be updated.
insert into table1 select distinct sID, 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A'
from table2;
update table1
set Name1 =(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID);
update table1
set Name2 =(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1);
update table1
set Name3=(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2);
update table1
set Name4=(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3);
update table1
set Name5=(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4);
update table1
set Name6=(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4
and table2.Name <> table1.Name5);
Ed Enstrom wrote:
> After I posted, I realized that outer joins will not give you what you
> want. They will just give you all permutations of the names.
> I will have to give this some more thought. I have never run into this
> situation before, but someone else probably has. Maybe they have a neat
> solution.
> WANNABE wrote:
>> There's much that I do not understand;
>> Can I use the select statement on it's own to view what will be
>> inserted into the table when I run the full query?
>> Does this join table2 to itself 6 times?
>> Table2 does not always have 6 entries per sid, and neither does
>> table1, but should I use FULL OUTER?
>> HOW is this suppose to work?
>> ======================================>> "Ed Enstrom" <nospam@.invalid.net> wrote in message
>> news:Ey0di.75$LW6.21@.newsfe12.lga...
>> Try this:
>> select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
>> from table2 as a
>> join table2 as b on b.sID = a.sID and b.Name < a.Name
>> join table2 as c on c.sID = a.sID and c.Name < b.Name
>> join table2 as d on d.sID = a.sID and d.Name < c.Name
>> join table2 as e on e.sID = a.sID and e.Name < d.Name
>> join table2 as f on f.sID = a.sID and f.Name < e.Name
>> This will give one row with the names in alphabetical order.
>> You need as many tables as the number of entries per sID in table2.
>> If table2 does not always have six entries for each
>> sID, use OUTER JOINs
>> WANNABE wrote:
>> How can the data in a relational table be parsed into a flat file
>> table'
>> For instance; table1 has an id for each entry and an sid and name,
>> table2 has an
>> sid and name1 name2 name3...456.
>> I have both tables in the same database and want to import the data
>> from table1
>> into table2. The records in table1 with identical sid's should
>> create only one
>> record in table2. Here is a sample of the tables, and what I have
>> started
>> with>>
>> CREATE DATABASE SAMPLE002
>> GO
>> USE SAMPLE002
>> CREATE TABLE table1
>> (
>> sID int NOT NULL,
>> Name1 varchar(50) NULL,
>> Name2 varchar(50) NULL,
>> Name3 varchar(50) NULL,
>> Name4 varchar(50) NULL,
>> Name5 varchar(50) NULL,
>> Name6 varchar(50) NULL
>> )
>> GO
>> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6)
>> VALUES (1,
>> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
>> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6)
>> VALUES (2,
>> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
>> GO
>> CREATE TABLE table2
>> (
>> pID int IDENTITY (1, 1) NOT NULL,
>> sID int NOT NULL,
>> Name varchar(50) NOT NULL
>> )
>> GO
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
>> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
>> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
>> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
>> --View the tables as they are
>> select * from table1
>> select * from table2
>> --I tried this first but of course it duplicates the sid in table 2
>> and that
>> can't happen. I expect there needs to be a looping process on each
>> sid but I am
>> not sure how to write that
>> insert into table1 (sid,Name1)
>> select sid,Name from table2
>> --Use this to clean out the mess made by the previous insert
>> delete from table1
>> where sid >2
>> THANK YOU !!!
>> =================================>>
>>|||Thanks Mark, I was able to make this code work but it parsed the data INTO the
relational table, and I am looking to parse the data INTO the FLAT file table.
===================================================="Mark Yudkin" <DoNotContactMe@.boingboing.org> wrote in message
news:%23IOEPIMsHHA.3628@.TK2MSFTNGP02.phx.gbl...
Warning: code not run, hence may have typos.
with T1 as (
select sId, name1 as Name from table1 where name1 is not null
union -- not union ALL, so as to eliminate duplicates
select sId, name2 from table1 where name2 is not null
union
... etc. ...
) -- you could also use the UNPIVOT operator to create T1 (briefer code)
insert into table2
select * from T1
where not exists (select * from table2 T2
where T2.sId = T1.Sid and T2.name = T1.Name)
"WANNABE" <SameAsB4> wrote in message
news:uE3dc1FsHHA.5008@.TK2MSFTNGP05.phx.gbl...
> How can the data in a relational table be parsed into a flat file table'
> For instance; table1 has an id for each entry and an sid and name, table2
> has an
> sid and name1 name2 name3...456.
> I have both tables in the same database and want to import the data from
> table1
> into table2. The records in table1 with identical sid's should create
> only one
> record in table2. Here is a sample of the tables, and what I have started
> with>>
> CREATE DATABASE SAMPLE002
> GO
> USE SAMPLE002
> CREATE TABLE table1
> (
> sID int NOT NULL,
> Name1 varchar(50) NULL,
> Name2 varchar(50) NULL,
> Name3 varchar(50) NULL,
> Name4 varchar(50) NULL,
> Name5 varchar(50) NULL,
> Name6 varchar(50) NULL
> )
> GO
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (1,
> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
> (2,
> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
> GO
> CREATE TABLE table2
> (
> pID int IDENTITY (1, 1) NOT NULL,
> sID int NOT NULL,
> Name varchar(50) NOT NULL
> )
> GO
> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
> --View the tables as they are
> select * from table1
> select * from table2
> --I tried this first but of course it duplicates the sid in table 2 and
> that
> can't happen. I expect there needs to be a looping process on each sid
> but I am
> not sure how to write that
> insert into table1 (sid,Name1)
> select sid,Name from table2
> --Use this to clean out the mess made by the previous insert
> delete from table1
> where sid >2
> THANK YOU !!!
> =================================>|||Thanks allot Ed, but this replaces NULL values with N/A, and replaces all the
existing Name1...6 fields with N/A
=========================================="Ed Enstrom" <nospam@.invalid.net> wrote in message
news:lOcdi.6$Nx2.2@.newsfe12.lga...
These statements should work for you. It may not be the most elegant T-SQL
code, but it works.
The first statement seeds table1 with the distinct sID values from table2,
with "N/A" value for all the name columns.
The next statement updates Name1 in table1 with the lowest value in table2
for that sID.
The next statement updates Name2 in table1 with the lowest value in table2
that is not equal to the Name1 value in table1.
The next statement updates Name3 in table1 with the lowest value in table2
that is not equal to both the Name1 and Name2 values in table1.
And so on.
You need as many UPDATE statements as the maximum number of names for any sID.
For those sIDs in table2 that have fewer rows than the number of UPDATE
statements, the SELECT subquery will return NULL
and the ISNULL function will substitute "N/A" for the value to be updated.
insert into table1 select distinct sID, 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A'
from table2;
update table1
set Name1 =(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID);
update table1
set Name2 =(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1);
update table1
set Name3=(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2);
update table1
set Name4=(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3);
update table1
set Name5=(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4);
update table1
set Name6=(select ISNULL(min(Name),'N/A') from table2 where table1.sID = table2.sID
and table2.Name <> table1.Name1
and table2.Name <> table1.Name2
and table2.Name <> table1.Name3
and table2.Name <> table1.Name4
and table2.Name <> table1.Name5);
Ed Enstrom wrote:
> After I posted, I realized that outer joins will not give you what you
> want. They will just give you all permutations of the names.
> I will have to give this some more thought. I have never run into this
> situation before, but someone else probably has. Maybe they have a neat
> solution.
> WANNABE wrote:
>> There's much that I do not understand;
>> Can I use the select statement on it's own to view what will be
>> inserted into the table when I run the full query?
>> Does this join table2 to itself 6 times?
>> Table2 does not always have 6 entries per sid, and neither does
>> table1, but should I use FULL OUTER?
>> HOW is this suppose to work?
>> ======================================>> "Ed Enstrom" <nospam@.invalid.net> wrote in message
>> news:Ey0di.75$LW6.21@.newsfe12.lga...
>> Try this:
>> select a.sID, f.Name, e.Name, d.Name, c.Name, b.Name, a.Name
>> from table2 as a
>> join table2 as b on b.sID = a.sID and b.Name < a.Name
>> join table2 as c on c.sID = a.sID and c.Name < b.Name
>> join table2 as d on d.sID = a.sID and d.Name < c.Name
>> join table2 as e on e.sID = a.sID and e.Name < d.Name
>> join table2 as f on f.sID = a.sID and f.Name < e.Name
>> This will give one row with the names in alphabetical order.
>> You need as many tables as the number of entries per sID in table2.
>> If table2 does not always have six entries for each
>> sID, use OUTER JOINs
>> WANNABE wrote:
>> How can the data in a relational table be parsed into a flat file
>> table'
>> For instance; table1 has an id for each entry and an sid and name,
>> table2 has an
>> sid and name1 name2 name3...456.
>> I have both tables in the same database and want to import the data
>> from table1
>> into table2. The records in table1 with identical sid's should
>> create only one
>> record in table2. Here is a sample of the tables, and what I have
>> started
>> with>>
>> CREATE DATABASE SAMPLE002
>> GO
>> USE SAMPLE002
>> CREATE TABLE table1
>> (
>> sID int NOT NULL,
>> Name1 varchar(50) NULL,
>> Name2 varchar(50) NULL,
>> Name3 varchar(50) NULL,
>> Name4 varchar(50) NULL,
>> Name5 varchar(50) NULL,
>> Name6 varchar(50) NULL
>> )
>> GO
>> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6)
>> VALUES (1,
>> 'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
>> INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6)
>> VALUES (2,
>> 'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
>> GO
>> CREATE TABLE table2
>> (
>> pID int IDENTITY (1, 1) NOT NULL,
>> sID int NOT NULL,
>> Name varchar(50) NOT NULL
>> )
>> GO
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
>> INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
>> INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
>> INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
>> INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
>> --View the tables as they are
>> select * from table1
>> select * from table2
>> --I tried this first but of course it duplicates the sid in table 2
>> and that
>> can't happen. I expect there needs to be a looping process on each
>> sid but I am
>> not sure how to write that
>> insert into table1 (sid,Name1)
>> select sid,Name from table2
>> --Use this to clean out the mess made by the previous insert
>> delete from table1
>> where sid >2
>> THANK YOU !!!
>> =================================>>
>>|||Thanks Susan, That looks KOOL, but as a learning experiment I would like to
understand how this can be done with a simple query. Can you please explain
what you meant, "I think you got your tables mixed up" DID I DO SOMETHING
WRONG'
LOL
========================================"Steve Dassin" <rac4sqlnospam@.net> wrote in message
news:eZRxmOLsHHA.4932@.TK2MSFTNGP02.phx.gbl...
I think you got your tables mixed up -:)
CREATE TABLE Wtable1
(
pID int primary key,
sID int NOT NULL,
Name varchar(8) NOT NULL
)
GO
INSERT INTO Wtable1 (pID,sID,Name) VALUES (1,3, 'Robert')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (2,3, 'Roger')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (3,3, 'Edgar')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (4,3, 'Mitch')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (5,4, 'Julie')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (6,4, 'Wendy')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (7,4, 'Roberto')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (8,5, 'Jean')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (9,5, 'Jill')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (10,5, 'Alice')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (11,5, 'Claire')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (12,5, 'Steve')
INSERT INTO Wtable1 (pID,sID,Name) VALUES (13,5, 'Hugh')
CREATE TABLE Wtable2
(
sID int primary key,
Name1 varchar(8) NULL,
Name2 varchar(8) NULL,
Name3 varchar(8) NULL,
Name4 varchar(8) NULL,
Name5 varchar(8) NULL,
Name6 varchar(8) NULL
)
GO
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(1,
'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
INSERT INTO Wtable2 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(2,
'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
The RAC utility was designed by a brillant developer (I know him) to
solve these types of problems in the easiest and lest painful way
possible. This is really a pivoting problem and Rac does this well.
Rac is a system of stored procedures and functions that run in
sql server (2k/2005). It has many parameters that allow you to
manipulate (pivot) data and get exactly the result you want. You can
browse the site and look at the online Help to check out each
parameter.
Just insert the Rac result into Wtable2:
insert into Wtable2
Exec Rac
@.transform='Max(Name) as [Name]',
@.rows='sID',
@.pvtcol='pID',
--@.ranklimit would be the maximum NameN in Wtable2
@.from='Wtable1',@.rank='Col',@.ranklimit='6',
@.defaults1='y',@.racheck='y',@.shell='n',
@.replacepvtcols='{case when [value]>~~ then [value] else null end for []}',
@.select='select 1*sID as sID,_pvtcols_
from rac '
select * from Wtable2:
sID Name1 Name2 Name3 Name4 Name5 Name6
-- -- -- -- -- -- --
1 Hal Jenny Hank Mary NULL NULL
2 Leroy Anne Curtis NULL NULL NULL
3 Robert Roger Edgar Mitch NULL NULL
4 Julie Wendy Roberto NULL NULL NULL
5 Jean Jill Alice Claire Steve Hugh
Rac inserted rows 3-5.
Rac @.
www.rac4sql.net
More @.
www.beyondsql.blogspot.com|||"WANNABE" <SameAsB4> wrote in message
news:uhGNLeVsHHA.484@.TK2MSFTNGP06.phx.gbl...
> Thanks Susan, That looks KOOL, but as a learning experiment I would like
to
> understand how this can be done with a simple query. Can you please
explain
> what you meant, "I think you got your tables mixed up" DID I DO SOMETHING
> WRONG'
> LOL
Susan?...You got something going with my sister? -:)
>For instance; table1 has an id for each entry and an sid and name, table2
has an
>sid and name1 name2 name3...456.
That's backwards -:)|||Sorry Steve, Thanks Steve :-) LOL to you to, is your sister, never mind... :-)
I must have some dyslexic thing, mixing up my tables and names..
I see it now, Thanks again for all your help...
====================================="Steve Dassin" <rac4sqlnospam@.net> wrote in message
news:%23eZ$T4VsHHA.1208@.TK2MSFTNGP03.phx.gbl...
"WANNABE" <SameAsB4> wrote in message
news:uhGNLeVsHHA.484@.TK2MSFTNGP06.phx.gbl...
> Thanks Susan, That looks KOOL, but as a learning experiment I would like
to
> understand how this can be done with a simple query. Can you please
explain
> what you meant, "I think you got your tables mixed up" DID I DO SOMETHING
> WRONG'
> LOL
Susan?...You got something going with my sister? -:)
>For instance; table1 has an id for each entry and an sid and name, table2
has an
>sid and name1 name2 name3...456.
That's backwards -:)
Subscribe to:
Posts (Atom)