Help In Creating Insert Statements For Retreiving Data From Database
Apr 14, 2008
Hi all,
Could someone tell me how to get the data from all tables of the database in the form of insert script? We are moving our databse from SQL Server 2000 to SQL Server 2005. The scripts for the Database, Tables, Views , Procedures, Functions have been obtained and it is only the data that is remaining. Some are small tables with 5 to 6 columns but there are some with 50 odd columns. A friend of mine told me about a procedure that returns a dataset with INSERT statements by passing a table name as a parameter. Such procedure would be of great help.
Is it possible to export data from tables to SQL insert statements? Got SQL 2005 developer, visual studio 2005, and Visio enterprise architect, if that makes any difference. I already found this tool but it costs money.
How to create insert statements of the data from a table for top 'n' rows. I know we can create using generate scripts wizard, but we can't customize the number of rows. In my scenario i got a database with 10 tables where every table got millions of records, but the requirement is to sample out only top 10000 records from each table.
I am planning to generate table CREATE statements from GENERATE Scripts wizard and add this INSERT STATEMENT at the bottom.
EX : INSERT [dbo].[table] ([SERIALID], [BATCHID] VALUES (126751, '9100278GC4PM1', )
I have a database which will be generated via script. However there are some tables with default data that need to exist on the database.I was wondering if there could be a program that could generate inserts statments for the data in a table.I know about the import / export program that comes with SQL but it doesnt have an interface that lets you copy the insert script and values into an SQL file (or does it?)
We are using MS SQL Server 2008. I am running a batch job which deletes 21 days older records(6-7 million records). But daily we have transaction is going on in the database. When the delete occurs, all the insert statements got blocked and waits till the delete statement to complete. May I know why the blocking occurs?
I have a table which I would like to export to a series of insert statements (in a file maybe). Is this possible somehow?
Alternatively, I could use an existing xml document which contains table metadata (table name, column names,types etc) to transfer data from these particular columns to another database replica.
I have an SQL data source on my page and I select "Table". On the next screen I pick the fields I want to show. Then I click the "Advanced" button because I want to allow Inserts, updates and deletes. But its all greyed out abd I can't check this option. The UID in the connection string I am connecting under has the correct permissions in SQL server to do inserts, update and deletes too. Anyone know why it would be greyed out? The connectionstring property in the aspx code is dynamic but this shouldn't be the reason because I have used this before with success
I am trying to take an entire MS SQL database and put it in an sql file. I have succesfully copied the tables into an sql file by highlighting the tables in enterprise manager and choosing 'generate sql script'.
That gives me the structure, but now I would like the data (in insert statements). I have looked in enterprise manager's export wizard and sql analyzer to no avail. There seem to be a lot of options for exporting data except this one! Please point me in the right direction.
At the end of the day, I would like to be able to put everything in a text file. Then, should I have problems, I can just copy my text into query analyzer and have a brand new database.
The same as following Tree as you can see 1__ | 2__ | 4__ | 6 | __ 3__ 5
I need a query to return the following Result. I think it is possible only through Nested sub-Queries But i don't know how to do that Could any one help me.?
Hello all, I am having a lot of trouble with stored procedures. Could anyone help me out. I have a table which contains a number of meetings. What I want to do is search this table, get out all the meetings for today and put them in a seperate table meetings today. I can select the values, and I can insert the values. But how do I store the values so that i can pass the results of the select to the insert? Im also having a lot of trouble with storing date values. ANy help would be greatly appreciated. Regards, Padraic Hickey
I have table having around 100 million rows.Everyday we have an ETL process in which table will be trucnated and relaoded. Will creating a partition on the table increase the inserting speed?
Okay, guess a few questions rolled into one post here.On my site, I have a drop down list where users can select different columns from their database, and then once they select any given field, all the unique values that the field contains are brought up in a CheckBoxList for the user to select which ones they want to search for. (Note: that part of the site is already done--this next part is what I need help with) I want to create a SQL statement based on what the user checked. So like, if from FieldX they checked Item1, Item3, and Item8, the SQL statement created should be something along the lines of:SELECT * FROM Orders Where FieldX='Item1' OR FieldX='Item3' OR FieldX='Item8'This is going to be in an intranet, so I'm not too worried about SQL Injection attacks, which I've heard of, but don't really know what they are particulary. Although I guess it would be better to be safe rather than sorry.Also, as far as creating the SQL statement, some items from the database will be text and others will be numbers, so I guess I also need to know how to find out whether an item in question is a string or a number of some type so that I can know whether to enclose that item in single quotes within the SQL statement.Okay, I think that's it for now.Thanks in advance.
I have the following situation. We sell books on our website, and someof the books have more than one author. So I needed to create amany-to-many table, which is the intermidiate table between the authortable and the book table.I can't get the right join statement to work. I've used the code below,submitting an isbn (book id number) to identify the book, but the returnfrom the query simply sends me back all of the authors that are in themany_to_many table(called the book_to_author table here). I'd like it to return only theauthors attached to that isbn, instead of all the authors that are inthat table. What's wrong with the code below? Thanks for your help!SELECT a.firstname, a.lastname, a.title AS degree, a.bio, a.author_id,bf.isbn, bf.title AS booktitle, m.isbn AS Expr2, m.id, m.author_id ASExpr3 FROM author a INNER JOIN book_to_author m ONa.author_id=m.author_id CROSS JOIN book_detail_final bf WHEREbf.isbn='"&isbn&"' order by m.id descBill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I build my SQL statement with these values like so: SELECT * FROM POO WHERE COMPANY = 'foo' AND DAY = 2
The problem I am having is when there are multiple values of the same type in the list box. Say: lstCriteria.items(1).value = "COMPANY = 'foo'" lstCriteria.items(2).value = "DAY= 2" lstCriteria.items(1).value = "COMPANY = 'moo'"
My employer wants this to be valid, but I am having a tough time coming up with a solution.
I know that my SQL statement needs to now read: SELECT * FROM POO WHERE COMPANY = 'foo' AND DAY = 2 OR COMPANY = 'poo' AND DAY = 2
I have code set up to read the values of each list box item up to the "=". And I know that I need to compair this value with the others in the list box...but I am not running into any good solutions.
hi, i'm trying to create a table and populate it with data from another database residing on the same server. i've done this on oracle using tables within the same database and am just making a first effort with the added twist of a different database. this is what i've been doing so far...
CREATE TABLE facility_dimension ( fac_id INT IDENTITY(1,1), tri_fac_id CHAR(17), fac_nameVARCHAR(100), street VARCHAR(100), city VARCHAR(100), county VARCHAR(50), state VARCHAR(4), longitude REAL, latitude REAL, PRIMARY KEY(fac_id) ) SELECT tri_facility_id, facility_name, street_address, city_name, county_name, state_abbr, fac_latitude, fac_longitude FROM TRI_2004.form_1;
....where TRI_2004 is the other database and form_1 is the table. the result is creation of the new table and then the output of the secondary query. i'm assuming this can even be done but if it can't that would be helpful to know as well. thanks in advance!
Hi guys! I have these commands that insert into two tables, if condition 1 is met, it will insert into the first table, if the second condition is met, it will insert into the second table. Is there a way for the insert statements to be merged so that I won't be executing two statements? Dim update_phase_before As New SqlCommand("INSERT INTO TE_shounin_todokesho_jizen (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='before'", cnn) Dim update_phase_after As New SqlCommand("INSERT INTO TE_shounin_todokesho_jigo (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='after'", cnn)
i'm quite new with sql and i have an question about het insert statements. Is the order of the insert statements from any importance? and why is/isn't it?
I need to write some insert statements, 1 per table, ~100 tables, all having the approximante form:
Select Into TableA Select * From TableB
Except that I need explicit statements:
Select Into TableA Col1, Col2, Col3, ... ColN Values ...
The reason is that I need to preserve the current identity values (it's a replication setup scenario). I can set Identity_Insert On, but then it wants the explicit column names and values.
Is there a wizard or utility that will generate the statements for me? With 100 tables in the db, I'm not looking forward to writing it all :-)
TIA, Arthur
PS. Given that it's Easter weekend, if you have an answer could you please e me directly? Thanks!
I am creating my companys' database and I have a small problem that must be solved.
I have a pictures table: PicturesTable ------------- ProductID int ForeignKey Picture nvarchar(30) ...
(A product can have many pictures & the ProductID is unique for any product, but not for the table of pictures).
What I want to do is to somehow do a procedure to: 1) Check if any images (for a productID) exists in the table 2) if they do not exist then add the appropriate images into the table 3) if the images exist, then update the images with the new one that I have.
What I thought was to just delete all the images from the table for the specific product:
DELETE FROM PicturesTable WHERE ProductID = '10-11'
and then add the appropriate images: INSERT INTO PicturesTable (ProductID, Picture) VALUES ('10-11', 'Dir1/Pic1.gif') INSERT INTO PicturesTable (ProductID, Picture) VALUES ('10-11', 'Dir1/Pic2.gif') INSERT INTO PicturesTable (ProductID, Picture) VALUES ('10-11', 'Dir1/Pic3.gif')
but I do not like a lot this idea because if a user tries to read the pictures for that product (at the same time I was deleting them) s/he would get nothing. Is any other way that I can do it please?
Hi Friends, I have the following set of Insert Statements that calculates sums for various criteria and inserts a row at a time onto my table. I have a row for every month starting from January with sums for 4 severity levels. So for 12 months that would be 48 Insert Statements and if I want to do this for 4 different types of [EName] that would be 48 * 4 = 192 Insert Statements. Is there a better way to write this. Thanks for your help
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count]) SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 1 , Sum([Count]) FROM dbo.tbl_Ticket WHERE (TrendMonth LIKE 'January' and [Severity Level] = 1)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count]) SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 2 , Sum([Count]) FROM dbo.tbl_Ticket WHERE (TrendMonth LIKE 'January' and [Severity Level] = 2)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count]) SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 3 , Sum([Count]) FROM dbo.tbl_Ticket WHERE (TrendMonth LIKE 'January' and [Severity Level] = 3)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count]) SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 4 , Sum([Count]) FROM dbo.tbl_Ticket WHERE (TrendMonth LIKE 'January' and [Severity Level] = 4)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count]) SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 1 , Sum([Count]) FROM dbo.tbl_Ticket WHERE (TrendMonth LIKE 'February' and [Severity Level] = 1)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count]) SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 2 , Sum([Count]) FROM dbo.tbl_Ticket WHERE (TrendMonth LIKE 'February' and [Severity Level] = 2)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count]) SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 3 , Sum([Count]) FROM dbo.tbl_Ticket WHERE (TrendMonth LIKE 'February' and [Severity Level] = 3)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count]) SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 4 , Sum([Count]) FROM dbo.tbl_Ticket WHERE (TrendMonth LIKE 'February' and [Severity Level] = 4)
I would like to get opinions about the code below and what I can do to improve it. I don't know if I am using the best techniques in this code. I am not running into any problems, but I am assuming there has to be cleaner ways of doing this.
Also I am trying to figure out why the INSERT INTO statement in the query is giving me the error: "The column prefix '#ttsku' does not match with a table name or alias name used in the query."
The purpose of the code is to select all the item records from a linked server (Non MS SQL) and bring it into a temp table. I did this because I can't create a cursor to the other DB.
Using this temp table, look if the SKU table has the item in it, if it does then update the record, otherwise I need to create the record and fill in the values from the temp table.
Finally I need to delete any records that don't exist in the temp table.
Here is the code:
SELECT pt_mstr.pt_part as part, pt_mstr.pt_desc1 as desc1, dbo.udf_GetEntry(cd_det.cd_cmmt,1,';') as custdesc, dbo.udf_GetEntry(cd_det.cd_cmmt,2,';') as caformat, dbo.udf_GetEntry(cd_det.cd_cmmt,3,';') as plformat, dbo.udf_GetEntry(cd_det.cd_cmmt,6,';') as eaformat, (pt_mstr.pt__qad24 * pt_mstr.pt__qad25) as pallqty, case when um_mstr.um_conv is not null then round((pt_net_wt / (cast(pt_drwg_loc as numeric) /um_conv)),0) else round((pt_net_wt / cast(pt_drwg_loc as numeric)),0) end as packqty, pt_mstr.pt_drwg_loc as packsize, pt_mstr.pt_drwg_size as packum, dbo.udf_GetEntry(cd_det.cd_cmmt,4,';') as dist1, dbo.udf_GetEntry(cd_det.cd_cmmt,5,';') as dist2, pt_mstr.pt_user2 as brand, pt_mstr.pt__qad24 as ti, pt_mstr.pt__qad25 as hi, pt_mstr.pt_status as status INTO #ttsku FROM mfgprod..pub.pt_mstr pt_mstr left join mfgprod..pub.cd_det cd_det on (cd_det.cd_ref = pt_mstr.pt_part and cd_det.cd_type = 'MK' and cd_det.cd_lang = 'US' and cd_det.cd_seq = 0) left join mfgprod..pub.um_mstr um_mstr on (um_mstr.um_um = pt_mstr.pt_net_wt_um and um_mstr.um_alt_um = pt_drwg_size and um_mstr.um_part = '') WHERE pt_part_type = 'FG' and (pt_status = 'A' or pt_status = 'AMTO') and (pt_drwg_loc <> '' and pt_drwg_loc <> '0')
declare c_part cursor for select * from #ttsku
open c_part
fetch next from c_part
while @@fetch_status = 0 begin
if exists (select * from sku where sku.part = #ttsku.part) BEGIN update sku set sku.Desc1 = #ttsku.desc1, sku.CustDesc = ##ttsku.custdesc where sku.part = #ttsku.part END ELSE BEGIN insert into sku (sku.part, sku.desc1) select #ttsku.part, #ttsku.desc1 END
fetch next from c_part
END -- while
close c_part deallocate c_part
DELETE FROM sku WHERE not exists (select * from #ttsku where #ttsku.part = sku.part)
Hello all. Got bit of a long winded question here...........so here we go lol.
OK.......ive got data on an Excel spreadsheet. Ive set the spreadsheet up as a linked server and i'm creating a set of insert statements from it by using the following code:
For most records this generates a correct insert statement.........for example:
INSERT INTO TRAINREC (EMPLOY_REF, COURSE_NAME) VALUES ('153', 'NMA Panel');
However.........my problems start when the value for course name is containes an ' character. If it does the insert statement generated is incorrect. For example:
INSERT INTO TRAINREC (EMPLOY_REF, COURSE_NAME) VALUES ('139', 'Annual Accounting in Lloyd's Market');
can anyone suggest any ideas on how to get round this? Also if i havent explained it clearly enough just let me know and i can try and expand on it.
I'm using version 3.5.5386.0 of SqlServerCompact Edition for windows mobile.
My first tests indicates that an insert statement with '?' ist 20 % faster than with '@p1' parameters:
"INSERT INTO Itest(PKey,value1,value2,value3,Date1) VALUES (?,?,?,?,?)" "INSERT INTO Itest(PKey,value1,value2,value3,Date1) VALUES (@P1,@2,@3,@P4,@P5)"
I've no explanation for that. I reported some problems with the '?' parameters in my other task http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2715685&SiteID=1 So I'm a little bit afraid to use this version.
Is there a recommandation which version to use for Inserts?
I want to prepare insert statements for the data of all tables in my database. I want this to create seed data. Just by using those scripts i can insert the data in another db. Please suggest ho wto prepare in easy way.
we have some reference tables in in a specific database. that other applications need to have access to them. Is it possible to create a view in the application's database to retrive data from ref database while users just have access to the application Database not the view's underlying tables?
I rememeber they used to have this option to generate data script for the table in SQL 2000, but I can not find it in SQL 2005. I need to move one table from one database to another, but I need to generate SQL Insert Statements...
I'm using the following code to add some data to a table: Dim rand As Random = New Random Dim num As Int32 = rand.Next(10000000) Dim strConn as string = "......." Dim sql as string = "INSERT INTO tblitemid (itemid, userid, datetime, supplier, comment, commenttype, uniqueid) VALUES ('" & label1.Text & "', '" & user.identity.name & "', '"& System.DateTime.Now & "','3763' ,'" & textbox1.text & "' , 'C' ,'" & num & "')" Dim conn as New SQLConnection(strConn) Dim Cmd as New SQLCommand(sql, conn) Try conn.Open() Catch SQLExp as SQLException Response.Write ("An SQL Server Error Occurred: " & e.toString()) Finally cmd.ExecuteNonQuery conn.Close() End Try
As far as I can tell the code works fine. But for some odd reason I click the button, the code execute and the page closes as it should, but the data is never inserted into the database. I cant really seem to pick up on any paterns for why this would be happening. As a rough guess I'd say it doesnt insert the data 1 out of every 5 times or so it seems. Anyone every have any experience with this? Any comments would be helpful, cuz I'm at a loss. If youd like to see more code let me know.... Thanks, Scott
Is this a limitation of SQL server. I am running a quite complex sp that I wrote which uses exec to execute an SQL string. Running the SP produces the desired results but if I try to use this sp with an insert statement then I get an error message that exec cannot be nested in an insert statement.....any help would be appreciated
