1

One question about how to t-sql program the following query:

Table 1 I insert 400.000 mobilephonenumbers in a table with two columns. The number to insert and identity id.

Table 2 The second table is called SendList. It is a list with 3columns, a identity id, a List id, and a phonenumberid.

Table 3 Is called ListInfo and contains PK list id. and info about the list.

My question is how should I using T-sql:

Insert large list with phonenumbers to table 1, insert the generated id from the insert of phonenum. in table1, to table 2. AND in a optimized way. It cant take long time, that is my problem.

Greatly appreciated if someone could guide me on this one.

Thanks

Sebastian

Scott W
  • 9,742
  • 2
  • 38
  • 53
mandrakemagic
  • 11
  • 1
  • Are you loading these values from a file? If so, what are you using to import the file? – John M Gant Jun 28 '10 at 17:10
  • 1
    Also, are you sure you need the identity column on table 1? If the phone numbers are unique, that column may be a more appropriate key. If not, the identity column could give you a false sense of uniqueness. – John M Gant Jun 28 '10 at 17:11
  • 1
    Disagree @jmgant, phone numbers are not unique, ever. Multiple people can have the same phone number and they get reused when people turn them off. Never rely on phone number to be unique. – HLGEM Jun 28 '10 at 17:16
  • @HLGEM, you're right, hence the "if" in the second sentence. And if they're not guaranteed to be unique, there probably should be more than just those two columns in the table so that you can have some kind of real relational integrity. – John M Gant Jun 28 '10 at 17:30
  • Using business data as a primary key is obscene. People who even think about doing this should never be let near a computer again. – smirkingman Dec 09 '10 at 12:54

1 Answers1

1

What version of SQL Server are you using? If you are using 2008 you can use the OUTPUT clause to insert multiple records and output all the identity records to a table variable. Then you can use this to insert to the child tables.

DECLARE @MyTableVar table(MyID int);
INSERT MyTabLe (field1, field2)    
OUTPUT INSERTED.MyID
        INTO @MyTableVar
select Field1, Field2 from MyOtherTable where field3 = 'test'

--Display the result set of the table variable.

Insert MyChildTable (myID,field1, field2)
Select MyID, test, getdate() from @MyTableVar

I've not tried this directly with a bulk insert, but you could always bulkinsert to a staging table and then use the processs, described above. Inserting groups of records is much much faster than one at a time.

Deepak
  • 376
  • 6
  • 23
HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • thanks for input! Users upload a file (xls,xlsx or txt) to webserver. I use 3rdparty class to load to DataTable (ADO.NET). (good for small files, bad for large). If its a large list then I will ftp the files to the C:\ of the server hosting sql server 2008. Then I bulk insert txt file. For the xls and xlsx I am looking into loading them using SSIS (aswell as loading them from SSIS directly from webserver into Sql Server, but firewall issues are still giving me problems). @HLGEM OUTPUT INSERTED.xx seem to be the right solution. I will try it =) – mandrakemagic Jun 28 '10 at 19:58
  • here is a link to the first draft of the data model. It will grow with much more tables eventually. http://dl.dropbox.com/u/2557994/datamodel.png – mandrakemagic Jun 28 '10 at 20:03